EntityFrameworkCore with Firebird

[alansbraga]

Modifications to avoid error: "The LINQ expression ... could not be translated"

Without this modification I've got the exception below when QuartzJob try to prune Tokens

Exception

Depth 0

Class

Quartz.JobExecutionException

Message

One or more errors occurred. (The LINQ expression 'DbSet<OpenIddictEntityFrameworkCoreToken>()
    .Where(o => o.CreationDate < __date_0)
    .LeftJoin(
        inner: DbSet<OpenIddictEntityFrameworkCoreAuthorization>(), 
        outerKeySelector: o => EF.Property<string>(o, "AuthorizationId"), 
        innerKeySelector: o0 => EF.Property<string>(o0, "Id"), 
        resultSelector: (o, i) => new TransparentIdentifier<OpenIddictEntityFrameworkCoreToken, OpenIddictEntityFrameworkCoreAuthorization>(
            Outer = o, 
            Inner = i
        ))
    .Where(o => o.Outer.Status != "inactive" && o.Outer.Status != "valid" || o.Inner != null && o.Inner.Status != "valid" || o.Outer.ExpirationDate < (DateTime?)DateTime.UtcNow)' could not be translated. 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'. See https://go.microsoft.com/fwlink/?linkid=2101038 for more information.)

Stack

at OpenIddict.Quartz.OpenIddictQuartzJob.Execute(IJobExecutionContext context) at OpenIddict.Quartz.OpenIddictQuartzJob.Execute(IJobExecutionContext context) at Quartz.Core.JobRunShell.Run(CancellationToken cancellationToken)

Depth 1

Class

System.AggregateException

Message

One or more errors occurred. (The LINQ expression 'DbSet<OpenIddictEntityFrameworkCoreToken>()
    .Where(o => o.CreationDate < __date_0)
    .LeftJoin(
        inner: DbSet<OpenIddictEntityFrameworkCoreAuthorization>(), 
        outerKeySelector: o => EF.Property<string>(o, "AuthorizationId"), 
        innerKeySelector: o0 => EF.Property<string>(o0, "Id"), 
        resultSelector: (o, i) => new TransparentIdentifier<OpenIddictEntityFrameworkCoreToken, OpenIddictEntityFrameworkCoreAuthorization>(
            Outer = o, 
            Inner = i
        ))
    .Where(o => o.Outer.Status != "inactive" && o.Outer.Status != "valid" || o.Inner != null && o.Inner.Status != "valid" || o.Outer.ExpirationDate < (DateTime?)DateTime.UtcNow)' could not be translated. 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'. See https://go.microsoft.com/fwlink/?linkid=2101038 for more information.)

Depth 2

Class

System.InvalidOperationException

Message

The LINQ expression 'DbSet<OpenIddictEntityFrameworkCoreToken>()
    .Where(o => o.CreationDate < __date_0)
    .LeftJoin(
        inner: DbSet<OpenIddictEntityFrameworkCoreAuthorization>(), 
        outerKeySelector: o => EF.Property<string>(o, "AuthorizationId"), 
        innerKeySelector: o0 => EF.Property<string>(o0, "Id"), 
        resultSelector: (o, i) => new TransparentIdentifier<OpenIddictEntityFrameworkCoreToken, OpenIddictEntityFrameworkCoreAuthorization>(
            Outer = o, 
            Inner = i
        ))
    .Where(o => o.Outer.Status != "inactive" && o.Outer.Status != "valid" || o.Inner != null && o.Inner.Status != "valid" || o.Outer.ExpirationDate < (DateTime?)DateTime.UtcNow)' could not be translated. 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'. See https://go.microsoft.com/fwlink/?linkid=2101038 for more information.

Stack

   at Microsoft.EntityFrameworkCore.Query.QueryableMethodTranslatingExpressionVisitor.<VisitMethodCall>g__CheckTranslated|15_0(ShapedQueryExpression translated, <>c__DisplayClass15_0& )
   at Microsoft.EntityFrameworkCore.Query.QueryableMethodTranslatingExpressionVisitor.VisitMethodCall(MethodCallExpression methodCallExpression)
   at Microsoft.EntityFrameworkCore.Query.QueryableMethodTranslatingExpressionVisitor.VisitMethodCall(MethodCallExpression methodCallExpression)
   at Microsoft.EntityFrameworkCore.Query.QueryableMethodTranslatingExpressionVisitor.VisitMethodCall(MethodCallExpression methodCallExpression)
   at Microsoft.EntityFrameworkCore.Query.QueryableMethodTranslatingExpressionVisitor.VisitMethodCall(MethodCallExpression methodCallExpression)
   at Microsoft.EntityFrameworkCore.Query.QueryCompilationContext.CreateQueryExecutor[TResult](Expression query)
   at Microsoft.EntityFrameworkCore.Storage.Database.CompileQuery[TResult](Expression query, Boolean async)
   at Microsoft.EntityFrameworkCore.Query.Internal.QueryCompiler.CompileQueryCore[TResult](IDatabase database, Expression query, IModel model, Boolean async)
   at Microsoft.EntityFrameworkCore.Query.Internal.QueryCompiler.<>c__DisplayClass12_0`1.<ExecuteAsync>b__0()
   at Microsoft.EntityFrameworkCore.Query.Internal.CompiledQueryCache.GetOrAddQuery[TResult](Object cacheKey, Func`1 compiler)
   at Microsoft.EntityFrameworkCore.Query.Internal.QueryCompiler.ExecuteAsync[TResult](Expression query, CancellationToken cancellationToken)
   at Microsoft.EntityFrameworkCore.Query.Internal.EntityQueryProvider.ExecuteAsync[TResult](Expression expression, CancellationToken cancellationToken)
   at Microsoft.EntityFrameworkCore.Query.Internal.EntityQueryable`1.GetAsyncEnumerator(CancellationToken cancellationToken)
   at System.Runtime.CompilerServices.ConfiguredCancelableAsyncEnumerable`1.GetAsyncEnumerator()
   at Microsoft.EntityFrameworkCore.EntityFrameworkQueryableExtensions.ToListAsync[TSource](IQueryable`1 source, CancellationToken cancellationToken)
   at OpenIddict.EntityFrameworkCore.OpenIddictEntityFrameworkCoreTokenStore`5.PruneAsync(DateTimeOffset threshold, CancellationToken cancellationToken)
   at OpenIddict.Quartz.OpenIddictQuartzJob.Execute(IJobExecutionContext context)

[kevinchalet]

Thanks for your PR 👍🏻

It's worth noting that this fix introduces a behavior change: providers that natively support translating DateTime.UtcNow in queries will no longer do so.

Before we change anything in OI, we need to determine if Firebird plans to fix this issue on their side. Can you please open a ticket on their repo?

[alansbraga]

Hi! I've opened a ticket there and this was the answer

Only in firebird 4 we will have UTC support. I'll wait your decision to decide what I'm going to do next.

Thank you again!

[kevinchalet]

@alansbraga thanks for opening a ticket there.

Given that it would change the behavior of the other providers, I'd prefer not introducing such a change in 4.x. That said, if Firebird 4 doesn't fix that, we'll probably reconsider that in OpenIddict 5.x.

Closing for now, but thanks again for your PR 😃