C# LINQ：结合加入和分组依据

LINQ: combining join and group by

提问by L-Four

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.

如您所见，它将 Products 表与 BaseProducts 表连接起来，并根据 Product 表的 id 分组。但是在生成的 ProductPriceMinMax 中，我还需要 BaseProducts 表的一个属性：bp.Name，但它不知道 bp。

Any idea what I'm doing wrong?

知道我做错了什么吗？

Thanks!

谢谢！

采纳答案by AakashM

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 por bp, you can only talk about pg.

您不再可以访问初始from. 也就是说，你不能再谈p或bp，你只能谈pg。

Now, pgis a groupand so contains more than oneproduct. All the products in a given pggroup 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.

现在，pg是一个组，因此包含多个产品。给定pg组中的所有产品都相同SomeId（因为这是您分组的内容），但我不知道这是否意味着它们都具有相同的BaseProductId.

To get a base product name, you have to pick a particular product in the pggroup (As you are doing with SomeIdand CountryCode), and thenjoin to BaseProducts.

要获得基本产品名称，您必须选择组中的特定产品pg（正如您对SomeId和所做的那样CountryCode），然后加入到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.

也就是说，这看起来很不寻常，我认为您应该退后一步，考虑一下您实际尝试检索的内容。

回答by L-Four

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

编辑：我们使用了 AakashM 的版本，因为它具有更好的性能

回答by Milkker

I met the same problem as you.

我遇到了和你一样的问题。

I push two tablesresultinto t1object and group t1.

我将两个推tablesresult入t1object 和 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
};