Linq group by, orderbydescending, and count

Question

So I'm trying to do a linq statement to group two db tables and select the top 25 based on how many reviews each category has. So my sql statement is

SELECT TOP 25 BusinessCategories.Category, COUNT(*) as count
FROM Reviews 
JOIN BusinessCategories 
ON BusinessCategories.BusinessID=Reviews.BusinessID 
GROUP BY BusinessCategories.Category
ORDER BY count desc

Which works perfectly. So now to try to do this in my web api I'm having troubles. This is what I have:

var top = (from review in Db.Reviews
           from category in Db.BusinessCategories
           where review.BusinessID == category.BusinessID
           group review by category into reviewgroups
           select new TopBusinessCategory
           {
               BusinessCategory = reviewgroups.Key,
               Count = reviewgroups.Count()
           }
           ).OrderByDescending(x => x.Count).Distinct().Take(25);

This gives me some of the same results, but it looks like when I call the api in the browser all the counts are the same...so I'm doing something wrong.

Answer 1

Solve the problem by using this

[HttpGet]
    [Queryable()]
    public IQueryable<TopBusinessCategory> GetTopBusinessCategories()
    {
        var top = (from p in Db.BusinessCategories
                  join c in Db.Reviews on p.BusinessID equals c.BusinessID into j1
                  from j2 in j1.DefaultIfEmpty()
                  group j2 by p.Category into grouped
                  select new TopBusinessCategory
                  {
                      BusinessCategory = grouped.Key,
                      Count = grouped.Count(t => t.BusinessID != null)
                  }).OrderByDescending(x => x.Count).Take(25);

        return top.AsQueryable();
    }

Answer 2

Try this may be it works for you

var top = (from review in Db.Reviews
           join category in Db.BusinessCategories
           on review.BusinessID equals category.BusinessID
           group review by category into reviewgroups
           select new TopBusinessCategory
           {
               BusinessCategory = reviewgroups.Key,
               Count = reviewgroups.Key.categoryId.Count() //CategoryId should be any   
                                                           //property of Category or you           
                                                           //can use any property of category
           }).OrderByDescending(x => x.Count).Distinct().Take(25);