Nested Queries in Linq

Question

I have my table structure like this.

ContinentTable => CountryTable => UserTable

User has CountryId. Country has ContinentId.

I want to query all the continents with the number of users in each.

I achieved this via SQL. But I want to use linq extension method since Im working with EF Core.

Here is my sql query

SELECT Continent.Id, Continent.Name, COUNT(Usr.UserName) AS 'User Count'
FROM Continents Continent, Countries Country, Users Usr 
WHERE Continent.Id = Country.ContinentId
AND Country.Id = Usr.CountryId
GROUP BY Continent.Id, Continent.Name

Possible duplicate of [LINQ with groupby and count](https://stackoverflow.com/questions/7285714/linq-with-groupby-and-count) — NetMage, Mar 16 '18 at 00:04

score 0 · Answer 1 · answered Mar 15 '18 at 13:58

If this is your object model:

Dim continents As Dictionary(Of String, Dictionary(Of String, List(Of String)))

    continents = New Dictionary(Of String, Dictionary(Of String, List(Of String))) From
        {
            {"Europe", New Dictionary(Of String, List(Of String)) From
                {
                    {"France", New List(Of String) From {"Antoine", "Serge"}},
                    {"Germany", New List(Of String) From {"Hans", "Wolfgang"}},
                    {"Spain", New List(Of String) From {"Pablo", "Pedro"}}
                }
            },
            {"America", New Dictionary(Of String, List(Of String)) From
                {
                    {"USA", New List(Of String) From {"Kevin", "Caleb"}},
                    {"Mexico", New List(Of String) From {"Gustavo", "Oscar"}},
                    {"Brazil", New List(Of String) From {"Alexandro"}}
                }
            }
        }

Then this would be your LINQ query:

Dim result As IEnumerable(Of KeyValuePair(Of String, Integer)) = continents.Select(Of KeyValuePair(Of String, Integer))(Function(x As KeyValuePair(Of String, Dictionary(Of String, List(Of String)))) New KeyValuePair(Of String, Integer)(x.Key, x.Value.Select(Of Integer)(Function(y As KeyValuePair(Of String, List(Of String))) y.Value.Count).Sum))

Nested Queries in Linq

1 Answers1