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.

Well, what you have tried? How much LINQ do you know? Is this LINQ to SQL, Entity Framework, ...? — Jon Skeet, Jan 27 '13 at 16:16
Check it out http://code.msdn.microsoft.com/101-LINQ-Samples-3fb9811b try something and post your code if you don't succeed — Androiderson, Jan 27 '13 at 16:28
Do you know how to right the SQL for this query and just not how to convert it to LINQ? — Servy, Jan 27 '13 at 16:41
Is entity framework. The example is using Linq to SQL because I'm using LinqPad for tests. But in the application itself I'll be using Entity Framework. — lcguida, Jan 27 '13 at 17:12

score 0 · Accepted Answer · answered Jan 27 '13 at 17:34

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:

Result from LinqPad 4

Linq group + Join

1 Answers1