修复了导致性能不佳的LINQ to SQL显式客户端评估

介绍

Web应用程序基于EF3.1。针对生产数据库的SQL分析显示检索了近100,000个表行。

背景

导致返回这么多表行的LINQ查询如下所示：

var responses = (from cr in _dbContext.CarrierResponses where cr.TenantId == tenantId select cr).ToList()
var modified = responses.Where(cr => modifiedLoads.Any(m => cr.LoadId == m.LoadId && cr.DateModified < m.ModifiedDateTimeUtc));

其中modifiedLoads是从应用用户请求传递的修改负载列表。如果租户非常活跃，第一个语句会返回大量记录。

我想知道为什么原始代码作者编写这个查询来返回这么多表记录。从源代码提交历史中，我发现代码是前一段时间编写的，如下所示。

var responses = from cr in _dbContext.CarrierResponses
                where cr.TenantId == tenantId && modifiedLoads.Any(m => cr.LoadId == m.LoadId && cr.DateModified < m.ModifiedDateTimeUtc)
                select cr;

它应该在EF Core 3.0之前工作，默认情况下允许客户端评估。顺便说一句，即使它有效，根据EF Core 3.x中的重大更改下的文档，“这种行为可能导致意外和潜在的破坏性行为，这些行为可能只会在生产中变得明显......可能导致所有行从要从数据库服务器传输的表，以及要应用于客户端的过滤器。”，在这种情况下使用客户端评估不是一个好主意。

该应用程序应该已经用新版本的EF内核升级了一段时间，所以原来的LINQ不再工作，并且像上面显示的那样创建了一个错误修复。它只是让它工作，可能只在生产中变得明显的性能问题没有得到解决。

有关通过linq操作进行显式客户端评估的更多信息，请参阅客户端与服务器评估。

修复问题

1. 通过动态Lambda表达式修复

创建动态表达式函数：

private Expression<Func<CarrierResponse, bool>> FilterCarrierResponsesBy(IEnumerable<OpenLoadModified> modifiedLoads)
{
    var carrierResponseFilterExpression = Expression.Parameter(typeof(CarrierResponse));
    CarrierResponse cr;
    var expressionList = new List<Expression>();
    modifiedLoads.ToList().ForEach(x =>
    {
         var exprLoadIdEqual = Expression.Equal(Expression.Property(carrierResponseFilterExpression, nameof(cr.LoadId)), Expression.Constant(x.LoadId));
         var exprDateTimeLessThan = Expression.LessThan(Expression.Property(carrierResponseFilterExpression, nameof(cr.DateCreated)), Expression.Constant(x.ModifiedDateTimeUtc));
         var andExp = Expression.And(exprLoadIdEqual, exprDateTimeLessThan);
         expressionList.Add(andExp);
    });

    var exprModifiedLoadOr = null as Expression;
    foreach (var expr in expressionList)
    {
        if (exprModifiedLoadOr == null)
        {
            exprModifiedLoadOr = expr;
            continue;
        }
        exprModifiedLoadOr = Expression.Or(exprModifiedLoadOr, expr);
    }
    return Expression.Lambda<Func<CarrierResponse, bool>>(exprModifiedLoadOr, carrierResponseFilterExpression);
}

以这种方式使用此动态表达式：

_dbContext.CarrierResponses.Where(x => x.TenantId == tenantId).Where(FilterCarrierResponsesBy(modifiedLoads));

它生成如下SQL语句：

exec sp_executesql N'SELECT [c].[Id], [c].[CarrierId], [c].[DateCreated], [c].[DateModified], [c].[IsLoadModified], [c].[LoadId], [c].[PrevResponseCode], [c].[PrevUserName], [c].[ResponseCode], [c].[TenantId], [c].[UserName]
FROM [CarrierResponses] AS [c]
WHERE ([c].[TenantId] = @__tenantId_0) AND (((CASE
    WHEN [c].[LoadId] = N''999'' THEN CAST(1 AS bit)
    ELSE CAST(0 AS bit)
END & CASE
    WHEN [c].[DateCreated] < ''2021-12-20T19:13:19.6866667'' THEN CAST(1 AS bit)
    ELSE CAST(0 AS bit)
END) | (CASE
    WHEN [c].[LoadId] = N''1000'' THEN CAST(1 AS bit)
    ELSE CAST(0 AS bit)
END & CASE
    WHEN [c].[DateCreated] < ''2021-12-20T19:13:19.6866667'' THEN CAST(1 AS bit)
    ELSE CAST(0 AS bit)

与使用客户端评估的现有代码的“读取：43，持续时间：35186”相比，它运行得如此之快，例如来自Profiler的“读取：57，持续时间：3662”。

2.通过存储过程修复

首先，创建一个用户定义的数据类型（自SQL Server 2012起支持）：

CREATE TYPE [dbo].[OpenLoadModified] AS TABLE(
    [LspId] [nvarchar](10) NOT NULL,
    [LoadId] [nvarchar](30) NOT NULL,
    [ModifiedDateTimeUtc] [datetime2](7) NULL,
    PRIMARY KEY CLUSTERED 
  (
    [LspId] ASC,
    [LoadId] ASC
  ) WITH (IGNORE_DUP_KEY = OFF)
)

然后创建存储过程：

CREATE PROC [dbo].[GetCarrierResponses]
(
    @TenantId BIGINT,
    @LoadsModified OpenLoadModified READONLY
)
AS
BEGIN
    SELECT r.*
    FROM CarrierResponses AS r WITH(NOLOCK)
    JOIN @LoadsModified AS m ON r.[LoadId] = m.[LoadId]
    WHERE r.TenantId = @TenantId AND r.DateModified < m.ModifiedDateTimeUtc
END

最后，在应用程序的业务部分，使用以下代码：

var dtModifiedLoads = new DataTable();
dtModifiedLoads.Columns.Add("LspId", typeof(string));
dtModifiedLoads.Columns.Add("LoadId", typeof(string));
dtModifiedLoads.Columns.Add("ModifiedDateTimeUtc", typeof(DateTime));

foreach (var load in modifiedLoads)
{
    var row = dtModifiedLoads.NewRow();
    row["LspId"] = load.LspId;
    row["LoadId"] = load.LoadId;
    row["ModifiedDateTimeUtc"] = load.ModifiedDateTimeUtc;
    dtModifiedLoads.Rows.Add(row);
}

_DAL.GetDataSetBySqlParameter("[dbo].[GetCarrierResponses]", new SqlParameter[] {
    new SqlParameter("@TenantId", tenantId),
    new SqlParameter("@LoadsModified", SqlDbType.Structured)
    {
        TypeName = "dbo.OpenLoadModified",
        Value = dtModifiedLoads
    }
});

它生成这些SQL语句：

declare @p2 dbo.OpenLoadModified
insert into @p2 values(N'7929497',N'999','2021-12-20 19:13:19.6866667')
insert into @p2 values(N'7929497',N'1000','2021-12-20 19:13:19.6866667')

exec [dbo].[GetCarrierResponses] @TenantId=30,@LoadsModified=@p2

Profiler报告“读取次数：50，持续时间：4300”。