C# + EntityFramework: Convert multiple group by query to nested JSON

Question

I made the following linq statement

C#

var list = from row in repository.GetAllEntities()
                       group row by new { row.RegionString, row.SubRegionString, row.CountryString } into g
                       select new { g.Key.RegionString, g.Key.SubRegionString, g.Key.CountryString, Count = g.Count() };

return Json(list, JsonRequestBehavior.AllowGet);

That returns

[
  {
    "RegionString":"Americas",
    "SubRegionString":"",
    "CountryString":"",
    "Count":2
  },
  {
    "RegionString":"Americas",
    "SubRegionString":"NorthAmerica",
    "CountryString":"Canada",
    "Count":5
  },
  {
    "RegionString":"Americas",
    "SubRegionString":"NorthAmerica",
    "CountryString":"US",
    "Count":3
  },
  {
    "RegionString":"Americas",
    "SubRegionString":"SouthAmerica",
    "CountryString":"Chile",
    "Count":3
  },
  {
    "RegionString":"EMEA",
    "SubRegionString":"AsiaPacific",
    "CountryString":"Australia",
    "Count":2
  },
  {
    "RegionString":"EMEA",
    "SubRegionString":"AsiaPacific",
    "CountryString":"Japan",
    "Count":1
  },
  {
    "RegionString":"EMEA",
    "SubRegionString":"SouthernEurope",
    "CountryString":"Turkey",
    "Count":1
  },
  {
    "RegionString":"EMEA",
    "SubRegionString":"WesternEurope",
    "CountryString":"",
    "Count":1
  }
]

But I am trying to make it into this format

[{
    name: "Americas",
    children: [
     {
         name: "NorthAmerica",
         children: [{ "name": "Canada", "count": 5 },
                    { "name": "US", "count": 3 }]

     },
     {
         name: "SouthAmerica",
         children: [{ "name": "Chile", "count": 1 }]
     },
    ],
},
{
    name: "EMA",
    children: [
     {
         name: "AsiaPacific",
         children: [{ "name": "Australia", "count": 2 },
                    { "name": "Japan", "count": 1 }]

     },
     {
         name: "SouthernEurope",
         children: [{ "name": "Turkey", "count": 1 }]
     },
    ],
}]

How could I modify the statement to get the result I'm looking for? Non linq answers are acceptable too.

EDIT: Region is the parent of SubRegion, SubRegion is the parent of Country and Count is the unique number of items that belongs to Country

Answer 1

Here is the linq query you want (I've removed the -String postfix for readability):

var list =
    from entity in repository.GetAllEntities()
    group entity by entity.Region into regions
    let childrenOfRegions =
        from region in regions
        group region by region.SubRegion into subregions
        let countriesOfSubRegions =
            from subregion in subregions
            group subregion by subregion.Country into countries
            select new { Name = countries.Key }
        select new { Name = subregions.Key, Children = countriesOfSubRegions }
    select new { Name = regions.Key, Children = childrenOfRegions };  

There is no Count in this query, since I don't really understand what you are counting.

What I'm doing here is grouping the rows into Regions and in the last line you can see the
select new { Name = regions.Key, ... } part where I'm returning the Regions.
To get the children, you need to group the Regions into SubRegions (the same way with Regions). You repeat this all the way down to the Countries and you're done.