dotnet / dotnet/efcore

Should compiled queries work with FromRawSql?

Open
#34,374 1 comment 0 reactions 0 assignees View on GitHub
area-query customer-reported
Dominant language
C#
Stars
14.8k
Forks
3.4k
PR merge metrics
PR metrics pending

Description

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

```
private static Func> _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 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.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)

Contributor guide

Open the contributing guide

Assessment

This issue has not been assessed yet.

Get new issues in your inbox

A short digest of beginner-friendly GitHub issues.