Using Linq to select maximum value in a group

Question

Oh, I just find that the error is caused by another part of code. Case closed.

I have 2 tables

1- userinfo

id uid name 
1 11 Billy
2 22 Paul
3 33 Joshua

2- Score

 id uid score
 1  11  30
 2  22  40
 3  11  50
 4  11  60
 5  33  20
 6  33  70
 7  33  80

I have a class called ScoreUser

public class ScoreUser{
public long uid{get; set;}
public string name{get;set;}
public int score{get;set;}
}

I want to use linq to query the above two tables, get the maximum score of each user and map it into the ScoreUser Object. I use the following code:

from s in Scores
join i in UserInfos
on s.uid equals i.uid
group uscore by new { s.uid, i.name} into g
let maxScore = g.Max(p => p.score)
select new ScoreUser
{
uid = g.Key.uid,
name = g.Key.name,
score = maxScore
 }

However, this code does does not work. It produces 7 objects instead of 3. What should I do?

Answer 1

var list = records.GroupBy(p => p.Year, (key, g) => g.OrderByDescending(y => y.Month).First()).ToList();

Above query will return a list that includes the highest month item by grouping the years.

Answer 2

for someone's flavor, providing here two LINQ using lambda expressions to select maximum value in a group

LINQ using lambda expressions

qry = Scores.Join(UserInfos, s => s.uid, i => i.uid, (s, i) => new { s, i })
     .GroupBy(g => new { g.s.uid, g.i.name })
     .Select(g => new ScoreUser
     {
        uid = g.Key.uid,
        name = g.Key.name,
        score = g.Max(p => p.s.score)
     });

LINQ(Lambda) using LastOrDefault() to get Max() value, so the class ScoreUser() can be eliminated here.

var qry = Scores.Join(UserInfos, s => s.uid, i => i.uid, (s, i) => new { s, i })
         .GroupBy(g => new { g.s.uid, g.i.name })
         .Select(g => new { su = g.OrderBy(i => i.s.score) })
         .Select(x => x.su.LastOrDefault()).ToList();

Both get same results as LINQ with query syntax.

This document may be interested LINQ Query Syntax versus Method Syntax.