SQL Query to Linq GroupBy and Max

Question

Here is a SQL query that I am trying to convert in to Linq. I am using a generic list of objects and not a DataTable if that is relevant.

Select Max(Date), ID, Property1, Peroperty2 From List Group By ID

Please help.

Answer 1

Though your SQL is not valid as Property1 and Property2 must be part of your aggregates, this will group by ID, Property1, then Property2 in that order which is what I believe you are looking for:

from obj in List
group obj by new {obj.ID, obj.Property1, obj.Property2 } into g
select new 
       { 
          ID = g.Key.ID, 
          Property1 = g.Key.Property1, 
          Property2 = g.Key.Property2,  
          MaxDate = g.Max(p => p.Date) 
       }

which is equivalent to :

Select Max(Date), ID, Property1, Peroperty2 
From List 
Group By ID, Property1, Property2

Answer 2

As Steve said, your SQL is not valid without Property1 etc. in the aggregate clause. However, it looks to me like you are trying to get the data for those 'rows' where the date is equal to the maximum date for the group of 'rows' by Id?

This works like that:

recs.GroupBy(r => r.Id).SelectMany(g => g.Where(r => r.Date == g.Max(a => a.Date)))

Groups by Id, gets an IEnumeration of items whose date matches the max date for that group, and flattens them into a single IEnumeration - which can then be iterated over or whatever you want.

Edit: By the way, in query syntax the best I can do (took me a while...) is something like:

from r in recs
where r.Date ==
    (from sr in recs
     where sr.Id == r.Id
     select sr.Date).Max()
select r;

Edit 2:

Which is actually more like:

recs.Where(r => r.Date == recs.Where(sr => sr.Id == r.Id).Max(a => a.Date)).Select(r => r)

in fluent syntax, which does not look as nice to me as the first way I did it... I don't really like query expression syntax, which is funny because I am at ease with SQL and query syntax is supposed to be more SQL like.