Lucy Lucy - 9 months ago 88
C# Question

LINQ: Grouping SubGroup

How to group SubGroup to create list of Continents where each Continent has it own counties and each country has its own cities like this table

enter image description here

Here is the t-sql:

select Continent.ContinentName, Country.CountryName, City.CityName
from Continent
left join Country
on Continent.ContinentId = Country.ContinentId

left join City
on Country.CountryId = City.CountryId

and the result of t-sql:

enter image description here

I tried this but it groups the data in wrong way i need to group exactly like the above table

var Result = MyRepository.GetList<GetAllCountriesAndCities>("EXEC sp_GetAllCountriesAndCities");

List<Continent> List = new List<Continent>();

var GroupedCountries = (from con in Result
group new



by new



List<Continent> List = GroupedCountries.Select(c => new Continent()

ContinentName = c.Key.ContinentName,
Countries = c.Select(w => new Country()
CountryName = c.Key.CountryName,

Cities = c.Select(ww => new City()
CityName = ww.CityName




You need to group everything by continent, these by country and the countries by city:

List<Continent> List = MyRepository.GetList<GetAllCountriesAndCities>("EXEC sp_GetAllCountriesAndCities")
    .GroupBy(x => x.ContinentName)
    .Select(g => new Continent 
        ContinentName = g.Key,
        Countries = g.GroupBy(x => x.CountryName)
                     .Select(cg => new Country 
                         CountryName = cg.Key,
                         Cities = cg.GroupBy(x => x.CityName)
                                    .Select(cityG => new City { CityName = cityG.Key })