c# - Translating query with GROUP BY and COUNT to Linq

Question

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]

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

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

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

Answer 1

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

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