EF Core 5 - In memory object list filtering not working against DBset

user_9822454 0
Viewed 381

I need to filter against a DBset using a list of objects passed in as a variable to my repo.

The following worked in EF6

List<MyFilterObj> incomingList = [{A: 1, B: 2}, {A: 3, B: 4}]

return await context.MyDBset
                .Where(x => incomingList.Any(v =>
                                v.A == x.A &&
                                v.B == x.B)).ToListAsync();

If I try this same approach in EF Core 5 I get the following error:

The LINQ expression (MyDBset) 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 can't seperate the incoming list into seperate A and B lists as the combination is essential to the filter accuracy. Otherwise I could have used this approach

return await context.MyDBset
                    .Where(x => incomingListA.Contains(x.A) &&
                                    incomingListB.Contains(x.B)).ToListAsync();

This approach does work in EF Core 5

Basically I want EF to translate my query into something like:

SELECT * FROM MyTable AS m
Where (m.A = 1 AND m.B = 2) OR (m.A = 3 AND m.B = 4)

If I loop through my incomingList adding "where" filters to a query the resultant SQL uses "AND" instead of "OR" like so:

SELECT * FROM MyTable AS m
Where (m.A = 1 AND m.B = 2) AND (m.A = 3 AND m.B = 4)

Right now my work around involves binging the result set into memory with some basic filters then running an in memory filter using the original syntax. This is obviously not ideal.

Any suggestions? Thanks

entity-framework-core .net-core linq c#
1 Answers

Since EF Core is not willing to translate such expression (it's a well known limitation that Contains with primitive value is the only supported operation on in-memory collection in L2E query since it directly translates to SQL IN operator), you have to do it yourself. Any is basically equivalent of Or as you mentioned, so for not so big list and top level query it could easily be done by using some predicate builder implementation to dynamically build || predicate. Or directly using the the Expression class methods as in this custom extension method from my answer to How to simplify repetitive OR condition in Where(e => e.prop1.contains() || e.prop2.contains() || ...):


public static partial class QueryableExtensions
{
    public static IQueryable<T> WhereAnyMatch<T, V>(this IQueryable<T> source, IEnumerable<V> values, Expression<Func<T, V, bool>> match)
    {
        var parameter = match.Parameters[0];
        var body = values
            // the easiest way to let EF Core use parameter in the SQL query rather than literal value
            .Select(value => ((Expression<Func<V>>)(() => value)).Body)
            .Select(value => Expression.Invoke(match, parameter, value))
            .Aggregate<Expression>(Expression.OrElse);
        var predicate = Expression.Lambda<Func<T, bool>>(body, parameter);
        return source.Where(predicate);
    }
}

with sample usage for your scenario:

context.MyDBset
    .WhereAnyMatch(incomingList, (x, v) => v.A == x.A && v.B == x.B)

Normally instead of Expression.Invoke helper utilities like this would use custom ExpressionVisitor to replace the parameters of the source expression (thus simulation a "call"), which produces more naturally looking query expression, but for EF Core it doesn't really matter since it recognizes and correctly translates invocation expressions inside the expression tree.

But just in case Expression.Invoke doesn't work, here is the parameter replacing version - it should work with all query providers. First, the helpers


public static partial class ExpressionBuilder
{
    public static Expression ReplaceParameter(this Expression source, ParameterExpression parameter, Expression value)
        => new ParameterReplacer { Parameter = parameter, Value = value }.Visit(source);

    class ParameterReplacer : ExpressionVisitor
    {
        public ParameterExpression Parameter;
        public Expression Value;
        protected override Expression VisitParameter(ParameterExpression node)
            => node == Parameter ? Value : node;
    }
}

and then simply replace the invocation

.Select(value => Expression.Invoke(match, parameter, value))

with

.Select(value => match.Body.ReplaceParameter(match.Parameters[1], value))

because specifically here we are reusing the first parameter (var parameter = match.Parameters[0];), otherwise it should be replaced as well.

user_52025630
Related