DateTimeOffset query issues when using the EF Core stores with PostgreSQL

[rvlajcev]

Getting errors on Quartz job trigger.
Tried with Postgresql and SQLite, standalone and embedded.

Migration added and database is created.
Stack trace is in an attachment;

Thank you and keep up the good work.

services.AddDbContext<MainDbContext>(options => options.UseNpgsql(connection).UseOpenIddict())...

services.AddIdentity<ApplicationUser, IdentityRole>()
                .AddEntityFrameworkStores<MainDbContext>()
                .AddDefaultTokenProviders()...
				
services.AddQuartz(options =>
            {
                options.UseMicrosoftDependencyInjectionJobFactory();
                options.UseSimpleTypeLoader();
                options.UseInMemoryStore();
            })...

services.AddQuartzHostedService(options => options.WaitForJobsToComplete = true)...

services.AddOpenIddict()
                .AddCore(options =>
                {
                    options.UseQuartz();                    
                    options.UseEntityFrameworkCore()
                        .UseDbContext<MainDbContext>();                   
                })...


<ItemGroup>
  <PackageReference Include="Microsoft.AspNetCore.Identity" Version="2.2.0" />
  <PackageReference Include="Microsoft.AspNetCore.Identity.EntityFrameworkCore" Version="3.1.8" />
  <PackageReference Include="Microsoft.EntityFrameworkCore" Version="3.1.8" />
  <PackageReference Include="Microsoft.EntityFrameworkCore.Relational" Version="3.1.8" />
  <PackageReference Include="Microsoft.EntityFrameworkCore.Sqlite" Version="3.1.8" />
  <PackageReference Include="Microsoft.EntityFrameworkCore.Tools" Version="3.1.8" />
  <PackageReference Include="Npgsql.EntityFrameworkCore.PostgreSQL" Version="3.1.4" />
  
  <PackageReference Include="OpenIddict" Version="3.0.0-beta5.20503.76" />
  <PackageReference Include="OpenIddict.EntityFrameworkCore" Version="3.0.0-beta5.20503.76" />
  <PackageReference Include="OpenIddict.Quartz" Version="3.0.0-beta5.20503.76" />
  <PackageReference Include="OpenIddict.Server.AspNetCore" Version="3.0.0-beta5.20503.76" />
  <PackageReference Include="OpenIddict.Validation.AspNetCore" Version="3.0.0-beta5.20503.76" />
  <PackageReference Include="Quartz.Extensions.Hosting" Version="3.2.0" />
</ItemGroup>

Stack trace:
log20201006.txt

[yawnston]

From what I can tell, these seem to be issues with the SQLite and Postgres providers for EF Core. These issues for SQLite and Postgres explain that the underlying DBs don't have a straightforward way of storing DateTimeOffset, so another way of mapping those values would need to be implemented in their providers (which doesn't seem to have happened yet). MSDN even mentions the problem with DateTimeOffset as a limitation of SQLite with EF Core. Therefore your issue is actually with your DB's provider for EF Core and not OpenIddict, you can track the progress of these issues there.

It could be argued that NodaTime's Instant would be a better representation of the values for which DateTimeOffset is currently used, but it's probably not worth adding a dependency just for that, and since all DateTimeOffsets in the codebase are being used with a +0 offset, the current implementation isn't wrong in any way. Another possibility could be to replace all usage of DateTimeOffset in the library with DateTime where Kind is set to UTC.

[rvlajcev]

Therefore your issue is actually with your DB's provider for EF Core and not OpenIddict, you can track the progress of these issues there.

Such feature should be database agnostic, imho.
I guess it's time for custom implementation.

[kevinchalet]

Such feature should be database agnostic, imho.

OpenIddict's EF Core stores are database agnostic, but as @yawnston perfectly explained, they use DateTimeOffset, for which querying has annoying limitations in certain providers like SQLite.

That said, it's not the first time we encounter bugs related to DateTimeOffset: the Oracle MySQL provider also has an issue with date round-tripping: #1097

Maybe we should move to DateTime for good, even if it's an important breaking change.

[rvlajcev]

OpenIddict's EF Core stores are database agnostic

I was reffering to public virtual async ValueTask PruneAsync,
not store in general.

Maybe we should move to DateTime for good, even if it's an important breaking change.

That's up to you, Kévin. Thanks to @yawnston, I now know where the problem is,
so I can move on to custom implementation.
I mean, it would be great if I don't have to, but for me, personally, it's not a big deal.

Cheers