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

I think you have to group again within the projection of the first groups: `Result.GroupBy(x => x.ContinentName).Select(g => g.GroupBy(x => x.CountryName))`. — Good Night Nerd Pride, Aug 22 '16 at 14:23
@TimSchmelter, Yes.. i want to group like this Continent -->Country--> City — Lucy, Aug 22 '16 at 14:29
@juharr...thank you so much for your help :) . I group by linq... would it better to group by sql? if yes please write simple code to show me how can i do that... — Lucy, Aug 22 '16 at 14:31

Tim Schmelter · Accepted Answer · 2016-08-22T15:33:35.237

14

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

edited Aug 22 '16 at 15:33

answered Aug 22 '16 at 15:15

Tim Schmelter

450,073
74
686
939

1

Thank you Tim! You are always help me my good friend!! This is exactly what i was looking for. What do you think about grouping that way with Linq, Is that fine, or should i group in sql server instead of Linq? my another question is: I am a new developer what is your advice for me to become really professional developer like :) ? – Lucy Aug 22 '16 at 15:37
I spent two days looking how to group that in sql server but couldn't find any helpful article – Lucy Aug 22 '16 at 15:42
Even if you group in the database you wouldn't get your nested structure, so your current approach is fine. Another approach would be to order the result set in the database by continent, country and city. Then a simple and efficient loop would be enough to create your list. – Tim Schmelter Aug 22 '16 at 16:16
You can start helping others in stckoverflow with things you already know. On that way you learn a lot because you have to think about this issue and you probably get other, better answers that show you different approaches. Therefore add your preferred tags to a question filter and you see new questions immediately which you might be able to answer. – Tim Schmelter Aug 22 '16 at 16:18
Hi Tim :), Can you please help me to solve this problem? thank you http://stackoverflow.com/questions/39275624/entity-framework-removing-child-entity/39276333?noredirect=1#comment65887790_39276333 – Lucy Sep 01 '16 at 21:02

score 2 · Answer 2 · answered Aug 22 '16 at 14:31

2

You should apply grouping twice

var grouped = Result
    .GroupBy(x => x.CountryName)
    .GroupBy(x => x.First().ContinentName);

var final = grouped.Select(g1 => new Continent
{
    ContinentName = g1.Key,
    Countries = g1.Select(g2 => new Country
    {
        CountryName = g2.Key,
        Cities = g2.Select(x => new City { CityName = x.CityName }).ToList()
    }).ToList()
});

answered Aug 22 '16 at 14:31

Hamid Pourjam

20,441
9
58
74

It worked, thank you so much :)... But i don't understand var grouped = Result .GroupBy(x => x.CountryName) .GroupBy(x => x.First().ContinentName); Can you please tell me an easier way to group it? something like what i used in my code... thank you – Lucy Aug 22 '16 at 15:11

score 2 · Answer 3 · answered Jun 30 '19 at 15:43

I know this is old, but I wanted to mention a much easier way per microsoft that is a bit more readable. This is an example with only 2 levels though but it will most likely work for others who reach this page (like me)

 var queryNestedGroups =
        from con in continents
        group con by con.ContinentName into newGroup1
        from newGroup2 in
            (from con in newGroup1
             group con by con.CountryName)
        group newGroup2 by newGroup1.Key;

The documentation for that is at https://learn.microsoft.com/en-us/dotnet/csharp/linq/create-a-nested-group and is using a Student object as an example

I do want to mention that the method they use for printing is harder than just creating a quick print function on your continent object

LINQ: Grouping SubGroup

3 Answers3

Linked