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!

CodePudding user response：

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
    };