Problem with DateTimeOffset and EF Core using Postgres

[toliverg]

Hi,

I have a problem using dynamic querys when a DateTimeOffset field is used to filter when used in a Postgres EF Core solution. For example I have this working fine: ctx.Customers.Where(x => x.NewDate == new DateTimeOffset(2016, 7, 8, 0, 0, 0, 0, new TimeSpan(2,0,0))).ToList()

but when I try this: ctx.Customers.WhereDynamic("x => x.NewDate == new DateTimeOffset(2016, 7, 8, 0, 0, 0, 0, new TimeSpan(2,0,0))").ToList()

I get an exception

System.InvalidOperationException: 'The LINQ expression 'DbSet() .Where(c => DateTimeOffset.op_Equality( left: c.NewDate, right: new DateTimeOffset( 2016, 7, 8, 0, 0, 0, 0, new TimeSpan( 2, 0, 0 ) )))' could not be translated. Additional information: Translation of method 'System.DateTimeOffset.op_Equality' failed. If this method can be mapped to your custom function, see https://go.microsoft.com/fwlink/?linkid=2132413 for more information. Either rewrite the query in a form that can be translated, or switch to client evaluation explicitly by inserting a call to 'AsEnumerable', 'AsAsyncEnumerable', 'ToList', or 'ToListAsync'.

---

Do you know why this can happen?

Thanks

[JonathanMagnan]

Hello @toliverg ,

Thank you for reporting, we will look at it.

Best Regards,

Jon

[JonathanMagnan]

Hello @toliverg ,

Which package for PostgreSQL are you using? My developer tried it and everything seems to work fine.

Are you using DevArt PostgreSQL or another one?

If possible, 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. It will help my developer to directly pass time to fix the bug instead of trying to reproduce it.

Best Regards,

Jon

[toliverg]

Hi,

I'm using Npgsql.EntityFrameworkCore.PostgreSQL 6.0.4.

I have included a small working project. You just need to change the connection string in MyContext.cs

TestDatetimeOffset.zip

Best Regards,

Tolo

[toliverg]

Hi,

Do you have an update on this?

Thanks

[JonathanMagnan]

Hello @toliverg ,

The fix has been merged last week. It should be released tomorrow.

Best Regards,

Jon

[toliverg]

Hi,

The fix is working. Thanks a lot for your help.

Best Regards,

Tolo

[JonathanMagnan]

Awesome, thank for letting us know ;)