Get Total Relationship Items 1:N

Question

I have two Product and Items tables - these tables have Father/Son relationship respectively. I would like to know how to get the total of items in the Items table knowing that each customer can own more than one Order and that each order consists of several items? If possible, I would also like to know how I do to get the total value of the items.

I’m using Entityframework with Code First. The query already has Include but so far when adding Groupby + Count the returned result is the total of Requests (parent table).

Thanks in advance for the strength.

Answer 1

I’m guessing the following construction of your Models:

public class Produto
{
    [Key]
    public int ProdutoId { get; set; }

    [Required]
    public int Nome { get; set; }
    [Required]
    public decimal Preco { get; set; }

    public virtual ICollection<PedidoProduto> PedidoProdutos { get; set; } // Aqui seria sua relação de itens
}

public class Pedido
{
    [Key]
    public int PedidoId { get; set; }
    public int ClienteId { get; set; }

    public virtual Cliente Cliente { get; set; }

    public virtual ICollection<PedidoProduto> PedidoProdutos { get; set; }
}

public class PedidoProduto
{
    [Key]
    public int PedidoProdutoId { get; set; }
    public int PedidoId { get; set; }
    public int ProdutoId { get; set; }

    [Required]
    public int Quantidade { get; set; }

    public virtual Pedido Pedido { get; set; }
    public virtual Produto Produto { get; set; }
}

I would like to know how to get the total of items in the Items table knowing that each customer can own more than one Order and that each order consists of several items?

It can be done like this:

var pedido = db.Pedidos.FirstOrDefault(p => p.PedidoId == 1);
var itens = pedido.PedidoProdutos.Select(pp => pp.Produto).ToList();

If possible, I would also like to know how I do to get the total value of the items.

Thus:

var total = pedido.PedidoProdutos.Sum(pp => pp.Produto.Preco * pp.Quantidade);