c# - LINQ 与 GroupBy 求和

Question

(不确定我是否需要 GroupBy)

我的(简化的)表:

产品(产品ID、名称、代码)
发票(InvoiceID、编号、IsPaid)
Invoices_Products(InvoiceID、ProductID、数量、价格)- 多对多链接表

我需要显示按总和(数量*价格)的产品代码分组的已付发票的 Invoices_Products 列表。

我第一次用来获取可以绑定(bind)到 UI 的集合的代码:

IEnumerable<Invoices_Products> invoices_products = db.Invoices_Products
.Where(ip => ip.Invoice.IsPaid).DistinctBy(m => m.Product.Code);

然后我遍历它以将它绑定(bind)到 UI:

List<BindableInvoiceProduct> bindableInvoiceProducts = 
new List<BindableInvoiceProduct>();

foreach (var item in invoices_products)
{
    decimal salesValue = db.Invoices_Products.Where(ip => ip.Invoice.IsPaid 
    && ip.Product.Code == item.Product.Code).Sum(m => (m.Price * m.Quantity));

    bindableInvoiceProducts.Add(new BindableInvoiceProduct()
    {
        A = item.A,
        B = item.B,
        SalesValue = salesValue.ToString()
    });
}

(DistinctBy方法来自morelinq)

为什么总计不正确？

编辑:

一些数据:

产品 - ProductID = 1，名称 = 123，代码 = A
产品 - ProductID = 2，名称 = 456，代码 = A
发票 - InvoiceID = 1，编号 = INV123，IsPaid = True
Invoices_Products - InvoiceID = 1，ProductID = 1，数量 = 10，价格 = 100
Invoices_Products - InvoiceID = 1，ProductID = 2，数量 = 10，价格 = 200

预期结果:

代码 = A，销售值 = 3000

Answer 1

from invoice in invoices
where invoice.IsPaid
from xr in invoice.InvoiceProducts
group xr.Quantity * xr.Price by xr.Product.Code into g
select new {Code = g.Key, SalesValue = g.Sum()};

如果您想要每张发票，则:

from invoice in invoices
where invoice.IsPaid
from xr in invoice.InvoiceProducts
group xr.Quantity * xr.Price
  by new {Code = xr.Product.Code, Invoice = invoice }
  into g
select new {
  Code = g.Key.Code,
  Invoice = g.Key.Invoice,
  SalesValue = g.Sum()};