'dotnet ef migrations script' fails if any migrations had "timestamp without time zone" type

[TiraelSedai]

I'm using RC2.

I have project that has DateTime fields, and all of them have Kind = UTC. They used to be timestamp without time zone.

If I use dotnet ef migrations add Timestamptz and also add migrationBuilder.Sql("SET TimeZone='UTC';"); to the top of Up migration it goes pretty smoothly and now fields are timestamptz as far as I can tell.

However, when I'm trying to see what exactly was executed, migrations script fails:

'timestamp without time zone' literal cannot be generated for a UTC DateTime

Here's full output in case it's useful:

Build started...
Build succeeded.
System.InvalidCastException: 'timestamp without time zone' literal cannot be generated for a UTC DateTime
   at Npgsql.EntityFrameworkCore.PostgreSQL.Storage.Internal.Mapping.NpgsqlTimestampTypeMapping.GenerateLiteralCore(Object value)
   at Npgsql.EntityFrameworkCore.PostgreSQL.Storage.Internal.Mapping.NpgsqlTimestampTypeMapping.GenerateNonNullSqlLiteral(Object value)
   at Microsoft.EntityFrameworkCore.Storage.RelationalTypeMapping.GenerateProviderValueSqlLiteral(Object value)
   at Microsoft.EntityFrameworkCore.Update.UpdateSqlGenerator.AppendSqlLiteral(StringBuilder commandStringBuilder, IColumnModification modification, String tableName, String schema)
   at Microsoft.EntityFrameworkCore.Update.UpdateSqlGenerator.<>c.<AppendValues>b__20_0(StringBuilder sb, IColumnModification o, ValueTuple`3 p)
   at System.Text.StringBuilderExtensions.AppendJoin[T,TParam](StringBuilder stringBuilder, IEnumerable`1 values, TParam param, Action`3 joinAction, String separator)
   at Microsoft.EntityFrameworkCore.Update.UpdateSqlGenerator.AppendValues(StringBuilder commandStringBuilder, String name, String schema, IReadOnlyList`1 operations)
   at Npgsql.EntityFrameworkCore.PostgreSQL.Update.Internal.NpgsqlUpdateSqlGenerator.AppendInsertOperation(StringBuilder commandStringBuilder, IReadOnlyModificationCommand command, Int32 commandPosition, Boolean overridingSystemValue)
   at Npgsql.EntityFrameworkCore.PostgreSQL.Migrations.NpgsqlMigrationsSqlGenerator.Generate(InsertDataOperation operation, IModel model, MigrationCommandListBuilder builder, Boolean terminate)
   at Microsoft.EntityFrameworkCore.Migrations.MigrationsSqlGenerator.<>c.<.cctor>b__83_28(MigrationsSqlGenerator g, MigrationOperation o, IModel m, MigrationCommandListBuilder b)
   at Microsoft.EntityFrameworkCore.Migrations.MigrationsSqlGenerator.Generate(MigrationOperation operation, IModel model, MigrationCommandListBuilder builder)
   at Npgsql.EntityFrameworkCore.PostgreSQL.Migrations.NpgsqlMigrationsSqlGenerator.Generate(MigrationOperation operation, IModel model, MigrationCommandListBuilder builder)
   at Microsoft.EntityFrameworkCore.Migrations.MigrationsSqlGenerator.Generate(IReadOnlyList`1 operations, IModel model, MigrationsSqlGenerationOptions options)
   at Npgsql.EntityFrameworkCore.PostgreSQL.Migrations.NpgsqlMigrationsSqlGenerator.Generate(IReadOnlyList`1 operations, IModel model, MigrationsSqlGenerationOptions options)
   at Microsoft.EntityFrameworkCore.Migrations.Internal.Migrator.GenerateUpSql(Migration migration, MigrationsSqlGenerationOptions options)
   at Microsoft.EntityFrameworkCore.Migrations.Internal.Migrator.GenerateScript(String fromMigration, String toMigration, MigrationsSqlGenerationOptions options)
   at Microsoft.EntityFrameworkCore.Design.Internal.MigrationsOperations.ScriptMigration(String fromMigration, String toMigration, MigrationsSqlGenerationOptions options, String contextType)
   at Microsoft.EntityFrameworkCore.Design.OperationExecutor.ScriptMigrationImpl(String fromMigration, String toMigration, Boolean idempotent, Boolean noTransactions, String contextType)
   at Microsoft.EntityFrameworkCore.Design.OperationExecutor.ScriptMigration.<>c__DisplayClass0_0.<.ctor>b__0()
   at Microsoft.EntityFrameworkCore.Design.OperationExecutor.OperationBase.<>c__DisplayClass3_0`1.<Execute>b__0()
   at Microsoft.EntityFrameworkCore.Design.OperationExecutor.OperationBase.Execute(Action action)
'timestamp without time zone' literal cannot be generated for a UTC DateTime

[roji]

First, can you please try with the latest daily build from the vNext feed? There have been some fixes in the area since RC2 was released.

If you're still seeing an issue, can you please post the migration code that's causing the issue? A minimal repro would be ideal in order to investigate and fix this.

[TiraelSedai]

Still the same. I will try to create minimal repro.

[TiraelSedai]

https://github.com/TiraelSedai/NpgsqlScriptIssue

1. dotnet build
2. dotnet ef database update
3. Update all nuget packages to 6.0 latest preview
4. Rebuild everything, delete bin/ and obj/, relaunch Studio, pray to God(s), this is the step where I'm not sure what should happen but it always work on my "main" project and not always on "minimal repro"
5. dotnet ef migrations add Timestamptz
6. If you did everything right, new migration will have yellow warning that it may result in loss of data, and will try to change columns. If not, it will be empty.
7. From here, dotnet ef database update will work but dotnet ef migrations script will not

[roji]

Thanks for the detailed repro, I can see the error and am looking into (if only everyone posted such a detailed step-by-step repro...!)

[roji]

OK, I understand the issue. BTW it reproes even without generating a migration; it's enough to simply upgrade to 6.0.0 and try to generate a script.

The issue is that your first migration contains seeding data with a UTC DateTime for Timestamp, which at that point is still defined as a timestamp without time zone; but 6.0 no longer allows timestamp without time zone to contain UTC timestamps.

I'm guessing you set up the seeding to use UTC before even generating the first migration (while still on 5.0) - I expect that in the typical case people would just have Unspecified there. But this is indeed an upgrade difficulty - people will have to manually modify such mismatched seeding data. The same is true in the reverse, BTW: a migration seeding a timestamptz column with a non-UTC value will no longer work in 6.0.

Unfortunately, short of relaxing the mismatch rules (which we really try to avoid) I don't see what I can do, beyond pointing this out in the release notes...

Finally, this only showed up when generating a script presumably because your database was already at the first migration. If you drop your database and try to do dotnet ef database update just on the first migration (after upgrading to 6.0.0), you should get the same error.

[TiraelSedai]

I'm guessing you set up the seeding to use UTC before even generating the first migration (while still on 5.0)

Yes, that is exactly the case.

So to sum up what I have to do is to pretend my seed data was in Kind.Local until I generate the migration to timestamptz?
Or am I doomed to switch it back and forth as long as it exists (unless I decide to opt-out)?

It would definitely be useful to document what is happening somewhere here:
https://www.npgsql.org/efcore/release-notes/6.0.html#migrating-columns-from-timestamp-to-timestamptz

[roji]

So to sum up what I have to do is to pretend my seed data was in Kind.Local until I generate the migration to timestamptz?

Yep (though Kind.Unspecified would be slightly more accurate). There's no switching back and forth - you need to do this exactly once when you upgrade to 6.0, from that point the UTC/non-UTC distinction is enforced and you shouldn't need to do anything.

Basically once you move to 6.0, timestamp with time zone can only work with UTC DateTime, and timestamp without time zone can only work with non-UTC DateTime.

I'll definitely add a note about this.

[230Daniel]

My apologies, I just read the release notes and you've given a great guide on how to fix this issue. Thank you for that :)

Hi, I have the same issue. After updating to .net 6 (and therefore 6.0.0 of this lib), when I create a migration it's trying to change all of my DateTime types from timestamp without time zone to timestamp with time zone, and I can't send a DateTime to the database without an exception like this showing up:

Microsoft.EntityFrameworkCore.DbUpdateException: An error occurred while saving the entity changes. See the inner exception for details.
 ---> System.InvalidCastException: Cannot write DateTime with Kind=Unspecified 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. See the Npgsql.EnableLegacyTimestampBehavior AppContext switch to enable legacy behavior.
   at Npgsql.Internal.TypeHandlers.DateTimeHandlers.TimestampTzHandler.ValidateAndGetLength(DateTime value, NpgsqlParameter parameter)
   at Npgsql.Internal.TypeHandlers.DateTimeHandlers.TimestampTzHandler.ValidateObjectAndGetLength(Object value, NpgsqlLengthCache& lengthCache, NpgsqlParameter parameter)
   at Npgsql.NpgsqlParameter.ValidateAndGetLength()
   at Npgsql.NpgsqlParameterCollection.ValidateAndBind(ConnectorTypeMapper typeMapper)
   at Npgsql.NpgsqlCommand.ExecuteReader(CommandBehavior behavior, Boolean async, CancellationToken cancellationToken)
   at Npgsql.NpgsqlCommand.ExecuteReader(CommandBehavior behavior, Boolean async, 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)
   at Microsoft.EntityFrameworkCore.DbContext.SaveChangesAsync(Boolean acceptAllChangesOnSuccess, CancellationToken cancellationToken)

Is there any update on this issue?

Thanks.