Linq to SQL生成SQL超时，如何改写查询匹配手动SQL性能？

我编写的Linq语句生成的SQL执行超时，但手动编写的等价SQL可在1秒内完成。

目标SQL：

SELECT MONTH(s.modified), s.site, s.campus, s.className, s.old, s.barcode, l.name, COUNT(*)
FROM dbo.Scrapped s
JOIN dbo.Location l ON s.barcode = l.spaceBarcode AND l.type = 'lab'
WHERE s.modified >= '2023-01-01'
GROUP BY MONTH(s.modified), s.site, s.campus, s.className, s.old, s.barcode, l.name

当前Linq代码：

from s in context.Scrapped.AsNoTracking()
join l in context.Location on new { type = "lab", spaceBarcode = s.barcode } equals new { l.type, l.spaceBarcode }
where s.modified >= date
group new { s, l } by new { s.modified.Month, s.site, s.campus, s.className, s.old, s.barcode, l.name } into grp
select new Scrapped2DTO {
    Month = grp.Key.Month,
    Site = grp.Key.site,
    Class = grp.Key.className,
    Barcode = grp.Key.barcode,
    Campus = grp.Key.campus,
    Old = grp.Key.old,
    Name = grp.Key.name,
    Count = grp.Count()
};

生成的SQL包含额外嵌套查询及不必要的空值判断，导致执行耗时极长：

SELECT [t].[Month], N'' AS [MonthName], [t].[site] AS [Site], [t].[className] AS [Class], [t].[barcode] AS [Barcode], [t].[campus] AS [Campus], [t].[old] AS [Old], [t].[name] AS [Name], COUNT(*) AS [Count]
FROM (
    SELECT [s].[barcode], [s].[campus], [s].[className], [s].[old], [s].[site], [s0].[name], DATEPART(month, [s].[modified]) AS [Month]
    FROM [Scrapped] AS [s]
    INNER JOIN [Location] AS [s0] ON N'lab' = [s0].[type] AND ([s].[barcode] = [s0].[spaceBarcode] OR (([s].[barcode] IS NULL) AND ([s0].[spaceBarcode] IS NULL)))
    WHERE [s].[modified] >= @__date_0
) AS [t]
GROUP BY [t].[Month], [t].[site], [t].[campus], [t].[className], [t].[old], [t].[barcode], [t].[name]

如何改写Linq查询以解决该问题？

核心问题有两点：

1. Join条件中把常量type="lab"放在匿名对象左侧，导致EF生成冗余的空值判断逻辑，破坏索引使用效率。
2. 分组逻辑触发EF生成嵌套子查询，增加查询开销。

改写后的Linq代码：

from s in context.Scrapped.AsNoTracking()
where s.modified >= date
join l in context.Location.Where(loc => loc.type == "lab") 
    on s.barcode equals l.spaceBarcode
group new { s, l } by new 
{ 
    Month = s.modified.Month, 
    s.site, 
    s.campus, 
    s.className, 
    s.old, 
    s.barcode, 
    l.name 
} into grp
select new Scrapped2DTO 
{
    Month = grp.Key.Month,
    Site = grp.Key.site,
    Class = grp.Key.className,
    Barcode = grp.Key.barcode,
    Campus = grp.Key.campus,
    Old = grp.Key.old,
    Name = grp.Key.name,
    Count = grp.Count()
};

关键调整点： #

• 提前过滤Location表：先通过Where(loc => loc.type == "lab")筛选出目标类型的记录，再执行Join操作，避免在Join条件中混入常量判断，消除冗余的空值逻辑。
• 前置时间过滤：将s.modified >= date的条件放在Join之前，先缩小Scrapped表的数据集，减少后续关联的数据量。

改写后EF将生成与手动SQL结构一致的查询，无嵌套子查询，能充分利用索引实现高效执行。

内容的提问来源于stack exchange，提问作者Gargoyle