Convert SQL query to LINQ

Question

How would this SQL query convert to 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

Answer 1

Alessandre, to define the Linq expression, you need to have access to the table, through a Dbcontext or something similar.

But assuming there’s a variable db with access to 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 });