Help with a Linq expression to return a list of strings based on the number of fields

Question

Let's say I have a Comments table with these columns: Id, Comment, Category, CreatedDate, CommenterId

I want to get the 5 best categories from the Comments table (depending on the amount of each category in this table). How to do it in linq to return List or IQueryable?

Answer 1

Try something like this:

var topComments = from comment in Comments
                  group comment by comment.Category into grp
                  orderby grp.Count() descending
                  select grp.Key;

var topFive = topComments.Take(5);

Answer 2

You can use something like this:

var query = comments
    .GroupBy(comment => comment.Category)
    .OrderByDescending(g => g.Count())
    .Select(g => g.Key)
    .Take(5);