LINQ: Getting the row with the maximum value of a given attribute

Question

I have a bunch of rows grouped on an attribute called MyID. Now I want the one row from each group where the StatusDate attribute is the highest in that one group.

This is what I've come up with.

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

With a bit more 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 any faster or more idiomatic way to do this?

Answer 1

One alternative would be to use:

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

... and check that the query optimiser knows that it doesn't really need to sort everything. (This would be disastrous in LINQ to Objects, but you could use MaxBy from MoreLINQ in that case :)

(Apologies for previous version - I hadn't fully comprehended the grouping bit.)

Answer 2

Don't know if this is Linq to SQL, but if it is, you could alternatively accomplish via a rank() function in SQL (rank each group by date, then select the first ranked row from each), then call this as a stored proc from LINQ. I think that's an approch that is becoming more idiomatic as people hit the bounderies of LINQ2SQL...