Should compiled queries work with FromRawSql?
- 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
Assessment
This issue has not been assessed yet.