CODEWITHSUNDEEP
c#entity-frameworkef-code-first
AliRam
AliRam

Reputation: 80

How do I display the Sum records of a child property in a field in the parent model?

How do I display the Sum records of a child property in a field in the parent model? for example Order Model:

  public class Order
  {
    [Key]
    public int Id { get; set; }
    public string Title { get; set; }
    public DateTime Date { get; set; }
    public long SumPrice { get; set; }

    public ICollection<OrderDetails> OrderDetails{ get; set; }
  }

OrderDetail Model:

public class InvoicesDetails
{
    [Key]
    public int Id { get; set; }
    public string Title { get; set; }
    public long Quantity { get; set; }
    public long Price { get; set; }
  
    public int OrderId { get; set; }
    [ForeignKey(nameof(Order Id))]
    public virtual Order Order { get; set; }
}

How do I display the Sum records of Price property in a field SumPrice in the Order model? I want to get the sum in this model and I don't want to calculate it while saving and write it to the parent model.

Upvotes: 0

Views: 62

Answers (1)

Steve Py
Steve Py

Reputation: 34653

As mentioned you can use an unmapped helper:

public class Order
{
    [Key]
    public int Id { get; set; }
    public string Title { get; set; }
    public DateTime Date { get; set; }
    public long SumPrice { get; set; }
    
    [NotMapped]
    public int OrderDetailCount => OrderDetails.Count;

    public ICollection<OrderDetails> OrderDetails{ get; } = [];
}

*note: Always initialize collection navigation properties and protect the setter. Code consuming an entity should never be able to "set" a collection navigation property. Initializing it ensures you don't end up with a NullReferenceException, the collection is initialized and ready to go.

However, there is a limitation here in that the count will only be accurate when you eager load the OrderDetails collection. By default on a new DbContext instance if you fetch just the Order you would get a count of 0. Worst case you can get an inaccurate count if the DbContext happens to have loaded (tracking) a subset of order details that happen to be associated to this order. Tracked entities will automatically be linked to collections etc. when a related entity is loaded. You also cannot use an unmapped helper in Linq query expressions that would get translated to SQL. For instance you cannot use something like: var smallOrders = _context.Orders.Where(o => o.OrderDetailCount < 5);

Eager loading all OrderDetails to get a count can be an expensive operation, especially if you want to search and display a list of Orders.

A better solution when it comes to reading data and having access to summary details like this is to use projection when reading. For instance if we want to list a set of Orders and include their order details count:

public class OrderSummaryViewModel
{
    public int Id { get; set; }
    public string Title { get; set; }
    public DateTime Date { get; set; }
    public long SumPrice { get; set; }
    public int OrderDetailCount { get; set; }
}

Then when reading our search results:


var orders = await _context.Orders
    .Where(o => o.Date >= startDate && o.Date < endDate)
    .Select(o => new OrderSummaryViewModel 
    {
        Id = o.Id,
        Title = o.Title,
        Date = o.Date,
        SumPrice = o.SumPrice,
        OrderDetailCount = o.OrderDetails.Count
    }).ToListAsync();

This can be simplified by using a mapper like Automapper or Mapperly, etc. which can leverage EF's IQueryable to build a query expression and populate the view model as a one-liner.

Often the view models will only need a subset of data from the actual model graph. This allows EF to build more efficient queries and there is no worry about missing an eager load or the cost overhead of Cartesian Products building queries to get back everything.

Upvotes: 1

Related Questions