Linq 分组依据、降序排序和计数

Question

所以我正在尝试执行一个 linq 语句来对两个数据库表进行分组，并根据每个类别的评论数量选择前 25 个。 所以我的sql语句是

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

这很完美。 所以现在尝试在我的 web api 中执行此操作，我遇到了麻烦。 这是我所拥有的：

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

这给了我一些相同的结果，但看起来当我在浏览器中调用 api 时，所有计数都是一样的......所以我做错了什么。

Answer 1

试试这个可能适合你

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

Answer 2

使用这个来解决问题

[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();
    }