Entity framework - calculate sum field from child records

Question

Entity framework - calculate sum field from child records

asp.net asp.net-mvc c# entity-framework entity-framework-core

Question

I'm using entity framework core with ASP.NET Core, code first.

In my app I have invoices, with the typical InvoiceHeader -> InvoiceLine relationship. The InvoiceLine entities have a LineAmount field, which I want to sum and display on the InvoiceHeader when displayed as a list (so I can see the invoice total when viewing the list of invoices).

I'm guessing I'll need to add a TotalAmount property to the InvoiceHeader entity, with the annotation [NotMapped]. But how to most efficiently populate it?

At the moment my InvoiceHeaderController.Index() is:

    // GET: InvoiceHeaders
    public async Task<IActionResult> Index()
    {
        ApplicationUser appUser = ConstantData.GetApplicationUser(_context, _userManager.GetUserId(User));
        var applicationDbContext = _context.InvoiceHeader.Include(i => i.Customer).Include(i => i.CustomerBranch)
            .Where(i => i.CustomerID == appUser.CustomerID);
        return View(await applicationDbContext.ToListAsync());
    }

Can anyone tell me what the most efficient way is to calculate (sum) this TotalAmount property?

Thanks.

Shaggs

View more on Stack Overflow

Shaggs · Accepted Answer · 11/21/2017 11:47:23 AM

I managed to work it out. Saneesh's suggestion was close, but not quite what I wanted.

The code I ended up using is:

// GET: InvoiceHeaders

public async Task<IActionResult> Index()
{
    ApplicationUser appUser = ConstantData.GetApplicationUser(_context, _userManager.GetUserId(User));
            var applicationDbContext = _context.InvoiceHeader.Include(i => i.Customer).Include(i => i.CustomerBranch)
                .Where(i => i.CustomerID == appUser.CustomerID)
                .Select(i => new InvoiceListViewModel
                {
                    invoiceHeader = i,
                    TotalAmount = i.InvoiceLines.Sum(t => t.LineAmount)
                });
    return View(await applicationDbContext.ToListAsync());
}

Thanks for your help Saneesh.

Entity framework - calculate sum field from child records

Question

Fastest Entity Framework Extensions

Popular Answer

Performance

Expression Evaluator

Related