使用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: 无法将 LINQ 表达式 'GroupBy([s.InternalUser].UserName, [s])' 翻译成语句并将本地计算。 Microsoft.EntityFrameworkCore.Query:Warning: 无法将 LINQ 表达式 'where ([s].Version == 1)' 翻译成语句并将本地计算。 Microsoft.EntityFrameworkCore.Query:Warning: 无法将 LINQ 表达式 'Count()' 翻译成语句并将本地计算。 Microsoft.EntityFrameworkCore.Query:Warning: 无法将 LINQ 表达式 'where ([s].Version == 1)' 翻译成语句并将本地计算。 Microsoft.EntityFrameworkCore.Query:Warning: 无法将 LINQ 表达式 'Count()' 翻译成语句并将本地计算。 Microsoft.EntityFrameworkCore.Query:Warning: 无法将 LINQ 表达式 'where ([s].Version > 1)' 翻译成语句并将本地计算。 Microsoft.EntityFrameworkCore.Query:Warning: 无法将 LINQ 表达式 'Count()' 翻译成语句并将本地计算。 Microsoft.EntityFrameworkCore.Query:Warning: 无法将 LINQ 表达式 'where ([s].Version > 1)' 翻译成语句并将本地计算。 Microsoft.EntityFrameworkCore.Query:Warning: 无法将 LINQ 表达式 'Count()' 翻译成语句并将本地计算。

如果我删除了Count()查询，那么 Group By 就会转化为查询。

是否有其他编写方式可以将其翻译成像我之前发布的 SQL 查询那样的内容呢？

- Valuator

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();

- Ivan Stoev

---

为什么我们要避免使用 Count 的谓词版本？ - Jonathan Wood

@JonathanWood 因为它没有直接的 SQL 等价物（而条件 Sum 有），所以它要么不被翻译（EF Core 到目前为止），要么得到更差和低效的翻译（EF6）。 - Ivan Stoev