Using Linq to select maximum value in a group
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.
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.
You are also grouping by score
when it should be the aggregator. Try this:
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 you found the problem. However I leave you here a test to this query:
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);
}
}
Prints:
UId:11 Name:Billy Score:60
UId:22 Name:Paul Score:40
UId:33 Name:Joshua Score:80