EF Core 2.1 GROUP BY and select first item in each group

In EF Core 2.1 GroupBy LINQ operator only support translating to the SQL GROUP BY clause in most common cases. Aggregation function like sum, max ...

linq-groupby-translation

You can until full support group by in EF Core use Dapper

I don't know since which version of EFCore it's possible, but there's a simpler single-query alternative now:

context.Topic
   .SelectMany(topic => topic.Posts.OrderByDescending(z => z.CreatedDate).Take(1),
        (topic, post) => new {topic.Id, topic.Title, post.Text, post.CreatedDate})
   .OrderByDescending(x => x.CreatedDate)
   .ToList();

Basically what I'm doing now is after running

var topics = _context.Posts.GroupBy(x => x.TopicId, (x, y) => new
            {
                CreatedDate = y.Max(z => z.CreatedDate),
                TopicId = x,
            }).ToList();

I build the following query:

Expression<Func<Post, bool>> lastPostsQuery = post => false;
foreach (var topic in topics) 
{
    lastPostsQuery = lastPostsQuery.Or(post => post.TopicId == topic.TopicId && post.CreatedDate = topic.CreatedDate); //.Or is implemented in PredicateBuilder
}
var lastPosts = _context.Posts.Where(lastPostsQuery).ToList();

Which results in one query (instead of N) like SELECT * FROM Posts WHERE (Posts.TopicId == 1 AND Posts.CreatedDate = '2017-08-01') OR (Posts.TopicId == 2 AND Posts.CreatedDate = '2017-08-02') OR ....

Not extremely efficient but since the number of topics per page is quite low it does the trick.