CODEWITHSUNDEEP
entity-framework
user383698
user383698

Reputation: 85

Max count on group by - Entity Framework

I have following two tables:

1.) Articles - [ArticleID]
2.) ArticleComments - [CommentID], [ArticleID]

I want to retrieve ArticleID with maximum no. of comments e.g.

ArticleID - 2
TotalNoOfComments - 15

How do I do it in Entity Framework?

I access ArticleComments collection like following: article.ArticleComments. The following will be the object to store the result.

public class CommentStats
{
    public int ContextId { get; set; }
    public int CommentCount { get; set; }
}

Upvotes: 2

Views: 1885

Answers (1)

Yakimych
Yakimych

Reputation: 17752

var query = context.Articles.
            Select(a => new CommentStats
                                    {
                                        ContextId = a.Id,
                                        CommentCount = a.ArticleComments.Count
                                    }
                  ).OrderByDescending(cs => cs.commentCount);

You can then run FirstOrDefault for the one article with most comments, or ToList for the whole ordered list.

Upvotes: 2

Related Questions