How to convert SQL command to LINQ?

Question

I have two classes:

Produção
{
int id,
procedimento int,
quant int
string competencia
}

bpi
{
int id,
ini idProducao
}

How to convert the code below to LINQ?

select ((sum(procedimento) + sum(quant)) mod 1111) + 1111 from producao
left join bpi
on producao.id = bpi.idProducao
where competencia = '01/2017'

The goal is to sum all the values of the columns procedimento and quant where competencia = '01/2017' and producao.id = bpi.idProducao

The sum should be divided by 1111.

To the rest of the division it should be added 1111.

This question of calculating by the 1111, I can do via code.

Answer 1

If based on these 2 classes you can do this query as follows.

public class Producao
{
    public int id { get; set; }
    public int procedimento { get; set; }
    public int quant { get; set; }
    public string competencia { get; set; }
}

public class Bpi
{
    public int id { get; set; }
    public int idProducao { get; set; }
}

Linq

var query = from p in listProd
            join b in listBpi on p.id equals b.idProducao into _b
            from b in _b.DefaultIfEmpty()
            where p.competencia == "01/2017"
            group p by p.id into newGroup
            select new
            {
                ProdutoId = newGroup.Key,
                Soma = ((newGroup.Sum(x => x.quant) + newGroup.Sum(x => x.procedimento)) % 1111) + 1111
            };

Now just adjust to your need, probably in made use of the lists I created you will have this object coming from the database. As you yourself mentioned, perhaps you’d better write the calculation of mod out in the query, so you could return the sum of qnt and the sum of separate procedures and in your application do the rest of the calculation