使用Join或GroupJoin,是否有办法为父表和子表中的字段生成聚合值。给定一个Orders表和一个OrderDetails表,使用以下2个步骤,我可以从Orders表中获取一个聚合值(MAX),并从OrderDetails表中获取一个聚合值(SUM)。
步骤1:
然而,当我尝试将它们结合起来,如下所示:
步骤1:
var query = from o in orders
join d in details on o.OrderId equals d.OrderId
select new
{
order = o.OrderId,
maximum = o.UserId,
quantity = d.Quantity
};
步骤二:
var result = (from q in query
group q by q.order into g
select new
{
OrderId = g.Key,
MaxUnits = g.Max(q => q.maximum),
Available = (g.Max(q => q.maximum) - g.Sum(q => q.quantity))
});
然而,当我尝试将它们结合起来,如下所示:
var finalresult = orders
.GroupJoin( details,
o => o.OrderId,
d => d.OrderDetailId,
(o, grp) => new {
OrderId = o.OrderId,
MaxUnits = grp.Max(o => o.maximum),
Available = (grp.Max(o => o.maximum) - grp.Sum(d => d.Quantity))
});
在分组集合“grp”中,“o”值超出了范围。因此,grp.Max(o => o.maximum) 会导致错误。似乎只有子表(OrderDetail)的聚合值可用。
那么,有人知道是否可能在单个查询中从子表和父表中获取聚合吗?