Anthony Chu

How SQL queries are generated by Entity Framework depends largely on how the LINQ queries are written. One example of this is how EF decides whether it will parameterize a query. Whenever possible, creating parameterized queries will allow SQL Server to cache a single version of a query and reuse the same execution plan for future executions of the same query that only differ by parameter values.

As a general rule of thumb, when EF parses a LINQ expression tree and it encounters a variable, it will create a parameterized query. When it encounters a constant, it will simply place the constant in the query it generates.

With the Skip(Int32) and Take(Int32) extension methods on IQueryable<T>, integers are passed into the methods and there is no way for EF to figure out if a constant or variable was passed in. For example, this LINQ expression:

context.Users.OrderBy(u => u.Id)
    .Skip(10)
    .Take(5)
    .ToList();

... translates into a SQL query with hardcoded constants:

SELECT 
    [Extent1].[Id] AS [Id], 
    [Extent1].[UserName] AS [UserName]
    FROM [dbo].[AspNetUsers] AS [Extent1]
    ORDER BY [Extent1].[Id] ASC
    OFFSET 10 ROWS FETCH NEXT 5 ROWS ONLY 

Luckily, Entity Framework 6 includes a lambda version of the Skip() and Take() extensions (available in System.Data.Entity.QueryableExtensions). These generate LINQ expression trees that retain whether a value came from a constant or variable.

When used with constants:

using System.Data.Entity;
// ...
context.Users.OrderBy(u => u.Id)
    .Skip(() => 10)
    .Take(() => 5)
    .ToList();

The result is the same query as before:

SELECT 
    [Extent1].[Id] AS [Id], 
    [Extent1].[UserName] AS [UserName]
    FROM [dbo].[AspNetUsers] AS [Extent1]
    ORDER BY [Extent1].[Id] ASC
    OFFSET 10 ROWS FETCH NEXT 5 ROWS ONLY 

We will only see a difference when used with variables:

using System.Data.Entity;
// ...
int skipRecords = 10;
int takeRecords = 5;
context.Users.OrderBy(u => u.Id)
    .Skip(() => skipRecords)
    .Take(() => takeRecords)
    .ToList();

The resulting SQL statement is parameterized and can be cached efficiently by SQL Server:

SELECT 
    [Extent1].[Id] AS [Id], 
    [Extent1].[UserName] AS [UserName]
    FROM [dbo].[AspNetUsers] AS [Extent1]
    ORDER BY [Extent1].[Id] ASC
    OFFSET @p__linq__0 ROWS FETCH NEXT @p__linq__1 ROWS ONLY 

-- p__linq__0: '10' (Type = Int32, IsNullable = false)
-- p__linq__1: '5' (Type = Int32, IsNullable = false)