强制实体框架使用SQL参数化以获得更好的SQL过程缓存重用

Question

Entity Framework 始终似乎在生成的 SQL 中使用 Skip() 和 Take() 提供的值的常量。

在下面这个极简化的示例中：

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查询：

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

导致在 SQL 过程缓存中添加了 2 个 Adhoc 计划，每个计划使用了 1 次。

我的目标是将 Skip() 和 Take() 逻辑参数化，以生成以下 SQL 查询：

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过程缓存中，并使用2次。

我有一些相当复杂的查询，第一次运行时会在SQL Server端产生显着的开销，而后续运行则会更快（因为它可以使用计划缓存）。请注意，这些更高级的查询已经使用sp_executesql，因为其他值是参数化的，所以我不担心这方面的问题。

以上生成的第一组查询基本上意味着任何分页逻辑都会为每个页面创建一个新的计划缓存条目，导致缓存膨胀并需要为每个页面承担计划生成开销。

我可以强制Entity Framework参数化值吗？对于其他的值，例如在“Where”子句中，有时它会参数化值，有时它会使用常量。

我完全错了吗？Entity Framework现有的行为是否比我期望的行为更好？

编辑：如有关联，请注意我正在使用Entity Framework 4.2。

编辑2：这个问题不是Entity Framework/Linq to SQL: Skip & Take的重复，它只是询问如何确保Skip和Take在SQL中执行而不是在客户端上执行。这个问题涉及参数化这些值。

- GWB

---

这个链接解释了如何使用Linq与SQL Params，您需要向下滚动到链接底部查看解释和示例。LinqPad网站- http://www.linqpad.net/WhyLINQBeatsSQL.aspx - MethodMan

很好的观察。我通常不会在“真正”的项目中使用EF，只是用来玩弄小东西，以前从未注意到这种行为。如果EF没有对所有可以参数化的内容进行参数化，那么我认为这是一个巨大的缺陷。 - CodingWithSpike

很棒的问题 - 你会认为他们已经优化了可能的计划重用。 - BrokenGlass

@DJ KRAZE：你的链接解释了Linq可以使用参数化SQL，但没有关于控制何时发生的内容。 - GWB

对于GWB我很抱歉，我误解了你的问题。 - MethodMan

Answer 1

更新：下面描述的接受 lambda 参数的 Skip 和 Take 扩展方法是 Entity Framework 6 及以上版本的一部分。您可以通过在代码中导入 System.Data.Entity 命名空间来利用它们。

通常，LINQ to Entities 将常量作为常量翻译，并将传递给查询的变量转换为参数。

问题在于，Queryable 版本的 Skip 和 Take 接受简单的整数参数而不是 lambda 表达式，因此虽然 LINQ to Entities 可以看到您传递的值，但它无法看到您使用变量来传递它们（换句话说，像 Skip 和 Take 这样的方法无法访问方法的闭包）。

这不仅影响了 LINQ to Entities 中的参数化，还影响了学习期望，即如果您将变量传递给 LINQ 查询，则每次重新执行查询时都使用变量的最新值。例如，对于 Where 这样的语句可以工作，但对于 Skip 或 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

请注意，相同的特性也影响ElementAt，但是LINQ to Entities目前不支持该操作符。

下面是一个小技巧，可以强制使用参数化的Skip和Take，并且使它们的行为更像其他查询运算符：

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

上面的类定义了新的Skip和Take重载方法，它们期望一个lambda表达式，并且可以捕获变量。使用这些方法将导致变量被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();

希望这能帮到您。

- divega

---

很棒的回答。我认为看到 EF 团队成员与社区互动非常棒！ - GWB

看起来这个解决方案中的方法现在已经内置于EF6中了。 :) - GWB

是的 :) 我们将它们作为 IQueryable<T> 的扩展方法添加了。您只需要将 System.Data.Entity 命名空间引入作用域，就可以使用 lambda 参数编写 Skip 和 Take。我会更新答案。 - divega

Answer 2

ObjectQuery<T>的方法Skip和Top可以被参数化。在MSDN上有一个示例。

我在自己的模型中做了类似的事情，SQL Server Profiler显示了相关部分。

SELECT TOP (@limit)

并且

WHERE [Extent1].[row_number] > @skip

所以，没错。这是可以做到的。我同意其他人对于你在这里提出的有价值的观察。

- Gert Arnold

---

有趣的是他们添加了一个Top方法而不是重用Take。我正在跳过/获取匿名投影，因此查询不属于ObjectQuery<T>类型，所以我不确定是否可以使用这种方法。但这确实给了我一些调查的东西。 - GWB

是的，这些方法似乎在使用上非常有限。我只能直接在ObjectSet上让它们起作用。甚至在将结果明确转换为ObjectQuery时，也不能在Where()的结果上使用它们（_查询构建器方法不支持LINQ到实体查询。有关更多信息，请参阅Entity Framework文档。_） - Gert Arnold