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.