Changing the Entity Framework provider from MSSQL to PostgreSQL #7490

• ABP Framework version: v8.2.0
• UI Type: MVC
• Database System: EF Core (PostgreSQL)
• Tiered (for MVC): no
• Exception message and full stack trace:

[07:33:12 ERR] Failed executing DbCommand (4ms) [Parameters=[@p0='?' (DbType = Guid), @p1='?', @p2='?' (DbType = DateTime), @p3='?' (DbType = Guid), @p4='?' (DbType = Guid), @p5='?' (DbType = DateTime), @p6='?', @p7='?' (DbType = Int32), @p8='?', @p9='?' (DbType = Boolean), @p10='?' (DbType = DateTime), @p11='?' (DbType = Guid), @p12='?' (DbType = DateTime), @p13='?' (DbType = Boolean), @p14='?' (DbType = DateTime), @p15='?', @p16='?', @p17='?', @p18='?', @p19='?', @p20='?', @p21='?' (DbType = Boolean), @p22='?', @p23='?' (DbType = Guid), @p24='?'], CommandType='Text', CommandTimeout='30']
INSERT INTO "AbpUsers" ("Id", "ConcurrencyStamp", "CreationTime", "CreatorId", "DeleterId", "DeletionTime", "Email", "EntityVersion", "ExtraProperties", "IsActive", "LastModificationTime", "LastModifierId", "LastPasswordChangeTime", "LockoutEnabled", "LockoutEnd", "Name", "NormalizedEmail", "NormalizedUserName", "PasswordHash", "PhoneNumber", "SecurityStamp", "ShouldChangePasswordOnNextLogin", "Surname", "TenantId", "UserName")
VALUES (@p0, @p1, @p2, @p3, @p4, @p5, @p6, @p7, @p8, @p9, @p10, @p11, @p12, @p13, @p14, @p15, @p16, @p17, @p18, @p19, @p20, @p21, @p22, @p23, @p24)
RETURNING "AccessFailedCount", "EmailConfirmed", "IsDeleted", "IsExternal", "PhoneNumberConfirmed", "TwoFactorEnabled";
[07:33:13 ERR] An exception occurred in the database while saving changes for context type 'Ambar.SaaS.IdentityService.EntityFrameworkCore.IdentityServiceDbContext'.
Microsoft.EntityFrameworkCore.DbUpdateException: An error occurred while saving the entity changes. See the inner exception for details.
 ---> System.ArgumentException: Cannot write DateTime with Kind=Local to PostgreSQL type 'timestamp with time zone', only UTC is supported. Note that it's not possible to mix DateTimes with different Kinds in an array, range, or multirange. (Parameter 'value')
   at Npgsql.Internal.Converters.DateTimeConverterResolver`1.Get(DateTime value, Nullable`1 expectedPgTypeId, Boolean validateOnly)
   at Npgsql.Internal.Converters.DateTimeConverterResolver.<>c.<CreateResolver>b__0_0(DateTimeConverterResolver`1 resolver, DateTime value, Nullable`1 expectedPgTypeId)
   at Npgsql.Internal.Converters.DateTimeConverterResolver`1.Get(T value, Nullable`1 expectedPgTypeId)
   at Npgsql.Internal.PgConverterResolver`1.GetAsObjectInternal(PgTypeInfo typeInfo, Object value, Nullable`1 expectedPgTypeId)
   at Npgsql.Internal.PgResolverTypeInfo.GetResolutionAsObject(Object value, Nullable`1 expectedPgTypeId)
   at Npgsql.Internal.PgTypeInfo.GetObjectResolution(Object value)
   at Npgsql.NpgsqlParameter.ResolveConverter(PgTypeInfo typeInfo)
   at Npgsql.NpgsqlParameter.ResolveTypeInfo(PgSerializerOptions options)
   at Npgsql.NpgsqlParameterCollection.ProcessParameters(PgSerializerOptions options, Boolean validateValues, CommandType commandType)
   at Npgsql.NpgsqlCommand.ExecuteReader(Boolean async, CommandBehavior behavior, CancellationToken cancellationToken)
   at Npgsql.NpgsqlCommand.ExecuteReader(Boolean async, CommandBehavior behavior, CancellationToken cancellationToken)
   at Npgsql.NpgsqlCommand.ExecuteDbDataReaderAsync(CommandBehavior behavior, CancellationToken cancellationToken)
   at Microsoft.EntityFrameworkCore.Storage.RelationalCommand.ExecuteReaderAsync(RelationalCommandParameterObject parameterObject, CancellationToken cancellationToken)
   at Microsoft.EntityFrameworkCore.Storage.RelationalCommand.ExecuteReaderAsync(RelationalCommandParameterObject parameterObject, CancellationToken cancellationToken)
   at Microsoft.EntityFrameworkCore.Update.ReaderModificationCommandBatch.ExecuteAsync(IRelationalConnection connection, CancellationToken cancellationToken)
   --- End of inner exception stack trace ---
   at Microsoft.EntityFrameworkCore.Update.ReaderModificationCommandBatch.ExecuteAsync(IRelationalConnection connection, CancellationToken cancellationToken)
   at Microsoft.EntityFrameworkCore.Update.Internal.BatchExecutor.ExecuteAsync(IEnumerable`1 commandBatches, IRelationalConnection connection, CancellationToken cancellationToken)
   at Microsoft.EntityFrameworkCore.Update.Internal.BatchExecutor.ExecuteAsync(IEnumerable`1 commandBatches, IRelationalConnection connection, CancellationToken cancellationToken)
   at Microsoft.EntityFrameworkCore.Update.Internal.BatchExecutor.ExecuteAsync(IEnumerable`1 commandBatches, IRelationalConnection connection, CancellationToken cancellationToken)
   at Microsoft.EntityFrameworkCore.ChangeTracking.Internal.StateManager.SaveChangesAsync(IList`1 entriesToSave, CancellationToken cancellationToken)
   at Microsoft.EntityFrameworkCore.ChangeTracking.Internal.StateManager.SaveChangesAsync(StateManager stateManager, Boolean acceptAllChangesOnSuccess, CancellationToken cancellationToken)
   at Npgsql.EntityFrameworkCore.PostgreSQL.Storage.Internal.NpgsqlExecutionStrategy.ExecuteAsync[TState,TResult](TState state, Func`4 operation, Func`4 verifySucceeded, CancellationToken cancellationToken)
   at Microsoft.EntityFrameworkCore.DbContext.SaveChangesAsync(Boolean acceptAllChangesOnSuccess, CancellationToken cancellationToken)
Microsoft.EntityFrameworkCore.DbUpdateException: An error occurred while saving the entity changes. See the inner exception for details.
 ---> System.ArgumentException: Cannot write DateTime with Kind=Local to PostgreSQL type 'timestamp with time zone', only UTC is supported. Note that it's not possible to mix DateTimes with different Kinds in an array, range, or multirange. (Parameter 'value')

• Steps to reproduce the issue: changed the Entity Framework provider from MSSQL to PostgreSQL in the ABP Commercial Microservices application, following these detailed steps:

1. Replace the Volo.Abp.EntityFrameworkCore.SqlServer Package Remove the Volo.Abp.EntityFrameworkCore.SqlServer NuGet package from your .EntityFrameworkCore project and add the same version of the Volo.Abp.EntityFrameworkCore.PostgreSql package.
2. Replace the Module Dependency Find your *EntityFrameworkCoreModule class inside the .EntityFrameworkCore project. Remove typeof(AbpEntityFrameworkCoreSqlServerModule) from the DependsOn attribute and add typeof(AbpEntityFrameworkCorePostgreSqlModule). Also, replace using Volo.Abp.EntityFrameworkCore.SqlServer; with using Volo.Abp.EntityFrameworkCore.PostgreSql;.
3. Update UseSqlServer to UseNpgsql Replace UseSqlServer() calls with UseNpgsql() in the following files:

*EntityFrameworkCoreModule.cs inside the .EntityFrameworkCore project. *DbContextFactory.cs inside the .EntityFrameworkCore project.

Depending on your solution structure, you may find more UseSqlServer() calls that need to be changed. 4. Change the Connection Strings Update all appsettings.json files in your solution to use PostgreSQL connection strings. You typically will change the appsettings.json inside the .DbMigrator and .Web projects, but it depends on your solution structure. For details on PostgreSQL connection string options, see connectionstrings.com. 5. Re-Generate the Migrations Since EF Core Migrations depend on the selected DBMS provider, changing the DBMS provider will cause migration issues. Follow these steps:

Delete the Migrations folder under the .EntityFrameworkCore project and re-build the solution. Run Add-Migration "Initial" on the Package Manager Console. Select the .DbMigrator (or .Web) project as the startup project in the Solution Explorer and select the .EntityFrameworkCore project as the default project in the Package Manager Console.

This will create a database migration with all database objects (tables) configured. Then, run the .DbMigrator project to create the database and seed the initial data. 6. Run the Application After completing the above steps, your application should be ready to run with PostgreSQL as the database provider. Simply run the application and ensure everything works as expected.