Get the first record of a group in LINQ?

Question

Summary: How to get top 1 element in ordered groups of data

I am trying to group by a CarId field , and then within each group, I want to sort on a DateTimeStamp field descending. The desired data would be for each Car give me the latest DateTimeStamp and only that 1 in the group.

I can get to this point, but having problems taking the top 1 off of the group and ordering the group by DateTimeStamp desc.

Here is what I have after the first group operation:

group 1
------------------------
CarId  DateTimeStamp 
1      1/1/2010
1      1/3/2010
1      3/4/2010

group 2
------------------------
CarId  DateTimeStamp 
2      10/1/2009
2      1/3/2010
2      9/4/2010

what I would desire only the top 1 in an ordered group

    group 1
    ------------------------
    CarId  DateTimeStamp 
    1      3/4/2010

    group 2
    ------------------------
    CarId  DateTimeStamp 
    2      9/4/2010

Brickwall: Where I get stopped, is needing the CarId and DateTimeStamp in the group by clause, in order to later sort by the DateTimeStamp. Maybe the sorting of the date should be done in a separate function, not sure.

Solution 1

data
    .GroupBy(
        x => x.CardId, 
        (x, y) => new { 
            Key = x, 
            Value = y.OrderByDescending(z => z.DateTimeStamp).FirstOrDefault() 
        }
    );

This will group all the elements by CardId, then order the elements of each group by DateTimeStamp (descending), then pare down each group to only contain the first element. Finally, it returns an enumerable of "groups" (with the scare quotes since they're actually an anonymous type instead of an IGrouping) where each group has the one item you're seeking.

Solution 2

I find the query expression syntax easier to understand myself:

from x in data 
group x by x.CarId into grp
select new {
    CarId = x.CarId,
    Record = (from x in grp orderby z.DateTimeStamp descending).FirstOrDefault()
};

Comments

[RyBolt]

That did it! I did get an exception and had to use FirstOrDefault() instead of First(). Thanks!

[Gone Coding]

+1: Perfect. Just what I needed (a version of grouping and first record I can actually follow :)

[Shaiju T]

thank you, i was searching for this, i searched sql group by last record but didn't find solution linq group by last record helped to come here, only need is ,i have to make it 'typed' for ViewModel

[Si8]

Can I concatenate two columns in the orderbydescending? Like z.Field<string>("Date") + z.Field<string>("Time")? Since they are string and separate columns, how can I achieve that? How can I select multiple column beside the CardId?

[Kirk Woll]

@Si8, you should really ask a separate question, but what you want is .OrderByDescending(...).ThenByDescending(...)

[Si8]

You are absolutely right and i did. I forgot to mention it here. Thanks. stackoverflow.com/questions/44764687/…