LINQ query for Select top category

Question

I am writing a query in SQL to select the 12 most visited categories by clickCount column

SELECT top(12) c.Name
FROM Category c
GROUP BY c.Name
ORDER BY SUM(ClickCount) DESC

I need to implement in linq. while I write this, but the result is not as expected

var query = _categoryRepository.Queryable()
                .GroupBy(r => r.Name)
                .SelectMany(g => g.OrderBy(r => r.ClickCount))
                .Take(12).ToList();

Answer 1

Try the following:

var query = (from item in _categoryRepository.Queryable()
            group item by item.Name into sub
            orderby sub.Sum(x => x.ClickCount) descending
            select sub).Take(12).ToList();