How to optimize this code?

Asked

Viewed 114 times

4

Is there any faster (performative) way to compare the current value with the previous (bank) value with Entity Framework? Instead of selecting Id for Id (according to property Discount code below), check the array or something like?

With the code below, I check the last price of the value and calculate the discount, however, is made Id for Id.

ha.AddRange(
    array.Select(
        x => new ProductHistory {
            ProductId = x.Id
                , Price = x.Price
                , LastUpdate = DateTime.Now.Date
                , Discount = x.Price / (
                    (Convert.ToDecimal(db.ProductHistory.Where((ProductHistory p) => x.Id == p.ProductId)
                        .OrderByDescending(o => o.LastUpdate)
                        .Select(y => y.Price)
                        .FirstOrDefault()) == 0) ? x.Price :
                    (Convert.ToDecimal(db.ProductHistory.Where((ProductHistory p) => x.Id == p.ProductId)
                        .OrderByDescending(o => o.Id)
                        .Select(y => y.Price)
                        .FirstOrDefault()))) * 100 - 100
        }
    )
);
c# entity-framework performance lambda-expressions

  • Just so I understand: what it is ha and what is array?

  • ha = Producthistory[] array = Product[]

  • I put as array because the Entity Addorupdate only accepts array, if it would not be a list.

1 answer

6


What you’re doing is terrible at performance. My suggestion is:

// Selecione todos os Products envolvidos no que você quer.
// Adiante ProductHistory. Include() usa INNER JOIN.
var allProducts = db.Products
                    .Include(p => p.ProductHistory)
                    .Where(...).ToList();

// Aqui uso apenas operações em memória. 
// O jeito que você estava fazendo abria vários SQL em sequência, 
// o que não vale a pena pra operação que você está fazendo.
foreach (var element in array)
{
    var product = allProducts.FirstOrDefault(p => p.Id == element.ProductId);
    var lastUpdateHistory = product.ProductHistory.OrderBy(ph => ph.LastUpdate).FirstOrDefault();
    var lastId = product.ProductHistory.OrderByDescending(ph => ph.Id).FirstOrDefault();
    element.Discount = lastUpdateHistory.Price > 0 ? array.Price : lastId.Price * 100 - 100;
}

Browser other questions tagged c# entity-framework performance lambda-expressions

You are not signed in. Login or sign up in order to post.