C # + EntityFramework: convert multiple groups on demand into nested JSON

Question

I made the following linq statement

WITH#

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

It 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'm trying to do it in 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 can I change the expression to get the result I'm looking for? Non-linear answers are also acceptable.

EDIT: Region is the parent of SubRegion, SubRegion is the parent of the country, and Count is a unique number of elements belonging to the country.

Answer 1

Here is the linq request (I removed the -String postfix for reading):

 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, as I really don't understand what you think.

What am I doing here, grouping the lines into regions, and in the last line you can see the select new { Name = regions.Key, ... } part where I return the Regions.
To get children, you need to group the regions in SubRegions (as well as in the regions). You repeat it all the way to countries, and you're done.