I am looking for a way in LINQ to match the follow SQL Query.
Select max(uid) as uid, Serial_Number from Table Group BY Serial_Number
Really looking for some help on this one. The above query gets the max uid of each Serial Number because of the Group By
Syntax.
This question is related to
.net
linq
linq-to-sql
In methods chain form:
db.Serials.GroupBy(i => i.Serial_Number).Select(g => new
{
Serial_Number = g.Key,
uid = g.Max(row => row.uid)
});
var q = from s in db.Serials
group s by s.Serial_Number into g
select new {Serial_Number = g.Key, MaxUid = g.Max(s => s.uid) }
This can be done using GroupBy and SelectMany in LINQ lamda expression
var groupByMax = list.GroupBy(x=>x.item1).SelectMany(y=>y.Where(z=>z.item2 == y.Max(i=>i.item2)));
I've checked DamienG's answer in LinqPad. Instead of
g.Group.Max(s => s.uid)
should be
g.Max(s => s.uid)
Thank you!
The answers are OK if you only require those two fields, but for a more complex object, maybe this approach could be useful:
from x in db.Serials
group x by x.Serial_Number into g
orderby g.Key
select g.OrderByDescending(z => z.uid)
.FirstOrDefault()
... this will avoid the "select new"
Source: Stackoverflow.com