将具有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()查询.

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重载与元素选择器一起使用来预先选择所需的表达式，例如

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屋！