c# - LINQ 查询以按不同类别/子类别计数

Question

我有一个表，它是根据对数据表的 LINQ 查询生成的，其中包含子类别和类别字段:

Name...........Category.........Subcategory  Kiss...........Rock.............Glam Rock  Metallica......Rock.............Hard Rock  Bon Jovi.......Rock.............Soft Rock  Slade..........Rock.............Glam Rock  Meatloaf.......Rock.............Soft Rock  Wilee..........Dance............Grime  Mgmt...........Dance............Nu Rave  Dizee..........Dance............Grime  

The LINQ query I am using to generate this table is:

var qCategory = from c in dtCategory.AsEnumerable()
            select new {
                Artist = c.Field<string>("Artist"),
                Category = c.Field<string>("Category"),
                Subcategory = c.Field<string>("Subcategory")
             };

现在我想计算每个类别/子类别对的数量。例如对于上面的例子我想返回:

Category............Subcategory.......Count  Rock................Glam Rock.........2  Rock................Soft  Rock........2   Rock................Hard Rock.........1  Dance...............Grime.............2  Dance...............Nu Rave...........1  

我怎样才能做到这一点？

Answer 1

尝试:

var counts = from artist in qCategory
             group artist by new { artist.Category, artist.Subcategory } 
                          into g
             select new { 
                           g.Key.Category,
                           g.Key.Subcategory, 
                           Count = g.Count() 
                        };

---

如果你想强制子类别始终具有相同的父类别(假设子类别被命名为“Glam Rock”等，我认为这实际上是这种情况)，请执行:

var counts = from artist in qCategory
             group artist by artist.Subcategory into g
             select new {
                           Category = g.Select(a => a.Category)
                                       .Distinct()
                                       .Single(),
                           Subcategory = g.Key,
                           Count = g.Count()
                        };

如果“Rap Rock”作为“Rap”和“Rock”的子类别出现，这将引发异常。