How to run dynamic query containing subquery with EF Core?

ChristofSenn 0
Viewed 33

I'm able to run all sorts of dynamically composed queries with EF Core except when containing sub-queries.

Hence, this is not a duplicate of EF Core dynamic lambda subquery not working or any other I was able to find for that matter.

A runnable repro to demonstrate the issue can be found on dotnetfiddle.net/4opEqr that uses a dynamically composed expression representing the following query:

efContext.Products.Where(p => p.Id == efContext.OrderItem.Max(i => i.ProductId)).ToList();

The exception I get is

System.InvalidOperationException : The LINQ expression 'InternalDbSet<OrderItem> {  }
    .Max(i => i.ProductId)' 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'. See https://go.microsoft.com/fwlink/?linkid=2101038 for more information.

I observe the same behaviour with Microsoft.EntityFrameworkCore.SqlServer as with Microsoft.EntityFrameworkCore.InMemory (versions 6.0.8 and 7.0.0-preview.7.22376.2).

subquery entity-framework-core
1 Answers

The problem seems to be both the way EF Core processes nested queryable expressions inside expression tree, and the way you are trying to test the dynamic expressions you create.

Shortly, here

productQueryable.Where(p => p.Id == orderItemQueryable.Max(i => i.ProductId))

productQueryable is regular variable (and .Where is regular call) and orderItemQueryable is compiler generated closure (as part of the queryable Where predicate expression), while here

() => productQueryable.Where(p => p.Id == orderItemQueryable.Max(i => i.ProductId))

they both are compiler generated closures.

So, the EF Core does not correctly process Constant expression holding nested queryable variable value inside "root" query expression, so for subqueries you should either pass directly IQueryable.Expression property value, or simulate closure (but not constant) expression. While for root queryable you should either wrap it in constant expression, or in simulated closure (but not directly).

Since wrapping the queryable variables work for both cases, the solution is to always wrap such variables in closure emulating expression. And closure emulating expression is any expression which contains constant expression holding some class instance holding the actual variable inside class property or field.

It can be implemented in several ways, for instance using System.Tuple class as holder:

static Expression MakeClosure<T>(T value)
{
    var closure = new Tuple<T>(value);
    return Expression.Property(Expression.Constant(closure), nameof(closure.Item1));
}

or real compiler generated closure class instance:

static Expression MakeClosure<T>(T value)
{
    var closure = new { value };
    return Expression.Property(Expression.Constant(closure), nameof(closure.value));
}

or the same using the body of compiler generated lambda expression containing closure:

static Expression MakeClosure<T>(T value)
{
    Expression<Func<T>> lambda = () => value;
    return lambda.Body;
}

Finally, in all the cases change the sample code as

var productQueryableExp = MakeClosure(productQueryable);
var orderItemQueryableExp = MakeClosure(orderItemQueryable);

and everything will work as expected. At least with EF Core. What about the other library you seem to be using (Remote.Linq), have no idea (that's I guess would be another question).

Ivan Stoev0
Related