c# - 将使用 GROUP BY 和 COUNT 的查询转换为 Linq

Question

我有一个查询要查看用户插入了多少实体(Version = 1)以及他们更新了多少实体(Version > 1)。它通过记录的用户名查询整个表和组。这是原始 SQL 查询:

SELECT 
    [s.InternalUser].[UserName],
    COUNT(CASE WHEN s.Version = 1 THEN 1 END) AS [InsertCount],
    COUNT(CASE WHEN s.Version > 1 THEN 1 END) AS [UpdateCount]
FROM [Sale] AS [s]
INNER JOIN [InternalUser] AS [s.InternalUser] ON [s].[InternalUserId] = 
    [s.InternalUser].[InternalUserId]
GROUP BY [s.InternalUser].[UserName]

这会返回我想要的结果。我尝试将其转换为使用 EF Core 2.2 的项目中的 Linq 查询:

var countData = await _context.Sale
.GroupBy(s => s.InternalUser.UserName)
.Select(g => new
{
    UserName = g.Key,
    InsertCount = g.Count(s => s.Version == 1),
    UpdateCount = g.Count(s => s.Version > 1)
})
.ToListAsync();

然而，这会导致加载整个表并在内存中完成计算:

Microsoft.EntityFrameworkCore.Query:Warning: The LINQ expression 'GroupBy([s.InternalUser].UserName, [s])' could not be translated and will be evaluated locally. Microsoft.EntityFrameworkCore.Query:Warning: The LINQ expression 'where ([s].Version == 1)' could not be translated and will be evaluated locally. Microsoft.EntityFrameworkCore.Query:Warning: The LINQ expression 'Count()' could not be translated and will be evaluated locally. Microsoft.EntityFrameworkCore.Query:Warning: The LINQ expression 'where ([s].Version == 1)' could not be translated and will be evaluated locally. Microsoft.EntityFrameworkCore.Query:Warning: The LINQ expression 'Count()' could not be translated and will be evaluated locally. Microsoft.EntityFrameworkCore.Query:Warning: The LINQ expression 'where ([s].Version > 1)' could not be translated and will be evaluated locally. Microsoft.EntityFrameworkCore.Query:Warning: The LINQ expression 'Count()' could not be translated and will be evaluated locally. Microsoft.EntityFrameworkCore.Query:Warning: The LINQ expression 'where ([s].Version > 1)' could not be translated and will be evaluated locally. Microsoft.EntityFrameworkCore.Query:Warning: The LINQ expression 'Count()' could not be translated and will be evaluated locally.

是 Count() 查询导致的，如果我删除 Group By 被转换为查询。

是否有不同的编写方式可以转换为类似于我之前发布的 SQL 查询？

Answer 1

避免 Count 的谓词版本并使用等效的条件 Sum。

在 EF Core 3.0+ 中，您可以直接将 Count(condition) 替换为 Sum(condition ? 1 : 0)，例如

var countData = await _context.Sale
    .GroupBy(s => s.InternalUser.UserName)
    .Select(g => new
    {
        UserName = g.Key,
        InsertCount = g.Sum(s => s.Version == 1 ? 1 : 0),
        UpdateCount = g.Sum(s => s.Version > 1 ? 1 : 0),
    })
    .ToListAsync();

EF Core 2.x 仅支持对简单分组元素属性访问器上的 GroupBy 聚合进行转换，因此您需要通过对元素使用 GroupBy 重载来预选所需的表达式选择器，例如

var countData = await _context.Sale
    .GroupBy(s => s.InternalUser.UserName, s => new
    {
        InsertCount = s.Version == 1 ? 1 : 0,
        UpdateCount = s.Version > 1 ? 1 : 0,
    })
    .Select(g => new
    {
        UserName = g.Key,
        InsertCount = g.Sum(s => s.InsertCount),
        UpdateCount = g.Sum(s => s.UpdateCount),
    })
    .ToListAsync();