Rewriting a LINQ Expression query to enable caching SQL Execution Plan

Question

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

Secondly, the problem [SQL Server won’t reuse the execution plan] occurs in the first place because (due to an implementation detail) when passing an int to the Skip() and Take() methods, Entity Framework can’t see whether they were passed absolute values like Take(100), or a variable like Take(resultsPerPage), so it doesn’t know whether the value should be parameterized.

The proposed solution is to change this style of code:

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

In to 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();

Which allows Entity Framework to know that these are variables and that the generated SQL should be parametrized, which in turn allows the execution plan to be reused.

We have some code in our application that uses variables in the same way, but we must build the Expression at runtime as the type is not known in advance.

Here is what it used to look like:

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 a constant to be inserted in to the generated SQL. This causes the SQL to change for every new item you look up, which stops any 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 Expression building in such a way as to force the parametrization of the generated SQL? The () => convertedId syntax will not work. I have answered this below.

Answer 1

After a lot of trial and error, we found you can still force Entity Framework to recognise convertedId as a parameter by slightly changing how we pass it in:

....

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);

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

WHERE [Extent1].[Id] = @p__linq__0 

The query in question that we were dealing with takes a long time to generate the execution plan, so we saw a significant decrease in execution time for accessing new IDs (from 3~4 seconds down to ~300 milliseconds)