LINQ: Grouping SubGroup

Question

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

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:

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 1

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