CODEWITHSUNDEEP
c#sqllinqgroup-by
Nandu
Nandu

Reputation: 395

Nested Group by LINQ

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?

Upvotes: 36

Views: 42678

Answers (4)

bhavsar japan
bhavsar japan

Reputation: 125

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

Upvotes: 0

AaronLS
AaronLS

Reputation: 38365

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

     }
  }
}

Upvotes: 64

Jon Skeet
Jon Skeet

Reputation: 1500675

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

Upvotes: 31

BitKFu
BitKFu

Reputation: 3697

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

Upvotes: 6

Related Questions