none
Running Total from 2 tables using LINQ RRS feed

  • Question

  • I have an invoice table and a payments table that are tied together by invoiceid.

    I have a report that lists the invoices:

     var db = CreateDBContext(connection);
                    {
                        var q = (from inv in db.Invoices
                                  where inv.ProcessingPeriod == period
                                       &&
                                       (employerGroupId == Guid.Empty ||
                                        inv.EmployerGroupId == employerGroupId)
                                       &&
                                       (employerId == Guid.Empty ||
                                        inv.EmployerGroup.EmployerId == employerId)
                                       &&
                                       inv.EmployerGroup.Employer.ParentCompanyId == CurrentParentCompanyId
                                 select new
                                 {
                                     GroupNumber = inv.EmployerGroup.GroupNumber,
                                     GroupName = inv.EmployerGroup.Name,
                                     Month =  inv.ProcessingPeriod.Value,
                                     InvoiceAmount = inv.TotalAmount,
                                     InvoiceNumber = inv.InvoiceNumber
                                     
                                 });
                        return q.OrderBy(g => g.GroupName).ThenBy(g => g.Month).ToList();                  
                    }

    Now, I want to add the total payments made on the invoices where the invoiceid on the payments table = the invoiceid on the invoice table.   It is possible to have multiple payments per invoice.

    So, I want the new results to be:

    select new
                                 {
                                     GroupNumber = inv.EmployerGroup.GroupNumber,
                                     GroupName = inv.EmployerGroup.Name,
                                     Month =  inv.ProcessingPeriod.Value,
                                     InvoiceAmount = inv.TotalAmount,
                                     InvoiceNumber = inv.InvoiceNumber

                                     TotalPayments = ?????
                                 });

    Thanks


    • Edited by cmihlfried Monday, April 27, 2015 7:54 PM
    Monday, April 27, 2015 7:48 PM

Answers

  • I found the solution... or at least 'a' solution:

    AmountReceived = db.Payments.Where(pi => pi.InvoiceId == inv.InvoiceId).Select(pi => (decimal?)pi.Amount).Sum() ?? 0

    • Marked as answer by cmihlfried Tuesday, April 28, 2015 8:54 PM
    Tuesday, April 28, 2015 8:54 PM