EF Core 2.1中如何在GROUP BY子句中选择每个组的第一项？

Question

假设有一个论坛，其中包含主题列表和每个主题中的帖子列表。我想获取每个主题的帖子列表以及最新帖子（按日期）的标题。

是否可以使用EF Core（2.1）实现此目标？在SQL中可以这样做：

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

在EFCore中，我可以选择最后的日期（LastDates）

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

如果我运行 .ToList()，查询将被正确地转换为 GROUP BY。 但是我无法继续执行。 接下来的操作将在内存中执行，而不是在SQL中（导致 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)
            })

尝试连接导致NotSupportedException(无法解析表达式)：

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

我知道可以使用SELECT N+1（每个主题运行一个单独的查询）来实现，但我想避免这样做。

- Shaddix

---

找到了这个网址http://tsherlock.tech/2018/03/20/joining-in-memory-list-to-entity-framework-query/，可能会有所帮助，但如果有纯EF的解决方案，那就更好了。 - Shaddix

Answer 1

我不知道从EFCore的哪个版本开始，但现在有一个更简单的单查询替代方案:

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

- Shaddix

Answer 2

我现在基本上在运行之后做的事情是

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

我构建了以下查询：

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

这将导致只有一个查询（而不是N个），例如 SELECT * FROM Posts WHERE (Posts.TopicId == 1 AND Posts.CreatedDate = '2017-08-01') OR (Posts.TopicId == 2 AND Posts.CreatedDate = '2017-08-02') OR ...。

虽然效率不是特别高，但由于每页的主题数量相当低，所以这样就足够了。

- Shaddix

---

“Not extremely efficient”…更像是极其低效。 - Mat Jones

Answer 3

在 EF Core 2.1 中，GroupBy LINQ 操作符只支持将最常见的情况转换为 SQL GROUP BY 子句。聚合函数比如 sum，max ...

linq-groupby-translation。

在 EF Core 支持完整的分组操作之前，您可以使用 Dapper。

- Mohammad Akbari

---

在尝试迁移到内置的 GroupBy 之前，我实际上是在使用 https://github.com/ethanli83/EFSqlTranslator（自 EFCore 1.1 起）。 - Shaddix

Answer 4

我不确定EFCore的版本，但你可以尝试以下代码：它会先按组分组，然后选择最大的ID，并返回每个组中的最大ID记录。

var firstProducts = Context.Posts
.GroupBy(p => p.TopicId)
.Select(g => g.OrderByDescending(p => p.id).FirstOrDefault())
.ToList();

- Neeraj Singh Chouhan