Convert SQL query to LINQ

Question

Convert SQL query to LINQ

Navigation

#1 by (0 votes)

5

What would be the converted SQL query for LINQ:

"SELECT SUM(valor_negocio) valor_negocio, " +
        " MONTHNAME(STR_TO_DATE(MONTH(data_inicio), '%m')) mes," +
        " STATUS" +
        " FROM negocio" +
        " WHERE data_inicio BETWEEN '2017/01/01' AND '2017/12/31'" +
        " AND id_empresa = "  + idEmpresa +
        " GROUP BY MONTH(data_inicio), STATUS, mes";

I need the result to be as follows:

valor_negocio   mes         STATUS
---------------------------------------------
1500.00         January     Fechado
1260.00         February    Fechado
500.00          March       Fechado
1300.00         May         Fechado
1500.00         June        Fechado
1000.00         July        Fechado
3800.00         August      Fechado
0.00            September   Contato
3000.50         September   Em andamento
1000.00         September   Fechado
500.00          September   Perdido
5500.00         October     Em andamento
7100.00         October     Fechado
500.00          November    Em andamento
400.00          November    Fechado

mysql sql .net linq

asked by anonymous 04.11.2017 / 20:26

1 answer

How to display data from a serialized file in Java? Extension to improve Atom productivity

score 0 · Answer 1

Alessandre, to define the Linq expression, you need to have access to the table, through a DbContext or something like that.

But assuming there is a variable db with access and a Tabela , the code can look like this:

using System.Globalization;
using System.Linq;
...
DateTimeFormatInfo dTFI = new DateTimeFormatInfo();
var resultado =
    db.Tabela
    .Where(n => n.data_inicio >= new DateTime(2017,1,1)
                && n.data_inicio <= new DateTime(2017,11,30)
                && n.id_empresa == 1)
    .GroupBy(n => 
                new { 
                    mes = n.data_inicio.Month,
                    STATUS = n.STATUS },
             n => n.valor_negocio,
             (g, n) =>
                new {
                    valor_negocio = n.Sum(),
                    mes = dTFI.GetMonthName(g.mes),
                    STATUS = g.STATUS });