Linq to Entities 3.5 where most recent date

Question

I have a table and for the sake of the example lets say it has three columns: Name, DateAdded, Note. In this table there will be multiple records with the same name. I need a linq where clause that will get me a result set with the most recent record for each name.

So if I had the following information:

Name, DateAdded, Note
Alpha, 1/1/2010, note one
Alpha, 1/2/2010, note two
Alpha, 1/3/2010, note three
Beta, 1/4/2010, note four
Beta, 1/5/2010, note five

I would expect to get the following results:

Name, DateAdded, Note
Alpha, 1/3/2010, note three
Beta, 1/5/2010, note five

In real use this is going to be a search option so I need it to be a .Where clause I can append to an IQueryable collection if possible.

Answer 1

from row in rows
group row by row.Name into grouping
from g in grouping
where g.DateAdded == grouping.Max(x => x.DateAdded)
select g;

If there's more than one row with the maximum DateAdded in some group, this returns all such rows.

You don't need just one Where clause. If rows is a IQueryable, then the result of this query is too, so you can use it as a part of a chain. But I have no idea how well does this query translate to SQL.