LINQ getting field from another collection

Question

I have 3 tables and I'm trying to get a combined result with a sum of one field of them.

I'm working with C#, .NET, Entity Framework 7 and SQL Server.

I need to get the city's Name of each result, but I store the idCity

Brand table:

public byte IdBrand { get; set; }
public string Name { get; set; } = null!;

Bundles table:

public int IdBundle { get; set; }
public short IdCity{ get; set; }
public short IdBrand { get; set; }
public decimal? Volume { get; set; }

Cities:

public short IdCity { get; set; }
public string Name { get; set; } = null!;

I've tried this linq query and got almost the result I want but the city field is failing and I got stuck...

var volume = context.Bundles
                    .GroupBy(city => city.IdCity)
                    .Select(cad => new
                                   {
                                       CITY = context.Cities.Local.ToList().ElementAt(cad.Key)!.Name,
                                       BRAND1 = cad.Where(c => c.IdBrand == 1).Sum(c => c.Volume),              
                                       BRAND2 = cad.Where(c => c.IdBrand == 19).Sum(c => c.Volume)
                                   }).ToList();

I get this result that I expect but the CITY is not correct, I think because the cad.Key is not the same than Cities Index

I also tried:

context.Cities.ToList()
       .Where(i => context.Bundles.Any(a=> i.IdCity == a.IdCity))
       .Select(x=> x.Name)

CITY	BRAND1	BRAND2
LONDON	10.2	12
MOSCOU	11.4	1
PARIS	9.1	0.4

I guess that the cad.Key is not what I need to use to get the ElementAt Cities but how can I get the city .Name from another table in the Select? Or what is the best way to perform this query?

Answer 1

Try the following query, it should have better performance:

var query = 
    from b in context.Bundles
    group b by b.IdCity into g
    select new
    {   
        IdCity = g.Key,
        BRAND1 = g.Sum(c => c.IdBrand == 1  ? c.Volume : 0),
        BRAND2 = g.Sum(c => c.IdBrand == 19 ? c.Volume : 0)
    } into agg
    join city in context.Cities on agg.IdCity equals city.Id
    select new 
    {
        CITY = city.Name,
        BRAND1 = agg.BRAND1,
        BRAND2 = agg.BRAND2
    };