CODEWITHSUNDEEP
c#linqentity-frameworklinq-to-sql
Mark
Mark

Reputation: 7818

Linq query to sum values from tables

I have a LINQ query, which takes an invoice, looks up the quantity and unit price of each item attached, and in another linked table, looks up any payments made against the invoice.

Currently this is:

from i in Invoices
select new
{
i.InvoiceId, i.CustomerName,
Items =
    from it in InvoiceItems
    where i.InvoiceId == it.InvoiceId 
    select new { 
    it.Item, 
    it.Quantity, 
    it.UnitPrice,
    SubPrice=it.Quantity*it.UnitPrice
    },
Payments = 
    from pi in PaymentInvoices
    where i.InvoiceId == pi.InvoiceId 
    select new {
    SumPayments=pi.AmountAllocated
    }
}

This shows the following results:

screenshot of results

How do I change this LINQ query to show just the sum of SubPrice and SumPayments (ie:

Invoice 11: SubPrice= 90.00 SumPayments= 24.00

Invoice 12: SubPrice=175.00, SumPayments=175.00

Thank you for any help,

Mark

UPDATE SHOWING WORKING ANSWER FOLLOWING ANSWER FROM KENNETH

from i in Invoices
select new
{
    InvoiceID = i.InvoiceId, 
    CustomerName = i.CustomerName,
    SubPrice = InvoiceItems.Where(it => it.InvoiceId == i.InvoiceId).Select(it => it.Quantity*it.UnitPrice).Sum(),
    SumPayments = PaymentInvoices.Where(pi => pi.InvoiceId == i.InvoiceId).Select(pi => pi.AmountAllocated).Sum()
}

Upvotes: 3

Views: 9624

Answers (1)

Kenneth
Kenneth

Reputation: 28737

You could use the following method:

from i in Invoices
select new
{
    InvoiceID = i.InvoiceId, 
    CustomerName = i.CustomerName,
    SubPrice = InvoiceItems.Where(it => it.InvoiceID = i.InvoiceID).Select(it => it.Quantity*it.UnitPrice).Sum(),
    SumPayments = PaymentInvoice.Where(pi => pi.InvoiceId = i.InvoiceId).Select(pi => pi.AmountAllocated).Sum()
}

Upvotes: 4

Related Questions