Linq group + Join

Question

I've read some of the other questions, but couldn't get ans answer from them.

I'm a newbie in Linq. I have 3 tables.

City (id, name)

Specialty (id, name)

Quota (id, cityId, specialtyId, qty)

So, you have quotas for a specialty in a city. I'm trying to list this grouped by city. Something like this:

City: New York

• specialty A: 15
• specialty B: 12
• specialty C: 10

City: Paris

• speacialty A: 10
• speacialty B: 8

and so on.

Can someone help me with that?

Adding Up

I wasn't really asking for ready code. I think I should be more clear.

I can join the tables, by I'm confused how I'm going to group that by cities.

So for example, this will join everything

from cota in Cotas
join cidade in Cidades on cota.CidadeId equals cidade.Id
join especialidade in EspecialidadeMedicas on cota.EspecialidadeId equals especialidade.Id
select new { Qty = cota.Quantidade, City = cidade.Nome, Specialty = especialidade.Nome}

Obs: Language is portuguese, I translated the creation of the object, but Quantidade is Quantity, EspecialidadeMedica is Medical specialty and Cidade is City.

The result of this query is:

Qty | City       | Specialty 

10    Campinas     Pediatria     

15    Campinas     Cardiologia

5     Campinas     Ortopedia     

10    Sumaré       Pediatria                                                                                                                                                                                                                                                       

15    Sumaré       Cardiologia                                                                                                                                                                                                                                                     

14    Hortolândia  Cardiologia                                                                                                                                                                                                                                                     

5     Hortolândia  Ortopedia   

I want to group that by the city, and be able to return an object which have city name and a List with Specialty and Qty.

Answer 1

So, I've got what I needed by doing more research.

The code to achieve what I was looking for is:

from cidade in Cidades
join lista in 
        (
            from cota in Cotas 
             join especialidade in EspecialidadeMedicas on cota.EspecialidadeId equals especialidade.Id
            select new {
                CidadeId = cota.CidadeId, CotaId=cota.Id, Quantidade=cota.Quantidade,
                EspecialidadeId=especialidade.Id, Especialidade=especialidade.Nome
            }
        ) on cidade.Id equals lista.CidadeId into listaCota
select new {CityId=cidade.Id, cityName=cidade.Nome, quotaList=listaCota}

That way I have the following: