LINQ: How to get the latest/last record with a group by clause

Sometimes, we need to select a record that has the maximum or minimum value from a group. Some examples are:

• We have a PersonOrders table and we want to find the last Order for each Person
• We have a UserOperations table and we need to find the first Operation for each User
• We have a Persons table and we need to find the person with maximum salary for each Department

Such scenarios are beautifully handled by TSQL’s Ranking_functions over partition by clauses. Here’s a typical example that uses Row_Number() function to assign a rank to each row per Person.

;With PersonOrderWithRank 
as
(
	Select *, Rnk = ROW_NUMBER() over (partition by PersonID order by OrderDate desc)
	from PersonOrders
)

Select * 
from PersonOrderWithRank
where Rnk=1

In LINQ, similar result can be achieved by using the let keyword. Here’s an example:

from p in PersonOrders
//where conditions or joins with other tables to be included here
group p by p.PersonID into grp
let MaxOrderDatePerPerson = grp.Max ( g=>g.OrderDate )

from p in grp
where p.OrderDate == MaxOrderDatePerPerson
select p

Another more compact method would be to retrieve the first record in the grouping like this:

from p in PersonOrders
//where conditions or joins with other tables to be included here
group p by p.PersonID into grp
select grp.OrderByDescending(g=>g.OrderDate).First()

The above LINQ approaches can be used for both LINQ To SQL as well as Entity Framework. Although the SQL and the LINQ approaches I described above are not exactly identical (since I used Row_Number() and not Rank() or Dense_Rank() ) but the purpose of this post is to provide an starting point to write similar queries using LINQ.