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?

score 118 · Accepted Answer · answered Feb 07 '12 at 09:27

118

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.

answered Feb 07 '12 at 09:27

AakashM

62,551
17
151
186

1

This is better than our solution :) Thanks. – L-Four Feb 07 '12 at 09:44
@AakashM Thanks. But I am fetching a problem with groupVariable.FirstOrDefault().column_name. When I am going to access the IQueryable variable Result then the error occurred which heading is **"entitycommandexecutionexception was unhandled by user code"** with body ***"An error occurred while executing the command definition. See the inner exception for details."*** I can't understand what is happening. – Muhammad Ashikuzzaman Feb 03 '16 at 06:22
@MuhammadAshikuzzaman sounds to me like you need to [Ask a new question](http://stackoverflow.com/questions/ask). Be sure to include all the details! – AakashM Feb 03 '16 at 09:16
`FirstOrDefault()` always returns null. I made it work with `pg.Key`, which actually makes more sense. I don't understand how `FirstOrDefault` would return any object, when all I'm grouping by is `someId` – SZT Nov 11 '20 at 19:46
@SZT once youve done a `group`, the thing you end up with (`pg` here) is an `IGrouping`. When you use any of the methods that do enumeration-type-things on an `IGrouping`, what you're iterating on is the members of the group. So `pg.FirstOrDefault()` here is the first (in some arbitrary order) of the `Products` that are in the particular `SomeId` group. – AakashM Nov 12 '20 at 08:42
**// join after group** that's the key, thanks – SamBerk Aug 26 '21 at 16:28

L-Four · Answer 2 · 2012-02-07T10:51:44.760

We did it like this:

from p in Products                         
join bp in BaseProducts on p.BaseProductId equals bp.Id                    
where !string.IsNullOrEmpty(p.SomeId) && p.LastPublished >= lastDate                         
group new { p, bp } by new { p.SomeId } into pg    
let firstproductgroup = pg.FirstOrDefault()
let product = firstproductgroup.p
let baseproduct = firstproductgroup.bp
let minprice = pg.Min(m => m.p.Price)
let maxprice = pg.Max(m => m.p.Price)
select new ProductPriceMinMax
{
SomeId = product.SomeId,
BaseProductName = baseproduct.Name,
CountryCode = product.CountryCode,
MinPrice = minprice, 
MaxPrice = maxprice
};

EDIT: we used the version of AakashM, because it has better performance

score 24 · Answer 3 · edited Sep 05 '16 at 06:48

I met the same problem as you.

I push two tables result into t1 object and group t1.

 from p in Products                         
  join bp in BaseProducts on p.BaseProductId equals bp.Id
  select new {
   p,
   bp
  } into t1
 group t1 by t1.p.SomeId into g
 select new ProductPriceMinMax { 
  SomeId = g.FirstOrDefault().p.SomeId, 
  CountryCode = g.FirstOrDefault().p.CountryCode, 
  MinPrice = g.Min(m => m.bp.Price), 
  MaxPrice = g.Max(m => m.bp.Price),
  BaseProductName = g.FirstOrDefault().bp.Name
};

LINQ: combining join and group by

3 Answers3

Linked