EF 生成低效查询-解网

问：

我需要得到一些计数：

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

所以在代码中我尝试了这个，这似乎非常接近原始查询：

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

但是，它会生成此查询，这非常慢：

  SELECT
   [s].[detailId],
    COUNT(*) AS [Total],
    -- with separate subquery for every counter similar to this
    (
    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]

我错过了什么？如何强制EF生成更快的查询？

更新将代码更改为

...
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]

C# 实体框架核心 EF-Core-7.0

使 EF 陷入困境的一个问题是处理可选关系。预期方案之间的一个显著区别是 GroupDetail 和 Environment 之间的差异，其中 EF 将在两者之间使用 an，因为 Environment 是可选的，即使在这种情况下，你只对具有环境的 GroupDetails 感兴趣。关于能够在查询级别指定这一点，已经有一些来回，但AFAIK对此没有采取任何明确的措施。一种可能有助于提高性能的方法是反转查询，但仅当 GroupDetail 和 Environments 之间的关系是双向的时，这才是一个选项，否则它需要显式：INNER JOINOUTER JOINJoin

var details = _context.Environments
    .SelectMany(e => e.GroupDetails)
    .GroupBy(detail => detail.detailId)
    .Select(detailGroup=> new
    {
        DetailId = detailGroup.Key,
        Total = detailGroup.Count(),
        TotalSub = detailGroup.Count(detail=>detail.Environment.Status == Status.Enabled && detail.Environment.Type == Type.General)
        ...
    }).SingleOrDefault();

没有双向参考：

var details = _context.Environments
    .GroupJoin(_context.GeneralDetails,
         e => e.EnvironmentId,
         gd => gd.EnvironmentId,
         (e, gd) => gd)
    .GroupBy(gd => gd.detailId)
    .Select(detailGroup=> new
    {
        DetailId = detailGroup.Key,
        Total = detailGroup.Count(),
        TotalSub = detailGroup.Count(detail=>detail.Environment.Status == Status.Enabled && detail.Environment.Type == Type.General)
        ...
    }).SingleOrDefault();

这些应该在表之间产生所需的内部联接。

声明：这些示例是凭记忆编写的，因此，如果它们在语法上不是 100% 正确，并且它们可能不起作用，或者有效但无法获得您喜欢的高效查询，请原谅。最坏的情况是，您始终可以为此边缘情况构建自定义 SQL 语句，以获取使用 .INNER JOIN

0赞 Svyatoslav Danyliv 11/3/2023 #2

Yua 面临着 EF Core 在聚合函数中使用导航属性的限制。作为解决方法，您应该使用适当的重载在运算符中投影导航属性：GroupBy

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

上一个：来自 SQL 的 LINQ-TO-Entities

下一个：EF Core 7.0 （PostgresSQL）生成 IQueryable with RowNumber concept

EF 生成低效查询

EF generates inefficient query

评论