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.

1
0
11/20/2017 6:49:55 AM

Fastest Entity Framework Extensions

Bulk Insert
Bulk Delete
Bulk Update
Bulk Merge

Popular Answer

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.

0
11/21/2017 11:47:23 AM

Related Questions

Entity Framework 6: Adding child object to parent's list vs. setting child's navigation property to parent

I have an existing database with two tables ...MailServers... and ...MailDomains... in it. ...MailDomains... has the foreign key column ...MailServerId... pointing to the ...Id... primary key column in ...MailServers.... So we have a one-to-many-relationship here....I followed ...this article... and created my Entity Framework POCOs via the "Code f...
asp.net c# entity-framework entity-framework-6
asked by Joerg

How does Entity framework implement deleting parent and a child record on the ame SaveChanges()

I have the following two entity sets representing Dept & Emp:-...public partial class Dept { public Dept() { this.Emps = new HashSet<Emp>(); } public int DeptID { get; set; } public string Name { get; set; } public string Description { get; set; } public virtual ICollection<Emp> Emps { get; set; } } public pa...
dbset edmx entity-framework entity-framework-5 entity-framework-6
asked by john Gu

How to only load certain fields of a child object in Entity Framework 6.1?

To achieve what you need you don't have other choice than project your query to an anonymous type or a DTO. As you can see, in the ...Include... extension method you can just specify the related entities you want to load which is translated in an inner join with a table (or several joins, see the ...Remarks... section in the quoted link), but that ...
c# entity-framework entity-framework-6 linq-to-entities
asked by octavioccl

Deleting parent-child relation with Entity Framework or Entity Framework Core

I'm learning how to use Entity Framework, having a NHibernate background. There are plenty of tutorials on the net, but I didn't find something for my specific case. I don't want to delete the Category record, I only want to delete the relation!...I have following poco's:... public class TrainingCourse { [Key] public int Cour...
c# entity-framework entity-framework-core
asked by evb

How to add a parent record with its children records in EF Core

Example 1 below used to work but it is not working in EF Core. ...Question...: How to make example 2 below work?...Example 1...:...child c1 = new child(); child c2 = new child(); parent p=new parent(); p.child.Add(c1); p.child.Add(c2); using (var db = new DbContext()) { db.parent.Add(p); db.SaveChanges(); } ...Entity Parent P has children C...
asp.net-core entity-framework-core
asked by nam

Entity Framework Core - Can't Add Child records

I was finally given permission to add a key to the child table. That made the code simple and I could use the scaffold command....The classes:...public partial class NewBusinessMaster { public NewBusinessMaster() { NewBusinessDetail = new HashSet<NewBusinessDetail>(); } public int NbTransactionId { get; set; } public st...
asp.net-core c# entity-framework-core
asked by WorkJ

How to automatically set CreatedOn fields with Entity Framework on saved entity and its child properties

Whenever I call ...databaseContext.SaveChanges()..., before data is saved by Entity Framework, I need all objects and their child classes to have the ...CreatedOn... field populated with ...DateTime.Now().......Details: I have a ...BaseEntity... class that all entities inherit from:... public class BaseEntity { [Key] public virtual...
c# entity-framework entity-framework-6 inheritance
asked by InspiredBy

Entity Framework Core Parent Child items in the same model, children node returns null

I have a parent and child property inside one domain model....public class TestModel { public int ParentID { get; set; } public int ID{ get; set; } public string Name{ get; set; } public virtual TestModel parent { get; set; } public virtual List<TestModel> children { get; set; } } ...When I write ...OnModelCreating... event as b...
asp.net c# entity-framework entity-framework-core
asked by coding4ever

Entity Framework: Unable to call .OrderBy on calculated field IQueryable

I am currently struggling to figure out how I can order a query by a field that only has a getter. I have an ...IQueryable... being built as follows:...var referrals = db.Referrals .Include(x => x.Doctor) .Include(x => x.OfficeLocation) .Where(x => (vm.OfficeLocationSelection =...
asp.net asp.net-mvc c# entity-framework entity-framework-6
asked by GregH

Entity Framework get SUM from child property

I have the following model where I'd like to get the sum of all OrderTotalItems for all Orders of a Customer where the OrderTotalType (Enumeration) is "total" or 99:...public class Customer { ... public ICollection<Order> Orders { get; set; } = new Collection<Order>(); } public class Order { ... public ICollection<OrderTotalItem> O...
c# entity-framework entity-framework-core
asked by Igotcha

Fastest Entity Framework Extensions

Bulk Insert
Bulk Delete
Bulk Update
Bulk Merge
View more on Stack Overflow

Prime Library

Performance

Expression Evaluator

More Projects...

Related

Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow
Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow