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.