EF Core 3.1: Group by Sum

Question

Let's suppose I have a list of player scores that looks somewhat like this:

public class ScoreEntry
{
  public int PlayerId {get; set;}
  public int Score {get; set;}
  public string SomeOtherValue {get;set;}
}

And there are multiple entries for a single player, so I want to group those score entries and sum up their score values, something like this:

  var query =
      from entry in scores
      group entry by entry.PlayerId into scoreEntryGroup
      select new ScoreEntry()
      {
        PlayerId = scoreEntryGroup.Key,
        Amount = scoreEntryGroup.Sum(g => g.Score),
        SomeOtherValue = scoreEntryGroup.FirstOrDefault().SomeOtherValue
      };

It throws an exception and I know that the problem is that I can't use SingleOrDefault() and many other similar methods in a group in EF Core 3.1

Could you help me find another way to achieve this? Any help would be highly appreciated!

Answer 1

Try the following query. It uses CROSS APPLY for joining to aggregation result.

var aggregations = 
    from entry in scores
    group entry by entry.PlayerId into scoreEntryGroup
    select new 
    {
        PlayerId = scoreEntryGroup.Key,
        Amount = scoreEntryGroup.Sum(g => g.Score)
    };
    
var query =
    from a in aggregations
    from entry in scores
        .Where(entry => entry.PlayerId == a.PlayerId)
        .Take(1)
    select new ScoreEntry()
    {
        PlayerId = entry.PlayerId,
        SomeOtherValue = entry.SomeOtherValue,

        Amount = a.Amount
    };