LINQ: combining join and group by

Question

I have a query that combines a join and a group, but I have a problem. The query is like:

 var result = from p in Products                           join bp in BaseProducts on p.BaseProductId equals bp.Id                      group p by p.SomeId into pg                           select new ProductPriceMinMax {         SomeId = pg.FirstOrDefault().SomeId,         CountryCode = pg.FirstOrDefault().CountryCode,         MinPrice = pg.Min(m => m.Price),         MaxPrice = pg.Max(m => m.Price),        BaseProductName = bp.Name  <------ can't use bp.   }; 

As you see, it joins the Products table with the BaseProducts table, and groups on an id of the Product table. But in the resulting ProductPriceMinMax, I also need a property of the BaseProducts table: bp.Name, but it doesn't know bp.

Any idea what I'm doing wrong?

Answer 1

Once you've done this

group p by p.SomeId into pg   

you no longer have access to the range variables used in the initial from. That is, you can no longer talk about p or bp, you can only talk about pg.

Now, pg is a group and so contains more than one product. All the products in a given pg group have the same SomeId (since that's what you grouped by), but I don't know if that means they all have the same BaseProductId.

To get a base product name, you have to pick a particular product in the pg group (As you are doing with SomeId and CountryCode), and then join to BaseProducts.

var result = from p in Products                           group p by p.SomeId into pg                           // join *after* group  join bp in BaseProducts on pg.FirstOrDefault().BaseProductId equals bp.Id           select new ProductPriceMinMax {         SomeId = pg.FirstOrDefault().SomeId,         CountryCode = pg.FirstOrDefault().CountryCode,         MinPrice = pg.Min(m => m.Price),         MaxPrice = pg.Max(m => m.Price),        BaseProductName = bp.Name  // now there is a 'bp' in scope  }; 

That said, this looks pretty unusual and I think you should step back and consider what you are actually trying to retrieve.