使用Linq分组

Question

我需要呈现一个按类别分组的广告列表，包括每个类别的广告计数。

类别按父类别分组，如汽车，包括类别轿车，敞篷车和运动。

模型:

public class Ad
{
    [Key]
    public int Id { get; set; }
    public Category Category { get; set; }
}
public class Category
{
    public int Id { get; set; }
    [ForeignKey("CategoryParent")]
    public int? CategoryParent_Id { get; set; }
    public string Name { get; set; }
    public virtual ICollection<Ad> Ads { get; set; }
}

结果是:

 Cars - Count: 100 (where 100 is the sum of for example 20 Saloon's Ads, 80 Cabrilet's)

目前，我只能显示所有类别的列表，而不能按父类别分组。

var adIds = {1,2,4,5}
var result = 
    from c in categoryQuery
    let searchCount = c.Ads.Count(a => adIds.Contains(a.Id))
    where searchCount > 0
    select new CategoryGetAllBySearchDto
    {
        Id = c.CategoryParent_Id,
        Name = c.CategoryParent.Name,
        SearchCount = searchCount,
        Ids = c.Ads.Where(a => adIds.Contains(a.Id)).Select(a => a.Id)
    }; 

Answer 1

GroupBy in memory:

var adIds = { 1, 2, 4, 5 };
var result = categoryQuery.Where(c => c.Ads.Any(a => adIds.Contains(a.Id)))
                        .Select(c => new 
                        { 
                            c.CategoryParent_Id, 
                            c.CategoryParent.Name,
                            Ids = c.Ads.Where(a => adIds.Contains(a.Id)).Select(a => a.Id).AsEnumerable()
                        })
                        .ToList()
                        .GroupBy(c => new {c.CategoryParent_Id, c.Name})
                        .Select(g => new CategoryGetAllBySearchDto
                         {
                             Id = g.Key.CategoryParent_Id,
                             Name = g.Key.Name,
                             Ids = g.SelectMany(u => u.Ids).AsEnumerable()
                         })
                         .ToList();

Answer 2

我想你需要这个:

var adIds = { 1, 2, 4, 5 };
var result = from c in categoryQuery
             where c.Ads.Any(a => adIds.Contains(a.Id))
             group c by new {c.CategoryParent_Id, c.CategoryParent.Name} into g
             select new CategoryGetAllBySearchDto
             {
                 Id = g.Key.CategoryParent_Id,
                 Name = g.Key.Name,
                 SearchCount = g.SelectMany(u => u.Ads)
                                .Where(a => adIds.Contains(a.Id))
                                .Count(),
                 Ids = g.SelectMany(u => u.Ads)
                        .Where(a => adIds.Contains(a.Id))
                        .Select(a => a.Id)
             };

Answer 3

您可以取出SearchCount并将AsEnumerable添加到Ids以获得一次查询

    public class CategoryGetAllBySearchDto
        {
            public int? Id { get; set; }
            public string Name { get; set; }
            public int SearchCount { get {  return this.Ids.Count() } }
            public IEnumerable<int> Ids { get; set; }
        }

和查询:

var adIds = { 1, 2, 4, 5 };
        var result = from c in categoryQuery
                     where c.Ads.Any(a => adIds.Contains(a.Id))
                     group c by new {c.CategoryParent_Id, c.CategoryParent.Name} into g
                     select new CategoryGetAllBySearchDto
                     {
                         Id = g.Key.CategoryParent_Id,
                         Name = g.Key.Name,
                         Ids = g.SelectMany(u => u.Ads)
                                .Where(a => adIds.Contains(a.Id))
                                .Select(a => a.Id)
                                .AsEnumerable()
                     };