How to avoid Query Plan re-compilation when using IEnumerable.Contains in Entity Framework LINQ queries?

I have the following LINQ query executed using Entity Framework (v6.1.1):

private IList<Customer> GetFullCustomers(IEnumerable<int> customersIds)
{
    IQueryable<Customer> fullCustomerQuery = GetFullQuery();
    return fullCustomerQuery.Where(c => customersIds.Contains(c.Id)).ToList();
}

This query is translated into fairly nice SQL:

SELECT 
[Extent1].[Id] AS [Id], 
[Extent1].[FirstName] AS [FirstName]
-- ...
FROM [dbo].[Customer] AS [Extent1]
WHERE [Extent1].[Id] IN (1, 2, 3, 5)

However, I get a very significant performance hit on a query compilation phase. Calling:

ELinqQueryState.GetExecutionPlan(MergeOption? forMergeOption)

Takes ~50% of the time of each request. Digging deeper, it turned out that query gets re-compiled every time I pass different customersIds. According to MSDN article, this is an expected behavior because IEnumerable that is used in a query is considered volatile and is part of SQL that is cached. That's why SQL is different for every different combination of customersIds and it always has different hash that is used to get compiled query from cache.

Now the question is: How can I avoid this re-compilation while still querying with multiple customersIds?

This is a great question. First of all, here are a couple of workarounds that come to mind (they all require changes to the query):

First workaround

This one maybe a bit obvious and unfortunately not generally applicable: If the selection of items you would need to pass over to Enumerable.Contains already exists in a table in the database, you can write a query that calls Enumerable.Contains on the corresponding entity set in the predicate instead of bringing the items into memory first. An Enumerable.Contains call over data in the database should result in some kind of JOIN-based query that can be cached. E.g. assuming no navigation properties between Customers and SelectedCustomers, you should be able to write the query like this:

var q = db.Customers.Where(c => 
    db.SelectedCustomers.Select(s => s.Id).Contains(c.Id));

The syntax of the query with Any is a bit simpler in this case:

var q = db.Customers.Where(c => 
    db.SelectedCustomers.Any(s => s.Id == c.Id));

If you don't already have the necessary selection data stored in the database, you will probably don't want the overhead of having to store it, so you should consider the next workaround.

Second workaround

If you know beforehand that you will have a relatively manageable maximum number of elements in the list you can replace Enumerable.Contains with a tree of OR-ed equality comparisons, e.g.:

var list = new [] {1,2,3};
var q = db.Customers.Where(c => 
    list[0] == c.Id ||
    list[1] == c.Id ||
    list[2] == c.Id );

This should produce a parameterized query that can be cached. If the list varies in size from query to query, this should produce a different cache entry for each list size. Alternatively you could use a list with a fixed size and pass some sentinel value that you know will never match the value argument, e.g. 0, -1, or alternatively just repeat one of the other values. In order to produce such predicate expression programmatically at runtime based on a list, you might want to consider using something like PredicateBuilder.

Potential fixes and their challenges

On one hand, changes necessary to support caching of this kind of query using CompiledQuery explicitly would be pretty complex in the current version of EF. The key reason is that the elements in the IEnumerable<T> passed to the Enumerable.Contains method would have to translate into a structural part of the query for the particular translation we produce, e.g.:

var list = new [] {1,2,3};
var q = db.Customers.Where(c => list.Contains(c.Id)).ToList();

The enumerable “list” looks like a simple variable in C#/LINQ but it needs to be translated to a query like this (simplified for clarity):

SELECT * FROM Customers WHERE Id IN(1,2,3)

If list changes to new [] {5,4,3,2,1}, and we would have to generate the SQL query again!

SELECT * FROM Customers WHERE Id IN(5,4,3,2,1)

As a potential solution, we have talked about leaving generated SQL queries open with some kind of special place holder, e.g. store in the query cache that just says

SELECT * FROM Customers WHERE Id IN(<place holder>)

At execution time, we could pick this SQL from the cache and finish the SQL generation with the actual values. Another option would be to leverage a Table-Valued Parameter for the list if the target database can support it. The first option would probably work ok only with constant values, the latter requires a database that supports a special feature. Both are very complex to implement in EF.

Auto compiled queries

On the other hand, for automatic compiled queries (as opposed to explicit CompiledQuery) the issue becomes somewhat artificial: in this case we compute the query cache key after the initial LINQ translation, hence any IEnumerable<T> argument passed should have already been expanded into DbExpression nodes: a tree of OR-ed equality comparisons in EF5, and usually a single DbInExpression node in EF6. Since the query tree already contains a distinct expression for each distinct combination of elements in the source argument of Enumerable.Contains (and therefore for each distinct output SQL query), it is possible to cache the queries.

However even in EF6 these queries are not cached even in the auto compiled queries case. The key reason for that is that we expect the variability of elements in a list to be high (this has to do with the variable size of the list but is also exacerbated by the fact that we normally don't parameterize values that appear as constants to the query, so a list of constants will be translated into constant literals in SQL), so with enough calls to a query with Enumerable.Contains you could produce considerable cache pollution.

We have considered alternative solutions to this as well, but we haven't implemented any yet. So my conclusion is that you would be better off with the second workaround in most cases if as I said, you know the number of elements in the list will remain small and manageable (otherwise you will face performance issues).

Hope this helps!

As of now, this is still a problem in Entity Framework Core when using the SQL Server Database Provider.

I wrote QueryableValues to solve this problem in a flexible and performant way; with it you can compose the values from an IEnumerable<T> in your query, like if it were another entity in your DbContext.

In contrast to other solutions out there, QueryableValues achieves this level of performance by:

Resolving with a single round-trip to the database.
Preserving the query's execution plan regardless of the provided values.

Usage example:

// Sample values.
IEnumerable<int> values = Enumerable.Range(1, 10);

// Using a Join.
var myQuery1 = 
    from e in dbContext.MyEntities
    join v in dbContext.AsQueryableValues(values) on e.Id equals v 
    select new
    {
        e.Id,
        e.Name
    };

// Using Contains.
var myQuery2 = 
    from e in dbContext.MyEntities
    where dbContext.AsQueryableValues(values).Contains(e.Id)
    select new
    {
        e.Id,
        e.Name
    };

You can also compose complex types!

It's available as a nuget package and the project can be found here. It's distributed under the MIT license.

The benchmarks speak for themselves.

I had this exact challenge. Here is how I tackled this problem for either strings or longs in an extension method for IQueryables.

To limit the caching pollution we create the same query with a multitude n of m (configurable) parameters, so 1 * m, 2 * m etc. So if the setting is 15; The queryplans would have either 15, 30, 45 etc parameters, depending on the number of elements in the contains (we don't know in advance, but probably less than 100) limiting the number of query plans to 3 if the biggest contains is less than or equal to 45.

The remaining parameters are filled with a placeholdervalue that (we know) doesn't exists in the database. In this case '-1'

Resulting query part;

... WHERE [Filter1].[SomeProperty] IN (@p__linq__0,@p__linq__1, (...) ,@p__linq__19)
... @p__linq__0='SomeSearchText1',@p__linq__1='SomeSearchText2',@p__linq__2='-1',
(...) ,@p__linq__19='-1'

Usage:

ICollection<string> searchtexts = .....ToList();
//or
//ICollection<long> searchIds = .....ToList();

//this is the setting that is relevant for the resulting multitude of possible queryplans
int itemsPerSet = 15;

IQueryable<MyEntity> myEntities = (from c in dbContext.MyEntities
select c)
.WhereContains(d => d.SomeProperty, searchtexts, "-1", itemsPerSet);

The extension method:

using System;
using System.Collections;
using System.Collections.Generic;
using System.Linq;
using System.Linq.Expressions;

namespace MyCompany.Something.Extensions
{

public static class IQueryableExtensions
    {
        public static IQueryable<T> WhereContains<T, U>(this IQueryable<T> source, Expression<Func<T,U>> propertySelector, ICollection<U> identifiers, U placeholderThatDoesNotExistsAsValue, int cacheLevel)
        {
            if(!(propertySelector.Body is MemberExpression))
            {
                throw new ArgumentException("propertySelector must be a MemberExpression", nameof(propertySelector));
            }

            var propertyExpression = propertySelector.Body as MemberExpression;
            var propertyName = propertyExpression.Member.Name;

            return WhereContains(source, propertyName, identifiers, placeholderThatDoesNotExistsAsValue, cacheLevel);
        }

        public static IQueryable<T> WhereContains<T, U>(this IQueryable<T> source, string propertyName, ICollection<U> identifiers, U placeholderThatDoesNotExistsAsValue, int cacheLevel)
        {
            return source.Where(ContainsPredicateBuilder<T, U>(identifiers, propertyName, placeholderThatDoesNotExistsAsValue, cacheLevel));
        }

        public static Expression<Func<T, bool>> ContainsPredicateBuilder<T,U>(ICollection<U> ids, string propertyName, U placeholderValue, int cacheLevel = 20)
        {
            if(cacheLevel < 1)
            {
                throw new ArgumentException("cacheLevel must be greater than or equal to 1", nameof(cacheLevel));
            }

            Expression<Func<T, bool>> predicate;

            var propertyIsNullable = Nullable.GetUnderlyingType(typeof(T).GetProperty(propertyName).PropertyType) != null;

            // fill a list of cachableLevel number of parameters for the property, equal the selected items and padded with the placeholder value to fill the list.

            Expression finalExpression = Expression.Constant(false);
            var parameter = Expression.Parameter(typeof(T), "x");
            /* factor makes sure that this query part contains a multitude of m parameters (i.e. 20, 40, 60, ...), 
             * so the number of query plans is limited even if lots of users have more than m items selected */
            int factor = Math.Max(1, (int)Math.Ceiling((double)ids.Count / cacheLevel));
            for (var i = 0; i < factor * cacheLevel; i++)
            {
                U id = placeholderValue;
                if (i < ids.Count)
                {
                    id = ids.ElementAt(i);
                }

                var temp = new { id };
                var constant = Expression.Constant(temp);
                var field = Expression.Property(constant, "id");
                var member = Expression.Property(parameter, propertyName);
                if (propertyIsNullable)
                {
                    member = Expression.Property(member, "Value");
                }
                var expression = Expression.Equal(member, field);
                finalExpression = Expression.OrElse(finalExpression, expression);

            }

            predicate = Expression.Lambda<Func<T, bool>>(finalExpression, parameter);


            return predicate;
        }
    }
}

How to avoid Query Plan re-compilation when using IEnumerable.Contains in Entity Framework LINQ queries?

Related

Recent Posts