Using Linq to select the maximum value in a group

Question

Oh, I just found that the error was caused by another piece of code. Case is closed.

I have 2 tables

1- userinfo

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

2- Evaluation

  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 ScoreUser class

 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 two above tables, get the maximum score of each user and match it with the ScoreUser object. I am using 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 not work. It creates 7 objects instead of 3. What should I do?

Answer 1

You also group score when it should be an aggregator. Try the following:

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

(update)

I see that you have found the problem. However, I leave you a test here for this request:

  class UserInfo { public int Id { get; set; } public int UId { get; set; } public string Name { get; set; } } class Score { public int Id { get; set; } public int UId { get; set; } public int SScore { get; set; } } public class ScoreUser { public int uid { get; set; } public string name { get; set; } public int score { get; set; } public override string ToString() { return string.Format("UId:{0} Name:{1} Score:{2}", uid, name, score); } } static void Main(string[] args) { List<UserInfo> infos = new List<UserInfo>() { new UserInfo {Id = 1, UId = 11, Name = "Billy"}, new UserInfo {Id = 2, UId = 22, Name = "Paul"}, new UserInfo {Id = 3, UId = 33, Name = "Joshua"} }; List<Score> scores = new List<Score>() { new Score {Id = 1, UId = 11, SScore = 30}, new Score {Id = 2, UId = 22, SScore = 40}, new Score {Id = 3, UId = 11, SScore = 50}, new Score {Id = 4, UId = 11, SScore = 60}, new Score {Id = 5, UId = 33, SScore = 20}, new Score {Id = 6, UId = 33, SScore = 70}, new Score {Id = 7, UId = 33, SScore = 80} }; var qry = from s in scores join i in infos on s.UId equals i.UId group s by new { s.UId, i.Name } into g select new ScoreUser { uid = g.Key.UId, name = g.Key.Name, score = g.Max(p => p.SScore) }; foreach (var su in qry) { Console.WriteLine(su); } } 

Print

 UId:11 Name:Billy Score:60 UId:22 Name:Paul Score:40 UId:33 Name:Joshua Score:80