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)

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?

CodePudding user response：

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
    };