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

Question

实体框架似乎总是在生成的 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

导致 2 个 Adhoc 计划添加到 SQL proc 缓存，每个计划使用 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 proc 缓存有 2 次用途。

我有一些相当复杂的查询，并且在第一次运行时遇到了巨大的开销（在 SQL Server 端），并且在后续运行中执行速度更快（因为它可以使用计划缓存）。请注意，这些更高级的查询已经使用 sp_executesql，因为其他值已参数化，因此我不关心这方面。

上面生成的第一组查询基本上意味着任何分页逻辑都会在计划缓存中为每个页面创建一个新条目，从而使缓存膨胀并要求为每个页面产生计划生成开销。

我可以强制实体框架参数化值吗？我注意到对于其他值，例如在 Where 子句中，有时它参数化值，有时它使用常量。

我完全出去吃午饭了吗？实体框架的现有行为有什么理由比我想要的行为更好吗？

编辑： 如果相关的话，我应该提到我正在使用 Entity Framework 4.2。

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

Answer 1

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

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

问题是 Skip 和 Take 的 Queryable 版本接受简单的整数参数而不是 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();

希望这会有所帮助。

Answer 2

ObjectQuery 的方法 Skip 和 Top 可以参数化。 MSDN 中有一个示例。

我在自己的模型中做了类似的事情，sql server profiler 显示了这些部分

SELECT TOP (@limit)

，

WHERE [Extent1].[row_number] > @skip

所以，是的。这是可以做到的。我同意其他人的观点，即这是您在这里所做的宝贵观察。