Lucy Lucy - 3 months ago 38
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
{


con.CityName,

}

by new
{

con.ContinentName,
con.CountryName
}

).ToList();

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
}
).ToList()

}).ToList()


}).ToList();

Answer

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 })
                                    .ToList()
                     })
                     .ToList()
    })
    .ToList();
Comments