使用 linq 从不同表中选择总和列

Question

这是我的 linq 代码..

( from Order o in masterRepository.Orders.Where(x => x.IsDeleted == false)
                join OrderItem oi in masterRepository.OrderItems on o.OrderID equals oi.OrderID
                group oi by new {o.StoreID, o.CreatedOn.Year, o.CreatedOn.Month, StoreNmae="Test" } into g
                        select new ST_SalesOrder
                       {
                         Year = g.Key.Year,
                         Month = g.Key.Month,
                         StoreID = g.Key.StoreID,
                         StoreName = "SA",
                         SubTotal = g.Sum(o => o.SubTotal),
                         Shipping = g.Sum(o => o.ShippingPrice),
                         Tax = g.Sum(o => o.Tax),
                         TotalAmount = g.Sum(o => o.Total),
                         TotalCount = g.Sum(oi => oi.Quantity)
                         }).OrderBy("Year desc, Month desc, StoreName").ToList();

问题是.. 我需要从 Order 表和 OrderItem 表获取总数据。 但我只能使用一张表进行分组。

如果我将 o by .... 分组，则 TotalCount = g.Sum(oi => oi.Quantity) <-- 这会出错， 如果我将 oi 分组为...，则 SubTotal = g.Sum(o => o.SubTotal), 运费 = g.Sum(o => o.ShippingPrice), Tax = g.Sum(o => o.Tax), <-- 这些会出错......

我该怎么办！ 请帮忙。

Answer 1

可以

group new { Item = oi, Order = o } by new {o.StoreID, o.CreatedOn.Year, o.CreatedOn.Month, StoreNmae="Test" } into g

然后你

g.Sum(o => o.Order.Total)

求和

g.Sum(o => o.Item.Quantity)