Query plan pollution using EF core 6 Contains

Question

So basically ran into performace issues due to new query plan generating every time. The problem being that values (itemIds) are translated as constants in sql's rpc rather than variables, thus generating separate plans for different combinations rather then one. I found workaround replacing contains statement with multiple OR's using LinqKit:

var filter = PredicateBuilder.New<Foo>();
foreach (var itemId in itemIds)
{
  typeFilter = typeFilter.Or(foo => foo.ItemId == itemId);
}
var filteredItems = contex.Foos.Where(filter).ToList();

And it works. Now I'm trying to move it to extension method for reasons. Wrote extension but it failes to be translated by EF:

public static IQueryable<TSource> WhereContains<TSource, TInItem>(
    this IQueryable<TSource> source,
    IEnumerable<TInItem> inItems,
    Expression<Func<TSource, TInItem>> selector
    ) where TInItem : IComparable
{
    var containsFilter = PredicateBuilder.New<TSource>();
    foreach (var item in inItems)
    {
        containsFilter = containsFilter.Or(x => selector.Invoke(x).Equals(item));
    }

    return source.Where(containsFilter);
}

Also I tried to work with following code, but it genrates same constants just in multiple OR statements rather than on IN. Probably Expression.Constant(item) has to be changed some how but could not figure out how.

public static IQueryable<TSource> WhereContains<TSource, TInItem>(
    this IQueryable<TSource> source, 
    List<TInItem> inItems, 
    Expression<Func<TSource, TInItem>> selector)
{
    var itemParameter = Expression.Parameter(inItems.GetType());
    var selectorParameter = selector.Parameters.Single();
    var itemEqualSelectorBody = Expression.Equal(itemParameter, selector.Body);
    var itemEqualSelectorLambda = Expression.Lambda<Func<TInItem, bool>>(itemEqualSelectorBody, itemParameter);
    var containsFilter = inItems
        .Select(item => Expression.Invoke(itemEqualSelectorLambda, Expression.Constant(item)))
        .Aggregate<Expression>(Expression.OrElse);

    return source.Where(Expression.Lambda<Func<TSource, bool>>(containsFilter, selectorParameter));
}

Can you point my mistakes? Over suggestions and solution will be appreciated.

You may be interested in [this answer](https://stackoverflow.com/a/70765466/861716). — Gert Arnold, Mar 10 '23 at 20:34
@GertArnold saw this, tried it - kinda works but query produced looked more like hack, also produces a sub query which would probably help with big numbers of variables. — Kilas, Mar 10 '23 at 22:20

Guru Stron · Answer 1 · 2023-03-10T20:30:56.370

0

Check the Plugging Expressions into EntitySets / EntityCollections: The Solution part if LinqKit's github page and this tutorial - you need to call AsExpandable() on your query so the Invoke call in the first attempt can be translated by EF:

So try something like:

public static IQueryable<TSource> WhereContains<TSource, TInItem>(
    this IQueryable<TSource> source,
    IEnumerable<TInItem> inItems,
    Expression<Func<TSource, TInItem>> selector
    ) where TInItem : IComparable
{
    var containsFilter = PredicateBuilder.New<TSource>();
    foreach (var item in inItems)
    {
        containsFilter = containsFilter.Or(x => selector.Invoke(x).Equals(item));
    }

    // or call .AsExpandable() on the query root
    return source.AsExpandable().Where(containsFilter);
}

edited Mar 10 '23 at 20:30

answered Mar 10 '23 at 20:05

Guru Stron

102,774
10
95
132

Thanks - totaly worked. Is the any other, more elegant solution? Like where I can use Contains inside Where statement and it woldn't be converted to constants; what about second extension, is it possible to somehow convert from Extension.Constant to Variable? – Kilas Mar 10 '23 at 20:30
@Kilas 1) TBH I don't have SQL Server available ATM and a bit lazy to install it, for Postgres `Where(Contains)` translates into `WHERE a."Name" = ANY (@__l_0)` for me (EF Core 7) and personally I would expect `WHERE ... IN ...` from SQL Server version 2) I misunderstood your problems with the second one, so removed my take on it. – Guru Stron Mar 10 '23 at 20:34
1

the problem is that `Select(Contains)` get translated to `WHERE ItemId in ('1','2','3')` rather than `WHERE ItemId in (@itemId_1,@itemId_2,@itemId_3)` where `'1','2','3'` are constants in rpc rather then variables (`@`). This means that if there is diferent combination than 1,2,3 then new plan will be generated for each combination and that hurts out db a lot – Kilas Mar 10 '23 at 20:50

Query plan pollution using EF core 6 Contains

1 Answers1