LINQ: Grouping SubGroup
<p>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</p>

<p><a href="https://i.stack.imgur.com/bNA9C.jpg" rel="noreferrer"><img src="https://i.stack.imgur.com/bNA9C.jpg" alt="enter image description here"></a></p>

<p>Here is the t-sql:</p>

<pre><code>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
</code></pre>

<p>and the result of t-sql:</p>

<p><a href="https://i.stack.imgur.com/qsaPp.jpg" rel="noreferrer"><img src="https://i.stack.imgur.com/qsaPp.jpg" alt="enter image description here"></a></p>

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

<pre><code>  var Result = MyRepository.GetList&lt;GetAllCountriesAndCities&gt;("EXEC sp_GetAllCountriesAndCities");

    List&lt;Continent&gt; List = new List&lt;Continent&gt;();


    var GroupedCountries = (from con in Result
                             group new
                             {


                                 con.CityName,

                             }

                             by new
                             {

                                 con.ContinentName,
                                 con.CountryName
                             }

            ).ToList();

    List&lt;Continent&gt; List = GroupedCountries.Select(c =&gt; new Continent()
    {

        ContinentName = c.Key.ContinentName,
        Countries = c.Select(w =&gt; new Country()
        {
            CountryName = c.Key.CountryName,

            Cities = c.Select(ww =&gt; new City()
            {
                CityName = ww.CityName
            }
            ).ToList()

        }).ToList()


    }).ToList();
</code></pre>