Rewriting a LINQ expression query to enable caching an SQL execution plan

Question

While reading an article on Entity Framework performance, I came across this information:

Secondly, the problem [SQL Server will not reuse the execution plan] occurs firstly, because (due to implementation details), when passing an int to the Skip () and Take () methods, the Entity Framework cannot determine if they were absolute values, such as Take (100), or a variable of type Take (resultsPerPage) are passed, so it does not know whether to value the parameter.

The proposed solution is to change this code style:

var schools = db.Schools .OrderBy(s => s.PostalZipCode) .Skip(model.Page * model.ResultsPerPage) .Take(model.ResultsPerPage) .ToList(); 

In this style:

 int resultsToSkip = model.Page * model.ResultsPerPage; var schools = db.Schools .OrderBy(s => s.PostalZipCode) .Skip(() => resultsToSkip) //must pre-calculate this value .Take(() => model.ResultsPerPage) .ToList(); 

This allows the Entity Framework to know that these are variables, and that the generated SQL must be parameterized, which in turn allows reuse of the execution plan.

We have some code in our application that uses variables in the same way, but we need to build an expression at runtime because the type is not known in advance.

Here's how it looked:

 var convertedId = typeof(T).GetConvertedIdValue(id); var prop = GetIdProperty(typeof(T)); var itemParameter = Expression.Parameter(typeof(T), "item"); var whereExpression = Expression.Lambda<Func<T, bool>> ( Expression.Equal( Expression.Property( itemParameter, prop.Name ), Expression.Constant(convertedId) ), new[] { itemParameter } ); return Get<T>().Where(whereExpression); 

The problem is that using Expression.Constant(convertedId) causes the constant to be inserted into the generated SQL. This forces SQL to change every new item you are viewing, which stops the execution plan caching:

 WHERE [Extent1].[Id] = 1234 

and

 WHERE [Extent1].[Id] = 1235 

and

 WHERE [Extent1].[Id] = 1236 

The question then is, How can you use the creation of an expression in such a way as to force parameterization of the generated SQL? Syntax () => convertedId will not work. I answered this below.

Answer 1

After a lot of trial and error, we found that you can still force the Entity Framework to recognize convertedId as a parameter by slightly changing the transfer method:

 .... var convObj = new { id = convertedId }; var rightExp = Expression.Convert(Expression.Property(Expression.Constant(convObj), "id"), convertedId.GetType()); var whereExpression = Expression.Lambda<Func<T, bool>> ( Expression.Equal( Expression.Property( itemParameter, prop.Name ), rightExp ), new[] { itemParameter } ); return Get<T>().Where(whereExpression); 

Forces the generated SQL to use the same parameter (and code) for any given id:

 WHERE [Extent1].[Id] = @p__linq__0 

In the question that we talked about, it takes a long time to create an execution plan, so we saw a significant reduction in execution time for access to new identifiers (from 3 to 4 seconds to ~ 300 milliseconds)

Answer 2

Let me repeat it.

You build Expression<Func<T, bool>> as follows

 var item = Expression.Parameter(typeof(T), "item"); var left = Expression.Property(item, idPropertyName); Expression right = ...; var body = Expression.Equal(left, right); var predicate = Expression.Lambda<Func<T, bool>>(body, item); 

and the question is what should be used for right so that EF does not treat it as a constant.

Apparently primitive meaning like

 var right = Expression.Convert(Expression.Constant(convertedId), left.Type); 

doesn't work, so the solution should provide a property of some instance of the class . You solved this using an anonymous type, but of course there are many other ways to do this.

For example, using closure (for example, it would be if you did not create the expression manually)

 Expression<Func<object>> closure = () => convertedId; var right = Expresion.Convert(closure.Body, left.Type); 

or Tuple<T> instance (bit verbose, but excludes Expression.Convert )

 var tuple = Activator.CreateInstance( typeof(Tuple<>).MakeGenericType(left.Type), convertedId); var right = Expression.Property(Expression.Constant(tuple), "Item1"); 

etc..