How to do a SUM in different table fields in the Entity Framework?

Question

How to do a SUM in different table fields in the Entity Framework?

Navigation

#1 by (2 votes)

2

This is the SQL that I have as an example, would you like to do the same with Linq ?

select SUM(iv.precoMediano * oi.quantidade) as total from orc_orcamentoItem oi
inner join orc_insumoValor iv on oi.codigo = iv.codigoSinapi
where oi.grupoPai = 1 
group by oi.grupoPai

My main question here is: How to do Sum in linq using Fields from the two tables. For example: Sum (table1 => table1.quantity * table2.value)

Entities

Budget item

public int Item_id { get; set; }
public int Orcamento_id { get; set; }
public string Tipo_desc { get; set; }
public string Descricao { get; set; }
public int GrupoPai { get; set; }
public int Codigo { get; set; }
public decimal Quantidade { get; set; }
public string base_desc { get; set; }

Value Input

public int InsumoDados_id { get; set; }
public int InsumoValor_id { get; set; }
public int CodigoSinapi { get; set; }
public string OrigemPreco { get; set; }
public decimal PrecoMediano { get; set; }

c# entity-framework linq

asked by anonymous 05.10.2017 / 15:17

1 answer

Divide a query Effect with JavaScript

score 2 · Accepted Answer

Below is the version in LINQ and the tables I used in this example.

LINQ Query

var result = from oi in ctx.OrcamentoItem // onde ctx é o contexto do EF 
             join iv in ctx.InsumoValor on oi.Codigo equals iv.CodigoSinapi
             where oi.GrupoPai == 1
             group new { oi, iv } by oi.GrupoPai into grp
             select new
             {
                Total = grp.Sum(t => t.oi.Quantidade * t.iv.PrecoMediano)
             };

Classes

public class OrcamentoItem
{
    public int Item_id { get; set; }
    public int Orcamento_id { get; set; }
    public string Tipo_desc { get; set; }
    public string Descricao { get; set; }
    public int GrupoPai { get; set; }
    public int Codigo { get; set; }
    public decimal Quantidade { get; set; }
    public string base_desc { get; set; }
}

public class InsumoValor
{
    public int InsumoDados_id { get; set; }
    public int InsumoValor_id { get; set; }
    public int CodigoSinapi { get; set; }
    public string OrigemPreco { get; set; }
    public decimal PrecoMediano { get; set; }
}

See working in .NET Fiddle