EF生成低效查询：如何优化分组统计查询性能？

我需要统计几个数值，期望的SQL语句如下：

SELECT
detail.detailId,
COUNT(*) as Total,
COUNT(CASE when t.Status = 1 and t.Type = 2 then 1 end) as TotalSub
...

FROM [GeneralDetails] AS [detail]
join Environment t on t.Id = detail.EnvironmentId 
where
([detail].[EnvironmentId] IS NOT NULL)

group by detail.detailId

于是我写了逻辑相近的EF代码：

GeneralDetails
    .Where(detail=> detail.EnvironmentId != null)
    .GroupBy(detail=> detail.detailId)
    .Select(detailGroup=> new
        {
        detailGroup.Key,
        Total = detailGroup.Count(),
        TotalSub = detailGroup.Count(detail=>detail.Environment.Status == Status.Enabled && detail.Environment.Type == Type.General)
        ...
        });

但EF生成了执行极慢的查询，每个统计项都对应独立子查询：

SELECT
[s].[detailId],
COUNT(*) AS [Total],
-- 每个统计项都有类似的独立子查询
(
SELECT COUNT(*)
FROM [GeneralDetails] AS [s0]
LEFT JOIN [Environment] AS [t] ON [s0].[EnvironmentId] = [t].[Id]
WHERE ([s0].[EnvironmentId] IS NOT NULL) AND [s].[detailId] = [s0].[detailId] AND [t].[Status] = 1 AND [t].[Type] = 2) AS [ActiveSub]
...
FROM [GeneralDetails] AS [s]
WHERE [s].[EnvironmentId] IS NOT NULL
GROUP BY [s].[detailId]

更新
我尝试把Count改成Sum：

...
TotalSub = detailGroup.Sum(detail=> detail.Environment.Status == Status.Enabled && detail.Environment.Type == Type.General ? 1 : 0)
...

但生成的查询依然低效：

SELECT [s].[detailId], COUNT(*) AS [Total], (
    SELECT COALESCE(SUM(CASE
        WHEN [t].[Status] = 1 AND [t].[Type] = 2 THEN 1
        ELSE 0
    END), 0)
    FROM [GeneralDetails] AS [s0]
    LEFT JOIN [Environment] AS [t] ON [s0].[EnvironmentId] = [t].[Id]
    WHERE ([s0].[EnvironmentId] IS NOT NULL) AND [s].[detailId] = [s0].[detailId]) AS [TotalSub]
FROM [GeneralDetails] AS [s]
WHERE [s].[EnvironmentId] IS NOT NULL
GROUP BY [s].[detailId]

问题原因 #

EF在分组后引用关联实体属性做条件聚合时，会将每个统计项解析为独立子查询，而非像手动SQL那样在主查询中用CASE表达式聚合。这种处理方式会导致多次扫描表，性能急剧下降。

解决方案 #

要生成高效SQL，需先关联表，再分组聚合，避免分组后再引用关联实体的属性。

写法一（查询表达式）

from detail in GeneralDetails
join env in Environment on detail.EnvironmentId equals env.Id
where detail.EnvironmentId != null
group new { detail, env } by detail.detailId into grouped
select new
{
    detailId = grouped.Key,
    Total = grouped.Count(),
    TotalSub = grouped.Count(item => item.env.Status == Status.Enabled && item.env.Type == Type.General)
    // 其他统计项按同样逻辑添加
}

写法二（方法链）

GeneralDetails
    .Where(d => d.EnvironmentId != null)
    .Join(Environment,
          detail => detail.EnvironmentId,
          env => env.Id,
          (detail, env) => new { Detail = detail, Env = env })
    .GroupBy(joined => joined.Detail.detailId)
    .Select(grouped => new
    {
        detailId = grouped.Key,
        Total = grouped.Count(),
        TotalSub = grouped.Count(item => item.Env.Status == Status.Enabled && item.Env.Type == Type.General)
    });

这样EF会生成和你期望一致的SQL：在主查询中用CASE配合COUNT/SUM聚合，避免子查询，大幅提升性能。

补充：EF Core 3.0+对分组聚合的解析有优化，但提前关联表的方式仍是多统计项场景下最可靠的性能保障。

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