Force Entity Framework to use SQL parameterization for better SQL proc cache reuse

Question

Entity Framework always seems to use constants in generated SQL for values provided to Skip() and Take().

In the ultra-simplified example below:

int x = 10; int y = 10;  var stuff = context.Users     .OrderBy(u => u.Id)     .Skip(x)     .Take(y)     .Select(u => u.Id)     .ToList();  x = 20;  var stuff2 = context.Users     .OrderBy(u => u.Id)     .Skip(x)     .Take(y)     .Select(u => u.Id)     .ToList(); 

the above code generates the following SQL queries:

SELECT TOP (10)  [Extent1].[Id] AS [Id] FROM ( SELECT [Extent1].[Id] AS [Id], row_number() OVER (ORDER BY [Extent1].[Id] ASC) AS [row_number]     FROM [dbo].[User] AS [Extent1] )  AS [Extent1] WHERE [Extent1].[row_number] > 10 ORDER BY [Extent1].[Id] ASC  SELECT TOP (10)  [Extent1].[Id] AS [Id] FROM ( SELECT [Extent1].[Id] AS [Id], row_number() OVER (ORDER BY [Extent1].[Id] ASC) AS [row_number]     FROM [dbo].[User] AS [Extent1] )  AS [Extent1] WHERE [Extent1].[row_number] > 20 ORDER BY [Extent1].[Id] ASC 

Resulting in 2 Adhoc plans added to the SQL proc cache with 1 use each.

What I'd like to accomplish is to parameterize the Skip() and Take() logic so the following SQL queries are generated:

EXEC sp_executesql N'SELECT TOP (@p__linq__0)  [Extent1].[Id] AS [Id] FROM ( SELECT [Extent1].[Id] AS [Id], row_number() OVER (ORDER BY [Extent1].[Id] ASC) AS [row_number]     FROM [dbo].[User] AS [Extent1] )  AS [Extent1] WHERE [Extent1].[row_number] > @p__linq__1 ORDER BY [Extent1].[Id] ASC',N'@p__linq__0 int,@p__linq__1 int',@p__linq__0=10,@p__linq__1=10  EXEC sp_executesql N'SELECT TOP (@p__linq__0)  [Extent1].[Id] AS [Id] FROM ( SELECT [Extent1].[Id] AS [Id], row_number() OVER (ORDER BY [Extent1].[Id] ASC) AS [row_number]     FROM [dbo].[User] AS [Extent1] )  AS [Extent1] WHERE [Extent1].[row_number] > @p__linq__1 ORDER BY [Extent1].[Id] ASC',N'@p__linq__0 int,@p__linq__1 int',@p__linq__0=10,@p__linq__1=20 

This results in 1 Prepared plan added to the SQL proc cache with 2 uses.

I have some fairly complex queries and am experiencing significant overhead (on the SQL Server side) on the first run, and much faster execution on subsequent runs (since it can use the plan cache). Note that these more advanced queries already use sp_executesql as other values are parameterized so I'm not concerned about that aspect.

The first set of queries generated above basically means any pagination logic will create a new entry in the plan cache for each page, bloating the cache and requiring the plan generation overhead to be incurred for each page.

Can I force Entity Framework to parameterize values? I've noticed for other values e.g. in Where clauses, sometimes it parameterizes values, and sometimes it uses constants.

Am I completely out to lunch? Is there any reason why Entity Framework's existing behavior is better than the behavior I desire?

Edit: In case it's relevant, I should mention that I'm using Entity Framework 4.2.

Edit 2: This question is not a duplicate of Entity Framework/Linq to SQL: Skip & Take, which merely asks how to ensure that Skip and Take execute in SQL instead of on the client. This question pertains to parameterizing these values.

Answer 1

Update: the Skip and Take extension methods that take lambda parameters described below are part of Entity Framework from version 6 and onwards. You can take advantage of them by importing the System.Data.Entity namespace in your code.

In general LINQ to Entities translates constants as constants and variables passed to the query into parameters.

The problem is that the Queryable versions of Skip and Take accept simple integer parameters and not lambda expressions, therefore while LINQ to Entities can see the values you pass, it cannot see the fact that you used a variable to pass them (in other words, methods like Skip and Take don't have access to the method's closure).

This not only affects the parameterization in LINQ to Entities but also the learned expectation that if you pass a variable to a LINQ query the latest value of the variable is used every time you re-execute the query. E.g., something like this works for Where but not for Skip or Take:

var letter = ""; var q = from db.Beattles.Where(p => p.Name.StartsWith(letter));  letter = "p"; var beattle1 = q.First(); // Returns Paul  letter = "j"; var beattle2 = q.First(); // Returns John 

Note that the same peculiarity also affects ElementAt but this one is currently not supported by LINQ to Entities.

Here is a trick that you can use to force the parameterization of Skip and Take and at the same time make them behave more like other query operators:

public static class PagingExtensions {     private static readonly MethodInfo SkipMethodInfo =          typeof(Queryable).GetMethod("Skip");      public static IQueryable<TSource> Skip<TSource>(         this IQueryable<TSource> source,          Expression<Func<int>> countAccessor)     {         return Parameterize(SkipMethodInfo, source, countAccessor);     }      private static readonly MethodInfo TakeMethodInfo =          typeof(Queryable).GetMethod("Take");      public static IQueryable<TSource> Take<TSource>(         this IQueryable<TSource> source,          Expression<Func<int>> countAccessor)     {         return Parameterize(TakeMethodInfo, source, countAccessor);     }      private static IQueryable<TSource> Parameterize<TSource, TParameter>(         MethodInfo methodInfo,          IQueryable<TSource> source,          Expression<Func<TParameter>>  parameterAccessor)     {         if (source == null)              throw new ArgumentNullException("source");         if (parameterAccessor == null)              throw new ArgumentNullException("parameterAccessor");         return source.Provider.CreateQuery<TSource>(             Expression.Call(                 null,                  methodInfo.MakeGenericMethod(new[] { typeof(TSource) }),                  new[] { source.Expression, parameterAccessor.Body }));     } } 

The class above defines new overloads of Skip and Take that expect a lambda expression and can hence capture variables. Using the methods like this will result in the variables being translated to parameters by LINQ to Entities:

int x = 10;        int y = 10;         var query = context.Users.OrderBy(u => u.Id).Skip(() => x).Take(() => y);         var result1 = query.ToList();  x = 20;   var result2 = query.ToList(); 

Hope this helps.

Answer 2

The methods Skip and Top of ObjectQuery<T> can be parametrized. There is an example at MSDN.

I did a similar thing in a model of my own and sql server profiler showed the parts

SELECT TOP (@limit) 

and

WHERE [Extent1].[row_number] > @skip 

So, yes. It can be done. And I agree with others that this is a valuable observation you made here.