使用 GROUP BY 和 COUNT 将查询转换为 Linq [英] Translating query with GROUP BY and COUNT to Linq

问题描述

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

I have a query to see how many entities Users have inserted (Version = 1) and entities they've updated (Version > 1). It queries the entire table and groups by the UserName of the record. This is the raw SQL query:

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 查询:

This returns what I want it to. I've tried translating this to a Linq query in a project using EF Core 2.2:

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

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

However this results the entire table being loaded and the computations being done in memory:

Microsoft.EntityFrameworkCore.Query:警告:LINQ 表达式GroupBy([s.InternalUser].UserName, [s])"无法翻译，将在本地进行评估.Microsoft.EntityFrameworkCore.Query:警告:LINQ 表达式where ([s].Version == 1)"无法翻译，将在本地进行评估.Microsoft.EntityFrameworkCore.Query:警告:LINQ 表达式Count()"无法翻译，将在本地进行评估.Microsoft.EntityFrameworkCore.Query:警告:LINQ 表达式where ([s].Version == 1)"无法翻译，将在本地进行评估.Microsoft.EntityFrameworkCore.Query:警告:LINQ 表达式Count()"无法翻译，将在本地进行评估.Microsoft.EntityFrameworkCore.Query:警告:LINQ 表达式where ([s].Version > 1)"无法翻译，将在本地进行评估.Microsoft.EntityFrameworkCore.Query:警告:LINQ 表达式Count()"无法翻译，将在本地进行评估.Microsoft.EntityFrameworkCore.Query:警告:LINQ 表达式where ([s].Version > 1)"无法翻译，将在本地进行评估.Microsoft.EntityFrameworkCore.Query:警告:LINQ 表达式Count()"无法翻译，将在本地进行评估.

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 被转换为查询.

It's the Count() query that causes it, if I remove that the Group By is translated to the query.

是否有其他不同的编写方式可以转换为我之前发布的 SQL 查询之类的内容?

Is there a different way of writing this that would translate to something like the SQL Query I posted before?

推荐答案

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

Avoid predicate version of Count and use the equivalent conditional Sum.

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

In EF Core 3.0+ you can directly replace Count(condition) with Sum(condition ? 1 : 0), e.g.

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 重载和 element 来预选所需的表达式选择器，例如

EF Core 2.x supports translation only for GroupBy aggregates on simple grouping element property accessors, so you need to preselect the required expressions by using the GroupBy overload with element selector, e.g.

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

这篇关于使用 GROUP BY 和 COUNT 将查询转换为 Linq的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！