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

Question

Let's imaging a forum having a list of topics and posts in them. I want to get the list of topics and a title of last post (by date) for each topic.

Is there a way to achieve this using EF Core (2.1)? In SQL it could be done like

SELECT Posts.Title, Posts.CreatedDate, Posts.TopicId FROM 
  (SELECT Max(CreatedDate), TopicId FROM Posts GROUP BY TopicId) lastPosts
JOIN Posts ON Posts.CreatedDate = lastPosts.CreatedDate AND Posts.TopicId = lastPosts.TopicId

In EFCore I can select LastDates

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

And if I run .ToList() the query is correctly translated to GROUP BY. But I can't go further. The following is executed in memory, not in SQL (resulting in SELECT * FROM Posts):

            .GroupBy(...)
            .Select(x => new
            {
                x.TopicId,
                Post = x.Posts.Where(z => z.CreatedDate == x.CreatedDate)
                //Post = x.Posts.FirstOrDefault(z => z.CreatedDate == x.CreatedDate)
            })

Attempting to JOIN gives NotSupportedException (Could not parse expression):

.GroupBy(...)
.Join(_context.Posts,
                    (x, y) => x.TopicId == y.TopicId && x.CreatedDate == y.CreatedDate,
                    (x, post) => new
                    {
                        post.Title,
                        post.CreatedDate,
                    })

I know I can do it using SELECT N+1 (running a separate query per topic), but I'd like to avoid that.

Answer 1

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

Answer 2

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