CODEWITHSUNDEEP
c#linqentity-framework
Todilo
Todilo

Reputation: 1333

Entity Framework select one of each group by date

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.

Upvotes: 41

Views: 48589

Answers (4)

NinjaNye
NinjaNye

Reputation: 7126

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

Upvotes: 1

Khanh TO
Khanh TO

Reputation: 48972

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()
                   )

Upvotes: 80

Alex
Alex

Reputation: 8937

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
}

Upvotes: 2

maxlego
maxlego

Reputation: 4914

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);

Upvotes: 2

Related Questions