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?

AaronLS · Answer 1 · 2017-08-31T15:33:39.650

59

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

     }
  }
}

edited Aug 31 '17 at 15:33

answered Feb 04 '14 at 21:21

AaronLS

37,329
20
143
202

4

This is a difficult answer to find! – Paul Totzke Dec 15 '15 at 17:41
1

Yeh, it took me awhile to figure this out when I've needed this for myself, and I actually come back to my own answer when I need to do this. – AaronLS Nov 28 '16 at 00:24

Jon Skeet · Accepted Answer · 2012-03-06T07:10:46.360

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

The second query will create for each group a secondary query, so I think a better solution is the first one followed by ToLookup by category — Adrian Iftode, Mar 06 '12 at 06:34
@AdrianIftode: Have you tried it? I haven't, but I would have *expected* the appropriate SQL to be generated to do it all in one go. — Jon Skeet, Mar 06 '12 at 06:58
yes, with LinqPad, one query for the categories groups and other X queries for priorities groups — Adrian Iftode, Mar 06 '12 at 07:02
@AdrianIftode: Right - will edit that in then. I wonder if the same thing happens in EF... — Jon Skeet, Mar 06 '12 at 07:08
no, it does an outer apply between main groups and secondary groups (uses the main group key in the outer apply join), so +1 for EF in this case (as in many others) — Adrian Iftode, Mar 06 '12 at 07:16

score 6 · Answer 3 · answered Mar 06 '12 at 06:38

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

@Nandu: You realize this is basically the same as my second query, just in lambda expression form, right? Note that it suffers the same poor performance (as it's the same query) that Adrian pointed out. — Jon Skeet, Mar 06 '12 at 19:50
The perils of taking 9 minutes more than Jon Skeet to write your answer ;) — Niall Connaughton, Apr 15 '15 at 01:23

score 0 · Answer 4 · answered Apr 30 '18 at 06:47

You can do it like this

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

Nested Group by LINQ

4 Answers4

Linked

Related