How can l get a total sum from one table if it is linked with a lot of tables using linq?

Question

l have multiple tables with one to may relationships eg Country -> Region -> Center -> Greater ->Section. The section has a column for census. l am trying to write a linq query for my view to get the total census grouped by the Country. l also need to know in a country how many regions are there, how many centers, how many greaters and the total census. They can be separate queries there is no problem.

Answer 1

In this case, the SelectMany method in LINQ is your friend. Each country has a collection of regions, right? You can use .SelectMany to combine all of the regions from several countries into a single collection of regions. Then you need to get a collection of centers from all of the regions, and so on and so on.

Consider this code:

context.Countries.SelectMany(country => country.Regions)
    .SelectMany(region => region.Centers)
    .SelectMany(center => center.Greaters)
    .SelectMany(greater => greater.Sections)
    .GroupBy(country => country.Id)
    .Sum(section => section.Census);