EF Core 2.1 GROUP BY and select first item in each group
Solution 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();
Solution 2
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.
Solution 3
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 ...
You can until full support group by in EF Core use Dapper
Shaddix
Updated on July 09, 2022Comments
-
Shaddix almost 2 years
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 inSELECT * 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.
-
Shaddix over 5 yearsI actually was using github.com/ethanli83/EFSqlTranslator (since EFCore 1.1) before attempting to migrate to built-in GroupBy.
-
Mat Jones over 3 years"Not extremely efficient"... More like extremely inefficient