Max count on group by - Entity Framework

Question

Hi,

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 ? Kindly help :-|

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; }
}

Any help would be greatly appreciated as I'm sitting stuck on this right now and it's very urgent :-|

Thanks in advance.

Answer 1

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.