EF.CompileQuery is not working with predefined static query failing

[msmolka]

I have query part defined as static query, to reuse in multiple places of code. When I running static query directly all is working properly, however when I'm trying to use it in EF.CompileQuery it throws exception. Putting the same part directly to compilation part work as expected. But then DRY principle is lost. And I have to repeat myself many times.

Steps to reproduce

Following query:

private static IQueryable<Address> GetBaseQuery(Context db, string searchSql)
            => from address in db.Address
               join search in db.AddressSearch on address.Id equals search.Id
               where EF.Functions.Contains(search.Address, searchSql)
               select address;

Following code works:

int total = await GetBaseQuery(this.Db, searchSql).CountAsync(this.CancellationToken)

This not:

private static Func<Context, string, Task<int>> queryFullSearchCount
   = EF.CompileAsyncQuery((Context db, string searchSql) 
   => GetBaseQuery(db, searchSql).Count());

int total = await queryFullSearchCount(this.Db, searchSql);

It throws following exception:

InvalidOperationException: Processing of the LINQ expression 'Count<Address>(GetBaseQuery( db: Context, searchSql: (Unhandled parameter: __searchSql)))' 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.

    Microsoft.EntityFrameworkCore.Query.Internal.NavigationExpandingExpressionVisitor.VisitMethodCall(MethodCallExpression methodCallExpression)
    System.Linq.Expressions.MethodCallExpression.Accept(ExpressionVisitor visitor)
    System.Linq.Expressions.ExpressionVisitor.Visit(Expression node)
    Microsoft.EntityFrameworkCore.Query.Internal.NavigationExpandingExpressionVisitor.ExpandAndReduce(Expression query, bool applyInclude)
    Microsoft.EntityFrameworkCore.Query.Internal.NavigationExpandingExpressionVisitor.Expand(Expression query)
    Microsoft.EntityFrameworkCore.Query.QueryTranslationPreprocessor.Process(Expression query)
    Microsoft.EntityFrameworkCore.Query.QueryCompilationContext.CreateQueryExecutor<TResult>(Expression query)
    Microsoft.EntityFrameworkCore.Storage.Database.CompileQuery<TResult>(Expression query, bool async)
    Microsoft.EntityFrameworkCore.Query.Internal.QueryCompiler.CompileQueryCore<TResult>(IDatabase database, Expression query, IModel model, bool async)
    Microsoft.EntityFrameworkCore.Query.Internal.QueryCompiler.CreateCompiledAsyncQuery<TResult>(Expression query)
    Microsoft.EntityFrameworkCore.Query.Internal.CompiledAsyncTaskQuery<TContext, TResult>.CreateCompiledQuery(IQueryCompiler queryCompiler, Expression expression)
    Microsoft.EntityFrameworkCore.Query.Internal.CompiledQueryBase<TContext, TResult>+<>c__DisplayClass6_0.<EnsureExecutor>b__0(TContext c, LambdaExpression q)
    Microsoft.EntityFrameworkCore.Internal.NonCapturingLazyInitializer.EnsureInitialized<TParam1, TParam2, TValue>(ref TValue target, TParam1 param1, TParam2 param2, Func<TParam1, TParam2, TValue> valueFactory)
    Microsoft.EntityFrameworkCore.Query.Internal.CompiledQueryBase<TContext, TResult>.EnsureExecutor(TContext context)
    Microsoft.EntityFrameworkCore.Query.Internal.CompiledQueryBase<TContext, TResult>.ExecuteCore(TContext context, CancellationToken cancellationToken, object[] parameters)
    Microsoft.EntityFrameworkCore.Query.Internal.CompiledQueryBase<TContext, TResult>.ExecuteCore(TContext context, object[] parameters)
    Microsoft.EntityFrameworkCore.Query.Internal.CompiledAsyncTaskQuery<TContext, TResult>.ExecuteAsync<TParam1>(TContext context, TParam1 param1)

Further technical details

EF Core version: 3.0 Database provider: (e.g. Microsoft.EntityFrameworkCore.SqlServer) Target framework: (.NET Core 3.0) Operating system: Windows 10 x64 IDE: (e.g. Visual Studio 2019 16.3.2)

[ajcvickers]

Notes from triage: currently composition in this way (where a function returning an IQueryable is present in the expression tree rather than everything being part of the same tree) is not supported. We could potentially make this work (for compiled queries and in other places) but it's not something we are likely to do without more feedback. One reason for this is that explicitly compiled queries often don't provide noticeably faster performance than normal, auto-compiled queries.

@msmolka Are you using compiled queries because of a significant performance improvement? If so, can you provide details, including your performance numbers?

[msmolka]

Hello, I'm trying different approach to avoid lag during startup (first run). Sometimes 5 second delay for first entity access is annoying. So I'm trying to find the way to improve first experience. I'm already using pooling, but still first one is longer.

[AndriySvyryd]

@msmolka You might be experiencing the delay caused by model building. You can access context.Model before performing a query to force the model to build and measure it separately.