CODEWITHSUNDEEP
c#linqentity-framework-core
A191919
A191919

Reputation: 3442

LINQ to entites max select max item by date EF Core 3

How to select in select new statement Post that has max date? 

var result = from category in dataRepository.Categories
                 from forum in category.Forums
                 from topic in forum.Topics
                 from post in topic.Posts
                 group new { category, post, post.DateTime } by new { category.Name, ForumName = forum.Name }
                 into resultSet
                 select new
                 {
                     TopicId = resultSet.Key.Name,
                     ForumName = resultSet.Key.ForumName,
                     Replies = resultSet.Count(),
                     MaxPostDate = resultSet.Max(t => t.DateTime),
                     Post = /*How to select here Post item max by date?*/
                 };

I tried 

        var result = from category in dataRepository.Categories
                     from forum in category.Forums
                     from topic in forum.Topics
                     from post in topic.Posts
                     group new { category, post } by new { category.Name, ForumName = forum.Name }
                     into resultSet
                     select new
                     {
                         TopicId = resultSet.Key.Name,
                         ForumName = resultSet.Key.ForumName,
                         Replies = resultSet.Count(),
                         LatestPost = resultSet.Where(t => t.post.DateTime == resultSet.Max(date => date.post.DateTime)).FirstOrDefault()
                     };

Getting error 

.Max(date => date.post.DateTime))' could not be translated. Either rewrite the query in a form that can be translated, or switch to client evaluation explicitly by inserting a call to either AsEnumerable(), AsAsyncEnumerable(), ToList(), or ToListAsync(). See https://go.microsoft.com/fwlink/?linkid=2101038 for more information.

Second try

        var result = from category in dataRepository.Categories
                     from forum in category.Forums
                     from topic in forum.Topics
                     from post in topic.Posts
                     group new { category, post, post.DateTime } by new { category.Name, ForumName = forum.Name }
                     into resultSet
                     select new
                     {
                         TopicId = resultSet.Key.Name,
                         ForumName = resultSet.Key.ForumName,
                         Replies = resultSet.Count(),
                         Post = resultSet.OrderByDescending(i => i.DateTime).Select(i => i).FirstOrDefault().post
                     };

Error

.OrderByDescending(i => i.DateTime)' could not be translated. Either rewrite the query in a form that can be translated, or switch to client evaluation explicitly by inserting a call to either AsEnumerable(), AsAsyncEnumerable(), ToList(), or ToListAsync(). See https://go.microsoft.com/fwlink/?linkid=2101038 for more information.

Upvotes: 1

Views: 1386

Answers (1)

Jonathan
Jonathan

Reputation: 5018

  Post = resultSet.OrderByDescending(i=>i.DateTime).Select(i=>i).FirstOrDefault().post

See also: LINQ to SQL: GroupBy() and Max() to get the object with latest date

And a simplified Fiddle here: https://dotnetfiddle.net/yYMVNn

Upvotes: 2

Related Questions