对组和使用LINQ

Question

我很难从linq查询中获得分组和。我正试图在一定的日期范围内得到产品的净变化。现在，我的查询运行，并返回每个事务的总数量变化，但我无法得到和。我的查询如下：

from t in Transactions
join p in Products on t.Product.ID equals p.ID
where t.TransactionDate >= DateTime.Parse("12/01/2015") && t.TransactionDate <= DateTime.Parse("12/31/2015")
orderby p.ProductCode
select new 
{
    Product = p.ProductCode,
    Description = p.Description,
    TotalQuantityChanged = (t.TransactionType.AddRemove == "Addition" ? (t.FullQuantity + (t.PartialQuantity / p.Pieces)).ToString() : (-1 * (t.FullQuantity + (t.PartialQuantity / p.Pieces))).ToString() )
}

这就回来了

Product     Description                         TotalQuantityChanged

B107        3 ½" x 11" x 105" Kraft Faced R-11          23 
B107        3 ½" x 11" x 105" Kraft Faced R-11          -16 
X13AK       3 ½" x 11" x 105" Kraft Faced R-13          65 
X13AK       3 ½" x 11" x 105" Kraft Faced R-13          45 
X13AK       3 ½" x 11" x 105" Kraft Faced R-13          -12 
X45EX       3 ½" x 15" x 105" Kraft Faced R-15 HD       3 
X45EX       3 ½" x 15" x 105" Kraft Faced R-15 HD       36 
X45EX       3 ½" x 15" x 105" Kraft Faced R-15 HD       -7 

但我已经试过各种方式的团体和总和，没有运气。结果应该是这样的：

Product     Description                         TotalQuantityChanged

B107        3 ½" x 11" x 105" Kraft Faced R-11          7 
X13AK       3 ½" x 11" x 105" Kraft Faced R-13          98 
X45EX       3 ½" x 15" x 105" Kraft Faced R-15 HD       32

我尝试过这样分组，但是在where子句和select语句中出现了一个错误。

from t in Transactions
join p in Products on t.Product.ID equals p.ID 
group p by new { 
        p.ProductCode, 
        p.Description 
    } into g
where t.TransactionDate >= DateTime.Parse("12/01/2015") && t.TransactionDate <= DateTime.Parse("12/31/2015")
select new
{
    Product = g.Key.ProductCode,
    Description = g.Key.Description,
    TotalQuantityChanged = (t.TransactionType.AddRemove == "Addition" ? (t.FullQuantity + (t.PartialQuantity / p.Pieces)).ToString() : (-1 * (t.FullQuantity + (t.PartialQuantity / p.Pieces))).ToString() )
}

我不知道如何分组或得到这笔钱。如能提供任何协助，将不胜感激。

Answer 1

这大概就是你想要的：

from t in Transactions
join p in Products on t.Product.ID equals p.ID
where t.TransactionDate >= DateTime.Parse("12/01/2015") && t.TransactionDate <= DateTime.Parse("12/31/2015")
group t.TransactionType.AddRemove == "Addition" 
           ? t.FullQuantity + (t.PartialQuantity / p.Pieces) 
           : -1 * (t.FullQuantity + (t.PartialQuantity / p.Pieces)) 
    by new {p.ProductCode, p.Description} into g
select new 
{
    Product = g.Key.ProductCode,
    Description = g.Key.Description,
    TotalQuantityChanged = g.Sum().ToString()
}