Npgsql 6 mistakenly identifying 'timestamp' for 'timestamptz'

Viewed 30

After migrating from .NET 5 to .NET 6, i get the following error when inserting into the database:

      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.
[2022-01-31 20:12:15] fail: Microsoft.EntityFrameworkCore.Database.Command[20102]
      Failed executing DbCommand (4ms) [Parameters=[@p0='?' (DbType = DateTime)], CommandType='Text', CommandTimeout='30']
      INSERT INTO database.table (timestamp)
      VALUES (@p0)
      RETURNING id;

I am using the following value converters to make sure all timestamps are still stored as UTC.

                var dateTimeConverter = new ValueConverter<DateTime, DateTime>(
                    v => DateTime.SpecifyKind(v.ToUniversalTime(), DateTimeKind.Unspecified),
                    v => DateTime.SpecifyKind(v, DateTimeKind.Utc));

                var nullableDateTimeConverter = new ValueConverter<DateTime?, DateTime?>(
                    v => v.HasValue ? DateTime.SpecifyKind(v.Value.ToUniversalTime(), DateTimeKind.Unspecified) : v,
                    v => v.HasValue ? DateTime.SpecifyKind(v.Value, DateTimeKind.Utc) : v);

The interesting thing is that the Postgres type of the field is not "timestamp with timezone" but "timestamp without timezone", which I verified in the database.

Setting

AppContext.SetSwitch("Npgsql.EnableLegacyTimestampBehavior", true);

does not fix my problem, moreover I would not like to keep legacy behavious that is considered obsolote.

Any ideas on how to fix?

EDIT: Fixed it!

Seems the problem was caused by the fact the Npsql now defaults to timestamptz for DateTime fields when creating migrations. This was also assumed for existing fields since the type was altered for all fields when trying to create a migration. To fix my problem, I had to manually set the column type to timestamp for each DateTime field in my models.

0 Answers
Related