问题描述:

Is it possible to return calculated values with an entity model when retrieving from the database. For example -

public class PaymentCertificate : DBModelBase

{

public PaymentCertificate()

{

ValuationItems = new Collection<ValuationItem>();

}

public virtual ICollection<ValuationItem> ValuationItems { get; set; }

}

public class ValuationItem: DBModelBase

{

public virtual Cost Cost { get; set; }

public decimal ValuationQuantity { get; set; }

}

public class Cost: DBModelBase

{

public decimal Price { get; set; }

}

When I return say a few hundred PaymentCertificate models from the database I want to calculate the Total for each so currently I am using a loop e.g.

foreach (var cert in PaymentCertificateList)

{

var total = PaymentCertificateList

.Sum(x => x.ValuationItems.ValuationQuantity*x.ValuationItems.Cost.Price);

}

This is really slow when calculating a few hundred payment certificates and I believe it's because of the calculation with nested entities (and even slower when using automapper). How can I 'preload' the Total value when returning the certificate object from the database? I'm thinking this is the best option as to push the calculation to the database side. Is this possible? Any suggestions welcome.

网友答案:

IF you are lazy loading the referenced tables, it will query the database for each call in the loop. You can avoid this by eagerly loading the related entities using .Include.

https://msdn.microsoft.com/en-us/data/jj574232.aspx

Adding onto that, you can also use projection to do some of the calculations in the initial query. I am not sure if this will work for your situation as you did not post any of your initial queries.

Looking at your model you could do something like this to get all the totals

var totals = DbContext.PaymentCertificates
                .Include("ValuationItems.Cost")
                .Select(c => new {
                    Total = c.ValuationItems.Sum(x => x.Cost.Price*x.ValuationQuantity)
                });
网友答案:

You can add a property to return this for you for each:

 public class PaymentCertificate : DBModelBase
 {
     public PaymentCertificate()
     {
         ValuationItems = new Collection();
     }
     public virtual ICollection ValuationItems { get; set; }

     [NotMapped]
     public decimal Total { get {return ValuationItems.Sum(v => v.ValuationQuantity * v.Cost.Price); }; private set;}
 }

The NotMapped attribute means EF will ignore it.

相关阅读:
Top