c# - Calculated property for model EF Core - Property or Method?

Question

I am new to EF core. I have a Customer model with the usual properties (Name,Address,Email).

I need a property to calculate the current balance for the customer.

This will be quite an intensive computation (once many records are stored) so am I correct in thinking that it should be stored in a Method, rather than a calculated property?

I am assuming I need to add a method such as .GetCurrentBalance().

Where would I put this method?

Simplified code below:

My Customer Model

public class Customer
{
    public int CustomerId { get; set; }
    public string Name { get; set; }
    public string EmailAddress { get; set; }

    public virtual IEnumerable<SalesInvoice> SalesInvoices{ get; set; }
}

My Sales Invoice Model

public class SalesInvoice
{
    public int SalesInvoiceId { get; set; }
    public int CustomerId { get; set; }

    public virtual IEnumerable<SalesInvoiceDetail> SalesInvoiceDetails{ get; set; }
}

My Sales Invoice Detail Model

public class SalesInvoiceDetail
{
    public int SalesInvoiceDetailId { get; set; }
    public int Qty { get; set; }
    public decimal UnitPrice { get; set; }
}

question from:https://stackoverflow.com/questions/66050888/calculated-property-for-model-ef-core-property-or-method

Answer 1

Create helper methods which returns desired results. Everything should play around IQueryable:

public class CustomerIdWithBalance
{
   public int CustomerId { get; set; }
   public decimal Balance { get; set; }
}

public class CustomerWithBalance
{
   public Customer Customer { get; set; }
   public decimal Balance { get; set; }
}

public static class BusinessLogicExtensions
{
    public static IQueryable<CustomerIdWithBalance> GetCustomerIdAndBalance(this IQueryable<Customer> customers)
    {
        var grouped = 
           from c in customers
           from si in c.SalesInvoices
           from sid in si.SalesInvoiceDetails
           group sid by new { c.CustomerId } into g
           select new CustomerIdWithBalance
           {
               g.Key.CustomerId,
               Balance = x.Sum(x => x.Qty * x.UnitPrice)
           } 
        return grouped;
    }

    public static IQueryable<CustomerWithBalance> GetCustomerAndBalance(this IQueryable<CustomerIdWithBalance> customerBalances, IQueryable<Customer> customers)
    {
        var query =
           from b in customerBalances
           join c in customers on b.CustomerId equals c.CustomerId
           select new CustomerWithBalance
           {
              Customer = c,
              Balance = b.Balance
           };
        return query;
    }

}

Later when you need to return that with API call (hypothetic samples)

var qustomerIdsWithHighBalance = 
   from c in ctx.Customers.GetCustomerIdAndBalance()
   where c.Balance > 1000
   select c.CustomerId;

var qustomersWithHighBalance = 
   ctx.Customers.GetCustomerIdAndBalance()
   .Where(c => c.Balance > 1000)
   .GetCustomerAndBalance(ctx.Customers);

var customersByMatchAndPagination = ctx.Customers
    .Where(c => c.Name.StartsWith("John"))
    .OrderBy(c => c.Name)
    .Skip(100)
    .Take(50)
    .GetCustomerAndBalance(ctx.Customers);

You will get desired results without additional database roundtrips. With properties you may load too much data into the memory.

It is everything about using EF with its limitations. But world is not stopped because EF team is too busy to create performance effective things.

Let's install https://github.com/axelheer/nein-linq

And create extension methods around Customer

public static class CustomerExtensions
{
   [InjectLambda]
   public static TotalBalance(this Customer customer)
      => throw new NotImplmentedException();
  
   static Expression<Func<Customer, decimal>> TotalBalance()
   {
       return customer => 
           (from si in customer.SalesInvoices
            from sid in si.SalesInvoiceDetails
            select sid)
           .Sum(x => x.Qty * x.UnitPrice));
   }
}

And everything become handy:

var customersWithHighBalance = 
   from c in ctx.Customers.ToInjectable()
   where c.TotalBalance() > 1000
   select c;

var customersWithHighBalance = 
   from c in ctx.Customers.ToInjectable()
   let balance = c.TotalBalance()
   where balance = balance > 1000
   select new CustomerWithBalance
   {
       Customer = c,
       Balance = balance
   };

var customersWithBalance = 
   from c in ctx.Customers.ToInjectable()
   where c.Name.StartsWith("John")
   select new CustomerWithBalance
   {
       Customer = c,
       Balance = c.TotalBalance()
   };

var paginated = 
    .OrderBy(c => c.Name)
    .Skip(100)
    .Take(50);