How to convert SQL command to LINQ?

Question

How to convert SQL command to LINQ?

Navigation

#1 by (1 votes)

0

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 procedimento and quant columns where competencia = '01/2017' and producao.id = bpi.idProducao

The sum should be divided by 1111.

The rest of the division should be added to 1111.

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

c# linq

asked by anonymous 12.03.2017 / 20:53

1 answer

Error in the codeigniter's querier builder How does Date Picker and Time Picker work in android Studio?

score 1 · Accepted Answer

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 did of using the lists that I created you will have that object coming from the database. As you yourself have quoted, it might be best to write the calculation of mod off in the query, so you could return the sum of qnt and the procedure sum separated and in your application do the rest of the calculation