Ef Core 6 DateTime in Postrgres

[rabberbock]

Description

The Npgsql Ef Core library made some breaking changes for the default mapping of DateTime. See Release Notes.

When I try a BulkInsert it fails, however a regular Add with SaveChanges works fine.

Exception

If you are seeing an exception, include the full exceptions details (message and stack trace).

Unhandled exception. System.InvalidCastException: Cannot write DateTime with Kind=UTC to PostgreSQL type 'timestamp without time zone', consider using 'timestamp with time zone'. 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.TimestampHandler.ValidateAndGetLength(DateTime value, NpgsqlParameter parameter)
   at Npgsql.Internal.TypeHandlers.DateTimeHandlers.TimestampHandler.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.ExecuteNonQuery(Boolean async, CancellationToken cancellationToken)
   at Npgsql.NpgsqlCommand.ExecuteNonQuery()
   at ?.?(DbCommand ?, BulkOperation ?, Int32 ?)
   at ?.?(? ?, DbCommand ?)
   at ?.Execute(List`1 actions)
   at ?.?(List`1 ?)
   at Z.BulkOperations.BulkOperation.Execute()
   at Z.BulkOperations.BulkOperation.BulkInsert()
   at ?.BulkInsert[T](DbContext this, IEntityType entityType, IEnumerable`1 list, Action`1 options, SavingSelector savingSelector, Boolean forceSpecificTypeMapping)
   at ?.BulkInsert[T](DbContext this, IEnumerable`1 entities, Action`1 options, Boolean isBulkSaveChanges)
   at DbContextExtensions.BulkInsert[T](DbContext this, IEnumerable`1 entities, Action`1 options)
   at DbContextExtensions.BulkInsert[T](DbContext this, IEnumerable`1 entities)
   at EfCoreBulkInsertDateTime.Program.Main(String[] args) in C:\Users\rabbe\Projects\PomeloMySqlJson\Program.cs:line 41

Fiddle or Project (Optional)

https://github.com/rabberbock/EfCoreBulkInsertDateTime/tree/master

Further technical details

• EF version: 6.0.0-rc.1.21452.10
• EF Extensions version: 6.0.0-rc.1.21452.10-1
• Database Provider: Npgsql (Postgres)

I suspect this may have to do with the temporary table that is being created under the hood, the DateTime in the temporary table may be timestamp without time zone when it is supposed to be timestamp with time zone. Just a thought.

Thanks for your help!

[JonathanMagnan]

Hello @rabberbock ,

Thank you for reporting, we will look at it.

Unfortunately, DateTime in ProgreSQL always has been a mess with all this kind of changes ;(

Best Regards,

Jon

[rabberbock]

Also, there seems to be an issue with BulkUpdate as well. Probably the same underlying issue, but wanted to note that as well. Thanks!

[JonathanMagnan]

Hello @rabberbock ,

Yup, all these kinds of issues are related.

A fix has been already merged and will be available next Tuesday.

Best Regards,

Jon

[rabberbock]

@JonathanMagnan Awesome, thanks so much!

[JonathanMagnan]

Hello @rabberbock ,

The v5.2.14 has been released.

Could you try it and let us know if everything behaves as expected?

Best Regards,

Jon

[roji]

Please ping me here if you need any sort of assistance from the Npgsql side... I know the changes in 6.0.0 are painful, but I'm hoping this cleans up the mess once and for all.

[rabberbock]

@JonathanMagnan I tested it out with 6.0.0-rc.2.21480.5-1 and it worked perfectly. Thanks!

[Grubana]

Hello,

I got the same problem with the DateTimeOffset type as there is no .ToUniversalTime() call for this type. I think it would make sense to support this for postgres.

@JonathanMagnan do you think this is possible?

[JonathanMagnan]

Hello @Grubana ,

Do you think you could create a runnable project with the issue? It doesn’t need to be your project, just a new solution with the minimum code to reproduce the issue. You can send it in private here: [email protected]

My developer didn't really find any issue / is not sure exactly what your problem is.

Best Regards,

Jon