Nested Group by LINQ

Question

我无法使用LINQ查询解决此问题。

因此，我们的表格结构如下：Id || bug_category || bug_name || bug_details || bug_priority

我想先按错误分类。对于每个bug_category，我希望依次按bug_priority进行分组。

所以基本上我想要这样的东西：

bug_category=AUDIO:：BUGS-->严重错误数=3，中等错误数=2，低错误数=7
bug_category=视频：：BUGS数量-->严重=5个，中等=1个，低=9个。

以下查询返回类别AND customer_pr优先级的所有唯一组合：

（其中RawDataList只是具有上述结构的数据列表）

        var ProceesedData = from d in RawDataList
                      group d by new { d.bug_category, d.bug_priority } into g
                      select new
                      {
                          g.Key.bug_category,
                          g.Key.bug_priority
                      };

下面的查询返回类别，然后是该类别中的记录列表：

            var ProceesedData = from d in RawDataList
                      group d by d.bug_category into g
                      select new { g.Key, records = g
                      };

但是，由于ProcessedData（返回变量）是一个未知类型，我无法继续操作。对此有什么想法吗？

Answer 1

这是实现嵌套分组的一种更简单的方法。我已经针对内存中的集合对它进行了测试，您的特定DB提供程序是否能很好地处理它可能会有所不同，或者它是否表现良好是未知的。

假设你有两处房产，并想按州和国家进行分组：

var grouped = People
  .GroupBy(l => new { l.State, l.Country})//group by two things
  .GroupBy(l=> l.Key.Country)//this will become the outer grouping

foreach(var country in grouped)
{
  foreach(var state in country)
  {
     foreach(var personInState in state)
     {
       string description = $"Name: {personInState.Name}, State: {state.StateCode}, Country: {country.CountryCode}";
       ...
     }
  }
}

Answer 2

我怀疑您想要（名称更改为更惯用）：

var query = from bug in RawListData
            group bug by new { bug.Category, bug.Priority } into grouped
            select new { 
                Category = grouped.Key.Category,
                Priority = grouped.Key.Priority,
                Count = grouped.Count()
            };

然后：

foreach (var result in query)
{
    Console.WriteLine("{0} - {1} - {2}",
                      result.Category, result.Priority, result.Count);
}

或者（但见下文）：

var query = from bug in RawListData
            group bug by new bug.Category into grouped
            select new { 
                Category = grouped.Category,
                Counts = from bug in grouped
                         group bug by grouped.Priority into g2
                         select new { Priority = g2.Key, Count = g2.Count() }
            };
foreach (var result in query)
{
    Console.WriteLine("{0}: ", result.Category);
    foreach (var subresult in result.Counts)
    {
        Console.WriteLine("  {0}: {1}", subresult.Priority, subresult.Count);
    }
}

编辑：如注释中所述，这将导致多个SQL查询。要获得类似的结果结构，但更有效，您可以使用：

var dbQuery = from bug in RawListData
              group bug by new { bug.Category, bug.Priority } into grouped
              select new { 
                  Category = grouped.Key.Category,
                  Priority = grouped.Key.Priority,
                  Count = grouped.Count()
              };
var query = dbQuery.ToLookup(result => result.Category,
                             result => new { result.Priority, result.Count };

foreach (var result in query)
{
    Console.WriteLine("{0}: ", result.Key);
    foreach (var subresult in result)
    {
        Console.WriteLine("  {0}: {1}", subresult.Priority, subresult.Count);
    }
}

Answer 3

我想你在搜索类似的东西：

    var processedData =
        rawData.GroupBy(bugs => bugs.bug_category,
            (category, elements) =>
            new
                {
                    Category = category,
                    Bugs = elements.GroupBy(bugs => bugs.bug_priority,
                                        (priority, realbugs) =>
                                        new
                                            {
                                                Priority = priority,
                                                Count = realbugs.Count()
                                            })
                });
    foreach (var data in processedData)
    {
        Console.WriteLine(data.Category);
        foreach (var element in data.Bugs)
            Console.WriteLine("  " + element.Priority + " = " + element.Count);
    }

Answer 4

你可以这样做

 var retList = (from dbc in db.Companies
                           where dbc.IsVerified && dbc.SellsPCBs && !dbc.IsDeleted && !dbc.IsSpam && dbc.IsApproved
                           select new
                           {
                               name = dbc.CompanyName,
                               compID = dbc.CompanyID,
                               state = dbc.State,
                               city = dbc.City,
                               businessType = dbc.BusinessType
                           }).GroupBy(k => k.state).ToList();

            List<dynamic> finalList = new List<dynamic>();
            foreach (var item in retList)
            {
                finalList.Add(item.GroupBy(i => i.city));
            }