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.