Convert SQL query to LINQ

Asked

Viewed 137 times

5

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

1 answer

0

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 });
  • Aaron, how to get the name of the month, in my table only one the initial data_field

  • failed to find Sum()();

  • @alessandremartins, the name of the month you can pick up using Datetimeformatinfo.Getmonthname (as it is a non-Static method you need to first instantiate the Datetimeformatinfo class, and then you can use the Getmonthname method, using as parameter.

  • As for Sum(), it is in System.Linq, so if you have referenced Assembly and used using with it, it should not be giving error.

  • @alessandremartins, I made the adjustments to the code to help you format the data right. It is also important that you validate the data that comes from the BD to make it compatible with the operations you do in the code.

  • the variable type was decimal, I changed to float and Sum() stopped giving error, however, gave another error: Unknown column 'Groupby1.K2' in 'field list', strange, it is waiting that has this field...

Show 1 more comment

Browser other questions tagged

You are not signed in. Login or sign up in order to post.