LINQ: getting the row with the maximum value of this attribute

Question

I have a group of strings grouped by MyID attribute. Now I need one row from each group, where the StatusDate attribute is the highest in this group.

This is what I came up with.

 rows.Select(x => x.Where(y => y.StatusDate == x.Max(z => z.StatusDate)).First()) 

With a more detailed explanation:

 rows.Select(x => // x is a group x.Where(y => // get all rows in that group where... // the status date is equal to the largest // status date in the group y.StatusDate == x.Max(z => z.StatusDate) ).First()) // and then get the first one of those rows 

Is there a faster or more idiomatic way to do this?

Answer 1

One of the options:

 rows.Select(x => x.OrderByDescending(y => y.StatusDate).First()); 

... and make sure the query optimizer knows that you don’t really need to sort everything. (This would be a disaster in LINQ for objects, but you could use MaxBy from MoreLINQ in this case :)

(Apologies for the previous version - I did not fully understand the grouping bit.)

Answer 2

I don’t know if this is Linq for SQL, but if so, you can alternatively execute the rank () function in SQL (rank each group by date, then select the first ranked row from each), then call it as a stored procedure from LINQ I think a statement that is becoming more idiomatic as people fall into LINQ2SQL stupid ...