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?
See Question&Answers more detail:
os 与恶龙缠斗过久,自身亦成为恶龙;凝视深渊过久,深渊将回以凝视…