Code First and Calculated Fields

Question

I have a class Pedido, which has a calculated field, which is the order value, composed of the sum of the items minus the discount.

public class Pedido
{
    public ICollection<PedidoItem> Itens { get; set; }

    public decimal ValorDesconto { get; set; }

    public decimal ValorPedido
    {
        get { return this.Itens.Sum(e => e.Quantidade * e.Valor) - this.ValorDesconto; }
    }
}

public class PedidoItem
{
    public int CodigoProduto { get; set; }
    public decimal Valor { get; set; }
    public decimal Quantidade { get; set; }
}

But if I try to do a query with lambda or Linq by filtering the order value directly, I will get an error.

// Linq nao reconhece ValorPedido
List<Pedido> pedidos = db.Pedidos.Where(p => p.ValorPedido > x).ToList(); 

I could do:

List<Pedido> pedidos = db.Pedidos.ToList().Where(p => p.ValorPedido > x).ToList()

But this second approach will bring all database requests to filter in memory, which may be bad for performance.

I could do a Linq, grouping, and adding, which would generate a more performative query but I would be repeating the logic already existing in the Value property of the class Pedido.

Entity 7 is able to interpret ValorPedido or the ValorPedido would have to have a set, and my application feed this attribute(but there are those who say that it should not store calculated fields)?

A Rigger would also solve, but I would be stuck with the SGBD, for example, I could not change the Provider only. Is there something to be done or is this a limitation of the ORM and period?

Answer 1

Unfortunately, what you want can’t be done this way, because when running a Where in the context items returns a Icollection, and in this case the data will only be brought to the application when you give a ToList() or select the desired data.

So when you call the calculated field directly from IQueryable it cannot calculate because the data is not loaded in memory.

What you can do is implement a property with the attribute DatabaseGeneratedOption.Computed and implement database logic.

See example in the link below:

http://www.davepaquette.com/archive/2012/09/23/calculated-columns-in-entity-framework-code-first-migrations.aspx

Answer 2

It is possible to do this way, but some care is needed:

1. Inform the Entity Framework that the property is not bank mapped

If it does so:

[NotMapped]
public decimal ValorPedido
{
    ...
}

2. Calculate only when there are elements, treating exceptions

[NotMapped]
public decimal ValorPedido
{
    get 
    {
        try {
            return this.Itens.Sum(e => e.Quantidade * e.Valor) - this.ValorDesconto;
        } catch {
            throw new Exception("Coleção não inicializada corretamente");
        } 
    }
}

The idea explaining this to you is to show what you can do, but that doesn’t mean it’s the right way to do it. The right way I put it below.

But if I try to do a query with lambda or Linq by filtering the order value directly, I will get an error.

List<Pedido> pedidos = db.Pedidos.Where(p => p.ValorPedido > x).ToList();

This does not work because you are mixing mapped field logic with unmapped field logic. You need to have the following to work:

List<Pedido> pedidos = db.Pedidos
                         .Include(p => p.Itens) // Carrego antecipadamente Itens. É equivalente a um JOIN.
                         .AsEnumerable() // Aqui resolvo o select, e o Where abaixo passa a funcionar.
                         .Where(p => p.ValorPedido > x)
                         .ToList();

Obviously this will give some performance problem depending on the size of the tables Pedidos and Itens. You can filter with the Sum() within the consultation thus:

List<Pedido> pedidos = db.Pedidos
                         .Include(p => p.Itens) // Carrego antecipadamente Itens. É equivalente a um JOIN.
                         .Where(p => p.Itens.Sum(e => e.Quantidade * e.Valor) > x)
                         .ToList();

Entity 7 is able to interpret Requested Value?

By mistake, we conclude that.

Is there something to be done or is this a limitation of the ORM and period?

It’s an approach problem. You’re trying to use the framework in a manner not foreseen by him.