Entity Framework select one of each group by date

Question

I have a table like this (Table name: Posts):

+----+--------------------------+-------+------------+
| id |         content          | type  |    date    |
+----+--------------------------+-------+------------+
|  0 | Some text                | TypeA | 2013-04-01 |
|  1 | Some older text          | TypeA | 2012-03-01 |
|  2 | Some even older texttext | TypeA | 2011-01-01 |
|  3 | A dog                    | TypeB | 2013-04-01 |
|  4 | And older dog            | TypeB | 2012-03-01 |
|  5 | An even older dog        | TypeB | 2011-01-01 |
+----+--------------------------+-------+------------+

Using a LINQ expression I want to find the newest content of each type, so the result should be

Some text | TypeA 
A dog     | TypeB

I have tried a few things but no point in pointing out non-working expressions.

score 77 · Accepted Answer · answered Apr 29 '13 at 09:13

77

If you want to get the whole Posts. You can try this:

var query = Posts.GroupBy(p => p.Type)
                  .Select(g => g.OrderByDescending(p => p.Date)
                                .FirstOrDefault()
                   )

answered Apr 29 '13 at 09:13

Khanh TO

48,509
13
99
115

4

Does the group by run before running the query or after? So will it pull every row from the db then group them or will it add a group by statement to the query so as to pull only the grouped rows? – Tyler Dec 27 '17 at 11:21
2

@Samir: That's a really good point indeed. It seems to have different behaviors depending on the version on EF. EF 7 seems to load the data to client and does the grouping there: https://stackoverflow.com/questions/11564311/sql-to-entity-framework-count-group-by . EF core seems to have support for sql group by: https://github.com/aspnet/EntityFrameworkCore/pull/9872 . To confirm this, you could use a profiler to see the actual sql query. – Khanh TO Dec 30 '17 at 02:31
@Samir: If you're using EF Core latest version and the performance is acceptable, you could consider this solution even if the sql query does not use group by (wrap the code in a function so we may refactor later if needed), it seems people have taken this into account and later releases may support this. Otherwise, you have to write raw sql query. – Khanh TO Dec 30 '17 at 02:36
9

Just to confirm, that EF Core 2.1 doesn't do the grouping on server side.The problem is that all operators following GroupBy are run on client, including also .Take(n) operator. So GroupBy(..).Take(n) will load all records. – Liero Aug 30 '18 at 15:55

Alex · Answer 2 · 2013-04-29T08:42:02.317

2

I suppose you can group your Posts rows by type and then select first content from descending ordered by date collection of that type

from row in Posts 
group row by row.type 
into g
select new
{       
    Content  = (from row2 in g orderby row2.date descending select row2.content).FirstOrDefault(),
    Type = g.Key
}

edited Apr 29 '13 at 08:42

answered Apr 29 '13 at 07:46

Alex

8,827
3
42
58

This seems to be working(after adding a "," and changing first to FirstOrDefault() . Is there a way to get the whole Post, or do I need to construct it in the "select new"? – Todilo Apr 29 '13 at 08:12
1

@Todilo I can't test it without model so sorry for mistakes :) You can get the Post by changing the return result from row2.content to row2, but group by is nessesary anyway, because you need newest content of "EACH TYPE". You can also try to get Distinct types, and then foreach type select top 1 Post ordered by date, by I suppose this query would be more efficient – Alex Apr 29 '13 at 08:39
I see. I will go with "manually" created the model again, it seems the most efficient way to do it. Thank you very much! – Todilo Apr 29 '13 at 08:47

score 2 · Answer 3 · answered Apr 29 '13 at 07:49

2

Or using temporary result and predicate

var tmp = posts.GroupBy(x => x.type).Select(x => new {x.Key, date = x.Max(g => g.date)).ToArray();

var filter = PredicateBuilder.False<Post>();

foreach (var item in tmp)
{
    filter = filter.Or(x => x.type == item.Key && x.date == item.date);
}

var newestPosts = posts.Where(filter);

answered Apr 29 '13 at 07:49

maxlego

4,864
3
31
38

As the above example did work I haven't tested this. Haven't seen PredicateBuilder before but seems like a cool feature I should learn. Thank you. – Todilo Apr 29 '13 at 08:26

score 1 · Answer 4 · edited Jul 29 '20 at 14:25

1

From memory, something like this should do it

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

This would give you a new anonymous type, but you can always map it to a class

edited Jul 29 '20 at 14:25

sibbl

3,203
26
38

answered Apr 29 '13 at 07:59

NinjaNye

7,046
1
32
46

3

You mean GroupBy(...) – Simon_Weaver May 25 '16 at 09:07

Entity Framework select one of each group by date

4 Answers4

Linked