Sum LINQ with GroupBy

Question

(Not sure if I even need GroupBy)

My (simplified) tables:

Products (ProductID, name, code)
Invoices (InvoiceID, Number, IsPaid)
Invoices_Products (InvoiceID, ProductID, Quantity, Price) - many-to-many reference table

I need to show a list of Invoices_Products of paid invoices, grouped by Product Code, which is the amount (quantity * Price).

The code that I first use to get a collection that I can bind to the user interface:

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

Then I repeat this to associate it with the user interface:

 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 method exists from morelinq)

Why is this not true?

edit:

Some data:

Product - ProductID = 1, Name = 123, Code = A
Product - ProductID = 2, Name = 456, Code = A
Invoice - InvoiceID = 1, Number = INV123, IsPaid = True
Invoices_Products - InvoiceID = 1, ProductID = 1, Quantity = 10, Price = 100
Invoices_Products - InvoiceID = 1, ProductID = 2, Quantity = 10, Price = 200

Expected Result:

Code = A, SalesValue = 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()}; 

If you want to receive an invoice, then:

 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()}; 

Answer 2

Based on your description, I would write:

 var bindableInvoiceProducts = db.Invoices_Products .Where(ip => ip.Invoice.IsPaid) .GroupBy(ip => ip.Product.Code, (code, ips) => new BindableInvoiceProduct() { Code = code, SalesValue = ips.Sum(ip => (ip.Price*ip.Quantity)) }) .ToList(); 

That's what you need? What are item.A and item.B in your code?