Code First and Calculated Fields

Question

Code First and Calculated Fields

Navigation

#1 by (4 votes)
#2 by (4 votes)

8

I have a class Pedido , which has a calculated field, which is the value of the request, consisting 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 value of the request directly, I'm going to 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 requests from the database to filter into 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 existing logic in the RequestData property of class Pedido .

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

A trigger would also work, but I would be stuck with the DBMS, 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?

c# entity-framework code-first

asked by anonymous 25.11.2015 / 14:23

2 answers

4

Unfortunately, what you want can not be done this way, because running a Where on context items returns an ICollection, in which case the data will only be brought to the application when you give% or select the desired data.

So when you call the computed field directly from ToList() it can not calculate because the data is not loaded into memory.

What you can do is implement a property with the IQueryable attribute and implement the logic in the database.

See example at the link below:

link

25.11.2015 / 14:31

How can I record information with Arduino? What is the difference between a .c and .cpp file?

score 4 · Accepted Answer

You can do this, but some care is needed:

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

If you do this:

[NotMapped]
public decimal ValorPedido
{
    ...
}

2. Do the calculation only when there are elements, handling 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 behind this is to show what you can do, but that does not mean it's the right way to do it. The correct form I put below.

But if I try to do a query with lambda or linq by filtering the value of the request directly, I'm going to 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 Pedidos and Itens tables. You can filter with Sum() within the query like this:

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();

Is Entity 7 able to interpret ValueOrder?

By error, we conclude that no.

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

It is a problem of approach. You are trying to use the framework in a way not foreseen by it.