Should compiled queries work with FromRawSql?

[brgrz]

I have a case where I want to make the following compiled query:

private static Func<MyDbContext, string, IEnumerable<Car>> _compiledQuery =
            EF.CompileQuery((MyDbContext dbContext, string sql) =>
                dbContext
                .Cars
                .FromSqlRaw(sql)
                .Include(b => b.File));

and use it in my query handler like this:

public IEnumerable<Car> GetAllAsync()
{
    // the actual SQL will be more complex this is just an example but even this does not work
        string sql = "select * from public.cars";

    return _compiledQuery(_dbContext, sql);
}

Even with the simple SQL statement as shown above this fails with the following exception:

System.ArgumentException: Expression of type 'System.Linq.IQueryable`1[Common.Data.Car]' cannot be used for parameter of type 'Microsoft.EntityFrameworkCore.DbSet`1[Common.Data.Car]' of method 'System.Linq.IQueryable`1[Common.Data.Car] FromSqlRaw[Car](Microsoft.EntityFrameworkCore.DbSet`1[Common.Data.Car], System.String, System.Object[])' (Parameter 'arg0')
   at System.Dynamic.Utils.ExpressionUtils.ValidateOneArgument(MethodBase method, ExpressionType nodeKind, Expression arguments, ParameterInfo pi, String methodParamName, String argumentParamName, Int32 index)
   at System.Linq.Expressions.Expression.Call(MethodInfo method, Expression arg0, Expression arg1, Expression arg2)
   at Microsoft.EntityFrameworkCore.Query.Internal.ParameterExtractingExpressionVisitor.VisitMethodCall(MethodCallExpression methodCallExpression)
   at Microsoft.EntityFrameworkCore.Query.Internal.ParameterExtractingExpressionVisitor.Visit(Expression expression)
   at System.Dynamic.Utils.ExpressionVisitorUtils.VisitArguments(ExpressionVisitor visitor, IArgumentProvider nodes)
   at System.Linq.Expressions.ExpressionVisitor.VisitMethodCall(MethodCallExpression node)
   at Microsoft.EntityFrameworkCore.Query.Internal.ParameterExtractingExpressionVisitor.VisitMethodCall(MethodCallExpression methodCallExpression)
   at Microsoft.EntityFrameworkCore.Query.Internal.ParameterExtractingExpressionVisitor.Visit(Expression expression)
   at Microsoft.EntityFrameworkCore.Query.Internal.ParameterExtractingExpressionVisitor.ExtractParameters(Expression expression, Boolean clearEvaluatedValues)
   at Microsoft.EntityFrameworkCore.Query.Internal.ParameterExtractingExpressionVisitor.ExtractParameters(Expression expression)
   at Microsoft.EntityFrameworkCore.Query.Internal.QueryCompiler.ExtractParameters(Expression query, IParameterValues parameterValues, IDiagnosticsLogger`1 logger, Boolean parameterize, Boolean generateContextAccessors)
   at Microsoft.EntityFrameworkCore.Query.Internal.QueryCompiler.CreateCompiledQuery[TResult](Expression query)
   at Microsoft.EntityFrameworkCore.Query.Internal.CompiledQuery`2.CreateCompiledQuery(IQueryCompiler queryCompiler, Expression expression)
   at Microsoft.EntityFrameworkCore.Query.Internal.CompiledQueryBase`2.<>c.<EnsureExecutor>b__7_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.CompiledQueryBase`2.ExecuteCore(TContext context, Object[] parameters)
   at Microsoft.EntityFrameworkCore.Query.Internal.CompiledQuery`2.Execute[TParam1](TContext context, TParam1 param1)

Since running those raw SQL queries with FromSqlRaw() works fine when used directly (without trying to compile it as as compiled query) my question is whether FromSqlRaw() is even supported for compiled queries.

My first attempt at this was using a much more complex raw SQL but I then tried with the above select * from ... and the exception remains the same.

Include provider and version information

EF Core version: 8.0.7 Database provider: npgsql Target framework: (e.g. .NET 8.0) Operating system: Windows 10 IDE: (e.g. Visual Studio 2022 17.4)

[roji]

This isn't something we've seen people attempting before, and there's a good chance there are some blockers to it. Putting on the backlog.