跨复杂父/子/父/子关系的LINQ查询

Question

这可能是一个LINQ的快照，但这超出了我的基本能力。我们通过实体框架访问了六个表：

Stores { Storeid, Description }
ShoppingDays { ShoppingDayid, Date, Storeid }
Transactions { Transactionid, Amount, ShoppingDayid, PaintColorid }
PaintColors { PaintColorid }
DyeAllocations { DyeAllocationid, PaintColorid, Percent, DyeId }
Dyes { DyeId, Name }

Stores、ShoppingDays和Transaction表很简单，不需要注释。但是，每笔交易都购买一种颜色的油漆。每一种颜色的油漆由一个混合的彩色染料百分比，加起来达到100%。

我想总结一下每家商店每天花在每种染料上的所有钱。假设store1在第一天有两笔交易，一笔是30美元购买紫色油漆(40%红色，40%蓝色，20%黑色)，另一笔是20美元粉红漆(20%红色，80%白色)。结果看起来就像

商店1，1，红色，16美元

库存1，1，蓝色，12美元

商店1，1，黑色，6美元

1仓库，白色，16美元

任何帮助都将不胜感激。我甚至不知道从哪里开始。我对所有表进行了内部连接，然后将数据放入excel枢轴表以提取数据。显然这是不对的。

我从以下几个方面开始。它提供了一个表格，显示每笔交易的每次染料采购。我想总结一下每个商店和购物日的采购情况，但我不知道怎么做。

var dyeValues = (from store in db.stores
                           join sd in db.shoppingdays on store.storeId equals sd.storeId
                           join tr in db.transactions on sd.shoppingdayId equals tr.shoppingdayId
                           join pc in db.paintcolors on tr.paintcolorId equals pc.paintcolorId
                           join da in db.dyeallocations on pc.paintcolorId equals da.paintcolorId
                           where da.percent > 0.0m
                           select new
                           {
                               store.Description,
                               shoppingdayDate = sd.Date,
                               da.dye.Name,
                               da.percent,
                               Allocation = da.percent * tr.Amount
                           });

Answer 1

这里是与传统SQL方法相当的方法。

首先是按{ShoppingDayId, DyeId}分组并计算Sum(Percent * Amount)的子查询

var dyeAllocations =
    from tr in db.Transactions
    join pc in db.PaintColors on tr.PaintColorId equals pc.PaintColorId
    join da in db.DyeAllocations on pc.PaintColorId equals da.PaintColorId
    where da.Percent > 0.0m
    group new { Allocation =  da.Percent * tr.Amount }
    by new { tr.ShoppingDayId, da.DyeId } into g
    select new { g.Key.ShoppingDayId, g.Key.DyeId, Allocation = g.Sum(e => e.Allocation) };

然后连接到其他表以获得所需的附加信息：

var dyeValues =
    from da in dyeAllocations
    join dye in db.Dyes on da.DyeId equals dye.DyeId
    join sd in db.ShoppingDays on da.ShoppingDayId equals sd.ShoppingDayId
    join store in db.Stores on sd.StoreId equals store.StoreId
    select new
    {
        store.Description,
        sd.Date,
        dye.Name,
        da.Allocation
    };

子查询可以嵌入到实际的查询中，为了可读性，我使用了一个单独的变量(它对EF生成的SQL查询没有影响)。此外，您可能需要更新字段名/大小写以匹配实际模型，但这应该会给您提供一个想法。

Answer 2

我把Ivan的答案修改成一个查询。它很慢，但很管用！

var deyValues = from cs in account.stores
       join sd in db.shoppingdays on cs.storeId equals sd.storeId
       join tr in db.transactions on sd.shoppingdayId equals tr.shoppingdayId
       join pc in db.paintcolors on tr.paintcolorId equals pc.paintcolorId
       join da in db.dyeallocations on pc.paintcolorId equals da.paintcolorId
       where da.AfterTaxOptimalPortion > 0.0m
       group new { Allocation = da.Percent * tr.Amount }
       by new { AccountName = cs.Account.Name, ShoppingDate = sd.Date, DyeName = da.dye.Name } into g
       select new { g.Key.AccountName, g.Key.ShoppingDate, g.Key.DyeName, Total = g.Sum(el => el.Allocation) };