CODEWITHSUNDEEP
c#.netentity-framework
Witos
Witos

Reputation: 339

Entity Framework group by and get max

Lets say I've table with the structure as below:

MyRow:

Id Name Date
1  A    2015/01/01
2  B    2015/01/01
3  C    2015/01/02
4  A    2015/01/03
5  B    2015/01/03
6  A    2015/01/02
7  C    2015/01/01

Using EF I would like to get list of MyRow which would contain elements with distinct names and newest date so in this case it would be:

4 A 2015/01/03
5 B 2015/01/03
3 C 2015/01/02

I started with something like this:

var myRows = context.MyRows.GroupBy(mr => mr.Name).Select(..now wth with max..)

Upvotes: 5

Views: 5319

Answers (2)

BSG
BSG

Reputation: 1

var data = context.MyRows.Group(p => p.Name)
                    .Select(g => new { 
                                      Type = g.Key, 
                                      Date = g.OrderByDescending(p => p.Date)
                                              .FirstOrDefault()
                                     }

Upvotes: 0

Jon Hanna
Jon Hanna

Reputation: 113272

Order each group and take the last of each.

Or since EF doesn't (last time I checked) support Last(), order each group in reverse and take the first:

var myRows = context.MyRows
  .GroupBy(mr => mr.Name)
  .Select(grp => grp.OrderByDescending(mr => mr.Date).First());

Upvotes: 6

Related Questions