LINQ: combining join and group by

by

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.

More Related Contents:

  1. Difference Between Select and SelectMany
  2. Return anonymous type results?
  3. How to do joins in LINQ on multiple fields in single join
  4. LINQ with groupby and count
  5. Unable to create a constant value of type Only primitive types or enumeration types are supported in this context
  6. LINQ – Left Join, Group By, and Count
  7. How to perform Join between multiple tables in LINQ lambda
  8. When should I dispose of a data context
  9. How to do a subquery in LINQ?
  10. The type of one of the expressions in the join clause is incorrect in Entity Framework
  11. LINQ join two DataTables
  12. LINQ to SQL and a running total on ordered results
  13. Client side GroupBy is not supported
  14. How to return anonymous type from c# method that uses LINQ to SQL [duplicate]
  15. How to write Asynchronous LINQ query?
  16. How do you perform a CROSS JOIN with LINQ to SQL?
  17. Linq order by, group by and order by each group?
  18. Operation could destabilize the runtime?
  19. How to select only the records with the highest date in LINQ
  20. Get table-data from table-name in LINQ DataContext
  21. DISTINCT() and ORDERBY issue
  22. LINQ Expression to return Property value?
  23. Group by variable integer range using Linq
  24. How to make LINQ execute a (SQL) LIKE range search
  25. Get sum of two columns in one LINQ query
  26. Select all columns after JOIN in LINQ
  27. LINQ to Entities group-by failure using .date
  28. LINQ to SQL Where Clause Optional Criteria
  29. Linq where column == (null reference) not the same as column == null
  30. Is Injection Possible through Dynamic LINQ?

Leave a Comment