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.

About these ads

20 Responses to “LINQ: How to get the latest/last record with a group by clause”

  1. DotNetShoutout Says:

    LINQ: How to get the latest/last record with a group by clause « Mehroz’s Experiments…

    Thank you for submitting this cool story – Trackback from DotNetShoutout…

  2. Nick Olsen Says:

    Thanks a ton! This was exactly what I needed!

  3. Miru Sharma Says:

    I need to convert the following sql query into LINQ: Can anybody help me.

    SELECT*( SELECT ROW_NUMBER() OVER (PARTITION BY fieldname ORDER BY errordate Desc) AS ‘RowNo’,
    id, dcn, fieldname, value, errordate, userid
    FROM input_error where dcn=@dcn and corrected=’N’) where RowNo=1

  4. Syed Mehroz Alam Says:

    @Miru,

    So want to get the record that has the maximum error date per fieldName. Here’s the LINQ query:

    from i in input_error
    where i.Dcn = dcnVariable && i.Corrected = “N”
    group i by i.FieldName into grp
    let maxErrorDate = grp.Max( g=>g.ErrorDate)

    from g in grp
    where g.ErrorDate == maxErrorDate
    select g

  5. Aneesh Daniel Says:

    Thank you very much. This article helped me a lot.

  6. Tato Says:

    Thanks a lot. I think it’s a common situation but your example was the only one that explain how to do it with linq.
    I would have found it before…
    Thanks a lot!

  7. Nick Says:

    Hi

    I’m trying to get the record ID of the record that’s just been saved with Entity Framework. Is there a simple way to accomplish this?

    … or is something like this viable?
    CLIENTS LastestClient = (from c in ent.CLIENTS
    select c).Last();

    I’m still new to EF and can’t find what I’m looking for on the net.

  8. Syed Mehroz Alam Says:

    Nick,

    EF automatically populates your business objects with the db-generated values after your call to SaveChanges(). Something like:

    //create a new Client object
    Client c = new Client();
    c.Name = "Test";
    
    //save to db
    context.AddToClients(c);
    context.SaveChanges();
    
    //EF will populate your original object with the generated values
    Debug.WriteLine( " Client ID is " + c.ClientID ); 
    
  9. Nick Says:

    Awesome.

    Thanks :)

  10. Jim Says:

    I am using Silverlight with EF, and I need to add an Include(). When I modify your code as shown below, I get a null for the linked object X. It works fine when I don’t group. Can anyone help?

    from p in PersonOrders.Include(“X”)
    group p by p.PersonID into grp
    let MaxOrderDatePerPerson = grp.Max ( g=>g.OrderDate )

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

  11. Syed Mehroz Alam Says:

    Jim,

    Include(“X”) should be applied in the last. Have a look at this post from Alex that describes how you need to cast your query into ObjectQuery<T> and then apply Include in the last.

  12. Naresh Mittal Says:

    Wonderful!! Thanks a lot for providing such a simple and efficient solution. Have been searching the web for last 2 days and found here the perfect solution. Great work.

  13. Jigar Says:

    Get the max date record fom List using the single LINQ query….!!!!!!!!!! ( objListView is the ILIST)

    objListView = (from Record in objListView
    where Record.COMPLETED_DATE == (from rcd in objListView select rcd).Max(a => a.COMPLETED_DATE)
    select Record).ToList();

  14. Dustin Says:

    Exactly what I needed. Thanks!

  15. Simon Weaver Says:

    be sure to try both ways!
    the more ‘compact’ version produces much much slower SQL (at least for my situation it did). a difference between 24 seconds for the ‘compact’ version compared with a fraction of a second for the ‘let’ version.
    don’t forget to add some indexes too for the grouping

  16. Dev Says:

    Awesome job man…

  17. Grant Says:

    Thanks Syed. Group by’s still throw me. I have a flattened list of companies with associated addresses, and your examples got me going in the right direction. Four years later and LINQ is still tough to grasp at times – except now I can’t imagine living without it.

  18. Martin H. Andersen Says:

    Do anyone know how to do this with NHibernate? If you solve it, I buy you a beer if you come to Denmark (-:


Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Follow

Get every new post delivered to your Inbox.

Join 56 other followers

%d bloggers like this: