力实体框架使用SQL参数化更好的SQL PROC缓存重用 [英] Force Entity Framework to use SQL parameterization for better SQL proc cache reuse

问题描述

实体框架似乎总是使用常量在生成的SQL的服务提供给值跳过（）和取（）

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

上面的代码生成以下SQL查询：

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

在2即席计划所得到SQL PROC缓存，每使用1

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

我想什么来完成是参数化跳过（）和取（）逻辑，所以下面的SQL查询生成的：

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

这将导致1准备计划添加到SQL PROC高速缓存2的用途。

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

我有一些相当复杂的查询和我遇到的后续运行在第一次运行显著开销（在SQL Server端），以及更快的执行（因为它可以使用计划缓存）。请注意，这些更高级的查询已经使用sp_executesql的其他值参数，所以我不担心这方面。

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?

编辑：
如果它是相关的，我要指出，我使用实体框架4.2

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

编辑2：
这个问题是不是<一个副本HREF =htt​​p://stackoverflow.com/questions/3870091/entity-framework-linq-to-sql-skip-take>实体框架/ LINQ到SQL：跳过&放大器;就拿，这仅仅是询问如何保证跳过和执行在SQL中，而不是客户端上执行。这个问题涉及到参数设置这些值

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.

推荐答案

的 更新：跳过和Take扩展即采取以下拉姆达参数的方法是从6及以后版本的实体框架的一部分。你可以在你的代码导入System.Data.Entity的命名空间中利用它们。的

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.

在一般的LINQ到实体转化为常量传递常量和变量到查询到的参数。

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

的问题是，跳过的可查询的版本，并以接受简单的整数参数，而不是lambda表达式，因此而LINQ到实体可以看到你传递的价值观，它不能看您使用的可变传递他们（换句话说，像跳过方法和以不具有访问该方法的关闭）的事实。

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

这不仅影响在LINQ的参数来实体而且还学会了期望，如果你传递一个变量LINQ查询变量的最新值用于每次重新执行查询的时间。例如，像这样的工作，但如果不进行跳跃或带走：

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.StartsWithName(letter));

letter = "p";
var beattle1 = q.First(); // Returns Paul

letter = "j";
var beattle2 = q.First(); // Returns John

请注意，同样的特点也影响ElementAt的，但是这个人是目前不支持LINQ到实体。

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

类以上定义跳过的新重载，并采取预计lambda表达式，并因此可以捕捉变量。使用方法，如这将导致变量通过LINQ被翻译成参数实体：

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.

这篇关于力实体框架使用SQL参数化更好的SQL PROC缓存重用的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！