FirstOrDefaultAsync is not supported in EF.CompileQuery

[Meberem]

trafficstars

FirstOrDefaultAsync is not supported when calling a Compiled Query

Exception message:
Stack trace: System.NotSupportedException: Could not parse expression 'value(Microsoft.EntityFrameworkCore.Query.Internal.EntityQueryable`1[MyEntity]).Where(m => (((m.One == __request.One) AndAlso (m.Two == __request.Two)) AndAlso (m.Three == __request.Three))).Select(m => m.Id).FirstOrDefaultAsync(__cancellationToken)': This overload of the method 'Microsoft.EntityFrameworkCore.EntityFrameworkQueryableExtensions.FirstOrDefaultAsync' is currently not supported.

Steps to reproduce

Run the query

public class Query
{
    public int One { get; set; }
    public int Two { get; set; }
    public int Three { get; set; }
}

public class MyEntity
{
    public int One { get; set; }
    public int Two { get; set; }
    public int Three { get; set; }
    public int Four { get; set; }
}

public class MyDbContext : DbContext
{
    public DbSet<MyEntity> MyEntities { get; set; }
}

private static readonly Func<MyDbContext, Query, CancellationToken, Task<string>>
            PreCompiledQuery = EF.CompileQuery((MyDbContextcontext, Query request, CancellationToken cancellationToken) =>
                context
                    .MyEntities
                    .Where(m => m.One == request.One &&
                                m.Two == request.Two &&
                                m.Three == request.Three)
                    .Select(m => m.Four)
                    .FirstOrDefaultAsync(cancellationToken));

Further technical details

EF Core version: Microsoft.EntityFrameworkCore.SqlServer (2.1.4) Operating system: Window 10 IDE: Visual Studio 2017 15.8.4

[smitpatel]

This may not work. Need to use CompileAsyncSingletonQuery. Need a different API design for CompileQuery.

[smitpatel]

new Exception

System.InvalidOperationException : Processing of the LINQ expression 'FirstOrDefaultAsync<string>(
        source: Select<Customer, string>(
            source: OrderBy<Customer, string>(
                source: Where<Customer>(
                    source: DbSet<Customer>, 
                    predicate: (c) => c.CustomerID == (Unhandled parameter: __customerID)), 
                keySelector: (c) => c.CustomerID), 
            selector: (c) => c.City), 
        cancellationToken: (Unhandled parameter: __cancellationToken))' by 'NavigationExpandingExpressionVisitor' failed. This may indicate either a bug or a limitation in EF Core. See https://go.microsoft.com/fwlink/?linkid=2101433 for more detailed information.

This is because our query pipeline does not understand/process Async queryable operators in the expression tree.

[smitpatel]

Dependent on #14551

[smitpatel]

Workaround. & pass cancellationToken when invoking the query.

private static readonly Func<MyDbContext, Query, CancellationToken, Task<string>>
            PreCompiledQuery = EF.CompileAsyncQuery((MyDbContextcontext, Query request, CancellationToken cancellationToken) =>
                context
                    .MyEntities
                    .Where(m => m.One == request.One &&
                                m.Two == request.Two &&
                                m.Three == request.Three)
                    .Select(m => m.Four)
                    .FirstOrDefault());

[StasPerekrestov]

var four = await PreCompiledQuery(context, query, cancellationToken); @smitpatel So, does EF.CompileAsyncQuery take into consideration a passed cancellation token then? Thanks.

PS: From what I've already checked, it seems it does use the token.

[prashantaggarwal1990]

I am facing an issue after converting the project from .Net core 2.1 to 3.1. I have following linq code:

var query = _context.FileDescriptor.Where(x => x.ClientId == clientId); var finalQuery = from entity in testquery join nextVersion in testquery on entity.Id equals nextVersion.PreviousRevisionId into nextVersions where !nextVersions.Any() && !entity.IsDeleted && entity.FolderId == folderId select entity return await finalQuery.FirstOrDefaultAsync(d => !d.IsDeleted && d.FolderId == folderId && d.FileName == fileName, token);

Getting the below error while executing: System.InvalidOperationException: Processing of the LINQ expression 'DbSet .Where(x => x.ClientId == __clientId_0) .GroupJoin( outer: DbSet .Where(x => x.ClientId == __clientId_0), inner: entity => (Nullable)entity.Id, outerKeySelector: nextVersion => nextVersion.PreviousRevisionId, innerKeySelector: (entity, nextVersions) => new { entity = entity, nextVersions = nextVersions })' by 'NavigationExpandingExpressionVisitor' failed. This may indicate either a bug or a limitation in EF Core. See https://go.microsoft.com/fwlink/?linkid=2101433 for more detailed information. at Microsoft.EntityFrameworkCore.Query.Internal.NavigationExpandingExpressionVisitor.VisitMethodCall(MethodCallExpression methodCallExpression) at System.Linq.Expressions.MethodCallExpression.Accept(ExpressionVisitor visitor) at Microsoft.EntityFrameworkCore.Query.Internal.NavigationExpandingExpressionVisitor.VisitMethodCall(MethodCallExpression methodCallExpression) at System.Linq.Expressions.MethodCallExpression.Accept(ExpressionVisitor visitor) at Microsoft.EntityFrameworkCore.Query.Internal.NavigationExpandingExpressionVisitor.VisitMethodCall(MethodCallExpression methodCallExpression) at System.Linq.Expressions.MethodCallExpression.Accept(ExpressionVisitor visitor) at Microsoft.EntityFrameworkCore.Query.Internal.NavigationExpandingExpressionVisitor.VisitMethodCall(MethodCallExpression methodCallExpression) at System.Linq.Expressions.MethodCallExpression.Accept(ExpressionVisitor visitor) at Microsoft.EntityFrameworkCore.Query.Internal.NavigationExpandingExpressionVisitor.Expand(Expression query) at Microsoft.EntityFrameworkCore.Query.QueryTranslationPreprocessor.Process(Expression query) 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_01.<ExecuteAsync>b__0() at Microsoft.EntityFrameworkCore.Query.Internal.CompiledQueryCache.GetOrAddQueryCore[TFunc](Object cacheKey, Func1 compiler) at Microsoft.EntityFrameworkCore.Query.Internal.CompiledQueryCache.GetOrAddQuery[TResult](Object cacheKey, Func1 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.EntityFrameworkQueryableExtensions.ExecuteAsync[TSource,TResult](MethodInfo operatorMethodInfo, IQueryable1 source, Expression expression, CancellationToken cancellationToken) at Microsoft.EntityFrameworkCore.EntityFrameworkQueryableExtensions.ExecuteAsync[TSource,TResult](MethodInfo operatorMethodInfo, IQueryable1 source, LambdaExpression expression, CancellationToken cancellationToken) at Microsoft.EntityFrameworkCore.EntityFrameworkQueryableExtensions.FirstOrDefaultAsync[TSource](IQueryable1 source, Expression`1 predicate, CancellationToken cancellationToken) Please help..

[smitpatel]

@prashantaggarwal1990 - Your query is not using EF.CompileQuery. Please stop spamming unrelated issues.

[ajcvickers]

Note for triage: unless I'm doing something wrong, the workaround above no longer works:

using (var context = new SomeDbContext())
{
    var query = new Query();

    var results1 = await context.MyEntities.Where(m => m.One == query.One && m.Two == query.Two && m.Three == query.Three)
        .Select(m => m.Four)
        .FirstOrDefaultAsync();

    var compiledQuery = EF.CompileAsyncQuery<SomeDbContext, Query, CancellationToken, int>(
        (SomeDbContext c, Query request, CancellationToken ct) =>
            c.MyEntities
                .Where(m => m.One == request.One &&
                    m.Two == request.Two &&
                    m.Three == request.Three)
                .Select(m => m.Four)
                .FirstOrDefault());

    var results2 = await compiledQuery(context, query, CancellationToken.None);

    Console.WriteLine(results1);
    Console.WriteLine(results2);
}

The non-compiled query translates, the compiled one throws:

Unhandled exception. System.InvalidOperationException: The LINQ expression 'DbSet<MyEntity>()
    .Where(m => m.One == __request.One && m.Two == __request.Two && m.Three == __request.Three)' 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'. S
ee https://go.microsoft.com/fwlink/?linkid=2101038 for more information.
   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.RelationalQueryableMethodTranslatingExpressionVisitor.VisitMethodCall(MethodCallExpression methodCallExpression)
   at Microsoft.EntityFrameworkCore.Query.QueryableMethodTranslatingExpressionVisitor.VisitMethodCall(MethodCallExpression methodCallExpression)
   at Microsoft.EntityFrameworkCore.Query.RelationalQueryableMethodTranslatingExpressionVisitor.VisitMethodCall(MethodCallExpression methodCallExpression)
   at Microsoft.EntityFrameworkCore.Query.QueryableMethodTranslatingExpressionVisitor.VisitMethodCall(MethodCallExpression methodCallExpression)
   at Microsoft.EntityFrameworkCore.Query.RelationalQueryableMethodTranslatingExpressionVisitor.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.CreateCompiledAsyncQuery[TResult](Expression query)
   at Microsoft.EntityFrameworkCore.Query.Internal.CompiledAsyncTaskQuery`2.CreateCompiledQuery(IQueryCompiler queryCompiler, Expression expression)
   at Microsoft.EntityFrameworkCore.Query.Internal.CompiledQueryBase`2.<>c.<EnsureExecutor>b__6_0(CompiledQueryBase`2 t, TContext c, LambdaExpression q)
   at Microsoft.EntityFrameworkCore.Internal.NonCapturingLazyInitializer.EnsureInitialized[TParam1,TParam2,TParam3,TValue](TValue& target, TParam1 param1, TParam2 param2, TParam3 param3, Func`4 valueFactory)
   at Microsoft.EntityFrameworkCore.Query.Internal.CompiledQueryBase`2.EnsureExecutor(TContext context)
   at Microsoft.EntityFrameworkCore.Query.Internal.CompiledQueryBase`2.ExecuteCore(TContext context, CancellationToken cancellationToken, Object[] parameters)
   at Microsoft.EntityFrameworkCore.Query.Internal.CompiledAsyncTaskQuery`2.ExecuteAsync[TParam1](TContext context, TParam1 param1, CancellationToken cancellationToken)
   at Program.Main() in C:\local\code\AllTogetherNow\Daily\Daily.cs:line 130
   at Program.<Main>()

Full code:

public class Query
{
    public int One { get; set; }
    public int Two { get; set; }
    public int Three { get; set; }
}

public class MyEntity
{
    public int Id { get; set; }
    public int One { get; set; }
    public int Two { get; set; }
    public int Three { get; set; }
    public int Four { get; set; }
}

public class SomeDbContext : DbContext
{
    protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
        => optionsBuilder
            //.UseNpgsql("Server=127.0.0.1;Port=5432;Database=myDataBase;User Id=myUsername;Password=myPassword;")
            //.UseSqlite(@"Data Source=c:\local\test.db")
            //.UseInMemoryDatabase("Test")
            // .UseLazyLoadingProxies()
            .UseSqlServer(@"Data Source=(LocalDb)\MSSQLLocalDB;Database=AllTogetherNow")
            .LogTo(Console.WriteLine, LogLevel.Information)
            .EnableSensitiveDataLogging();

    public DbSet<MyEntity> MyEntities
        => Set<MyEntity>();
}


public class Program
{
    public static async Task Main()
    {
        using (var context = new SomeDbContext())
        {
            await context.Database.EnsureDeletedAsync();
            await context.Database.EnsureCreatedAsync();

            await context.SaveChangesAsync();
        }

        using (var context = new SomeDbContext())
        {
            var query = new Query();

            var results1 = await context.MyEntities.Where(m => m.One == query.One && m.Two == query.Two && m.Three == query.Three)
                .Select(m => m.Four)
                .FirstOrDefaultAsync();

            var compiledQuery = EF.CompileAsyncQuery<SomeDbContext, Query, CancellationToken, int>(
                (SomeDbContext c, Query request, CancellationToken ct) =>
                    c.MyEntities
                        .Where(m => m.One == request.One &&
                            m.Two == request.Two &&
                            m.Three == request.Three)
                        .Select(m => m.Four)
                        .FirstOrDefault());

            var results2 = await compiledQuery(context, query, CancellationToken.None);

            Console.WriteLine(results1);
            Console.WriteLine(results2);
        }
    }
}