EF Core 2.0 Group By other properties

user_852619 0
Viewed 449

I have 2 tables:

USERS
UserId
Name
Scores (collection of table Scores)

SCORES
UserId
CategoryId
Points

I need to show all the users and a SUM of their points, but also I need to show the name of the user. It can be filtered by CategoryId or not.

Context.Scores
.Where(p => p.CategoryId == categoryId) * OPTIONAL
.GroupBy(p => p.UserId)
.Select(p => new 
{
    UserId = p.Key,
    Points = p.Sum(s => s.Points),
    Name = p.Select(s => s.User.Name).FirstOrDefault()
}).OrderBy(p => p.Points).ToList();

The problem is that when I add the 

Name = p.Select(s => s.User.Name).FirstOrDefault()

It takes so long. I don't know how to access the properties that are not inside the GroupBy or are a SUM. This example is very simple becaouse I don't have only the Name, but also other properties from User table.

How can I solve this?

entity-framework-core group-by c#
1 Answers

It takes so long because the query is causing client evaluation. See Client evaluation performance issues and how to use Client evaluation logging to identify related issues.

If you are really on EF Core 2.0, there is nothing you can do than upgrading to v2.1 which contains improved LINQ GroupBy translation. Even with it the solution is not straight forward - the query still uses client evaluation. But it could be rewritten by separating the GroupBy part into subquery and joining it to the Users table to get the additional information needed.

Something like this:

var scores = db.Scores.AsQueryable();
// Optional
// scores = scores.Where(p => p.CategoryId == categoryId);

var points = scores
     .GroupBy(s => s.UserId)
     .Select(g => new
     {
         UserId = g.Key,
         Points = g.Sum(s => s.Points),
     });

var result = db.Users
    .Join(points, u => u.UserId, p => p.UserId, (u, p) => new
    {
        u.UserId,
        u.Name,
        p.Points
    })
    .OrderBy(p => p.Points)
    .ToList();

This still produces a warning

The LINQ expression 'orderby [p].Points asc' could not be translated and will be evaluated locally.

but at least the query is translated and executes as single SQL:

SELECT [t].[UserId], [t].[Points], [u].[UserId] AS [UserId0], [u].[Name]
FROM [Users] AS [u]
INNER JOIN (
    SELECT [s].[UserId], SUM([s].[Points]) AS [Points]
    FROM [Scores] AS [s]
    GROUP BY [s].[UserId]
) AS [t] ON [u].[UserId] = [t].[UserId]
user_52025630
Related