Nested Group by LINQ

Question

I am unable to solve this problem with the LINQ Query.

So we have the table structure as follows: Id || bug_category || bug_name || bug_details || bug_priority

I want to group by bug_category first. For each bug_category, I want to in turn group by bug__priority.

So basically I want something like :

bug_category = AUDIO :: No of BUGS --> Critical = 3, Medium = 2 and Low = 7 bugs.
bug_category = VIDEO :: No of BUGS --> Critical = 5, Medium = 1 and Low = 9 bugs.

The below query returns all unique combinations of category AND customer_priority:

(where RawDataList is simply a List of data which has the above mentioned structure )

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

The below query returns the category followed by a list of records in that category:

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

But I am unable to proceed further as ProcessedData(the return variable) is an unknown type. Any thoughts on this?

Answer 1

This is an easier way to accomplish nested groupings. I've tested it for in memory collections, whether or not your particular DB provider will handle it well might vary, or whether it performs well is unknown.

Assuming you had two properties, and wanted to group by both State and Country:

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

I suspect you want (names changed to be more idiomatic):

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

Then:

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

Alternatively (but see later):

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

EDIT: As noted in comments, this will result in multiple SQL queries. To obtain a similar result structure but more efficiently you could use:

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

I think you're searching something like that:

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