重写 LINQ 表达式查询以启用缓存 SQL 执行计划

本文关键字：缓存 SQL 执行 计划 启用 LINQ 表达式 查询 重写 | 更新日期: 2023-09-27 18:30:41

在阅读有关实体框架性能的文章时，我遇到了以下信息：

其次，[SQL Server不会重用执行计划]的问题首先发生，因为（由于实现细节）当将int传递给Skip（）和Take（）方法时，实体框架无法看到它们是传递绝对值（如Take（100））或变量（如Take（resultsPerPage），因此它不知道是否应该参数化该值。

建议的解决方案是更改以下代码样式：

var schools = db.Schools
    .OrderBy(s => s.PostalZipCode)
    .Skip(model.Page * model.ResultsPerPage)
    .Take(model.ResultsPerPage)
    .ToList();

在这种风格中：

int resultsToSkip = model.Page * model.ResultsPerPage;
var schools = db.Schools
    .OrderBy(s => s.PostalZipCode)
    .Skip(() => resultsToSkip) //must pre-calculate this value
    .Take(() => model.ResultsPerPage)
    .ToList();

这允许实体框架知道这些是变量，并且生成的 SQL 应该被参数化，这反过来又允许重用执行计划。

我们的应用程序中有一些代码以相同的方式使用变量，但我们必须在运行时构建表达式，因为事先不知道类型。

这是它过去的样子：

var convertedId = typeof(T).GetConvertedIdValue(id);
var prop = GetIdProperty(typeof(T));
var itemParameter = Expression.Parameter(typeof(T), "item");
var whereExpression = Expression.Lambda<Func<T, bool>>
    (
    Expression.Equal(
        Expression.Property(
            itemParameter,
            prop.Name
            ),
        Expression.Constant(convertedId)
        ),
    new[] { itemParameter }
    );
return Get<T>().Where(whereExpression);

问题是使用 Expression.Constant(convertedId) 会导致将常量插入到生成的 SQL 中。这会导致您查找的每个新项的 SQL 都会更改，从而停止任何执行计划缓存：

WHERE [Extent1].[Id] = 1234

和：

WHERE [Extent1].[Id] = 1235

和：

WHERE [Extent1].[Id] = 1236

那么问题来了，你如何使用表达式构建来强制对生成的SQL进行参数化？() => convertedId语法将不起作用。我在下面回答了这个问题。

重写 LINQ 表达式查询以启用缓存 SQL 执行计划

经过大量的试验和错误，我们发现您仍然可以通过稍微更改传入的方式来强制实体框架将convertedId识别为参数：

....
var convObj = new
{
    id = convertedId
};
var rightExp = Expression.Convert(Expression.Property(Expression.Constant(convObj), "id"), convertedId.GetType());
var whereExpression = Expression.Lambda<Func<T, bool>>
    (
    Expression.Equal(
        Expression.Property(
            itemParameter,
            prop.Name
            ),
        rightExp
        ),
    new[] { itemParameter }
    );
return Get<T>().Where(whereExpression);

这会导致生成的 SQL 对任何给定 id 使用相同的参数（和代码）：

WHERE [Extent1].[Id] = @p__linq__0

我们正在处理的有问题的查询需要很长时间才能生成执行计划，因此我们看到访问新 ID 的执行时间显着减少（从 3~4 秒减少到 ~300 毫秒）

让我回顾一下。

您正在构建这样的Expression<Func<T, bool>>

var item = Expression.Parameter(typeof(T), "item");
var left = Expression.Property(item, idPropertyName);
Expression right = ...;
var body = Expression.Equal(left, right);
var predicate = Expression.Lambda<Func<T, bool>>(body, item);

问题是应该对right使用什么才能使 EF 不将其视为常量。

显然是原始值，例如 

var right = Expression.Convert(Expression.Constant(convertedId), left.Type);

不起作用，因此解决方案是提供某个类实例的属性。 您通过使用匿名类型解决了它，但当然还有许多其他方法可以做到这一点。

例如，使用闭包（就像您不手动创建表达式时一样） 

Expression<Func<object>> closure = () => convertedId;
var right = Expresion.Convert(closure.Body, left.Type);

或Tuple<T>实例（有点冗长，但消除了Expression.Convert） 

var tuple = Activator.CreateInstance(
    typeof(Tuple<>).MakeGenericType(left.Type), convertedId);
var right = Expression.Property(Expression.Constant(tuple), "Item1");

等。

相关文章：