Linq equivalent to the t-sql command

Asked

Viewed 101 times

-1

How to make the LINQ equivalent to the following SQL?

Select 
     sum(case when periodo = 1 then isnull(valor,0) else 0 end) Jan,
     sum(case when periodo = 2 then isnull(valor,0) else 0 end) Feb,
     sum(case when periodo = 3 then isnull(valor,0) else 0 end) Mar
...
from tablela
 inner join outratabela
c# sql linq
  • 1

    What do you know about LINQ? Have you tried anything? Share with us :D

2 answers

1

I made an example algorithm for you to apply, but you will need to replace it with your tables.

I created a class that represents the data that will be returned by the query:

    public class Soma
    {
        public Soma() { }

        public Soma(int periodo, decimal valor)
        {
            Periodo = periodo;
            Valor = valor;
        }

        public int Periodo { get; set; }

        public decimal? Valor { get; set; }

        public string DescricaoMes
        {
            get
            {
                switch (Periodo)
                {
                    case 1:
                        return "Janeiro";

                    case 2:
                        return "Fevereiro";

                    case 3:
                        return "Março";

                    case 4:
                        return "Abril";

                    case 5:
                        return "Maio";

                    case 6:
                        return "Junho";

                    case 7:
                        return "Julho";

                    case 8:
                        return "Agosto";

                    case 9:
                        return "Setembro";

                    case 10:
                        return "Outubro";

                    case 11:
                        return "Novembro";

                    case 12:
                        return "Dezembro";

                    default:
                        return string.Empty;
                }
            }
        }
    }

The query would look like this format:

var result = tabela1.Join(tabela2,
                          tabela1Alias => tabela1Alias.Identificador,
                          tabela2Alias => tabela2Alias.Identificador,
                          (tabela1Alias, tabela2Alias) => new
                          {
                               tabela1Alias.Periodo,
                               tabela2Alias.Valor
                          })
                    .GroupBy(a => a.Periodo)
                    .Select(s => new Soma
                    {
                        Periodo = s.Key,
                        Valor = s.Sum(a => a.Valor.HasValue ? a.Valor : 0)
                    }).ToList();

0

var query = context.Tablelas.GroupBy(g => g.Periodo);

var resposta = new { Jan = query.FirstOrDefault(f => f.Key == 1)?.Sum(s => s.Valor ?? 0) ?? 0,
                     Fev = query.FirstOrDefault(f => f.Key == 2)?.Sum(s => s.Valor ?? 0) ?? 0,
                     Mar = query.FirstOrDefault(f => f.Key == 3)?.Sum(s => s.Valor ?? 0) ?? 0,
                     Abr = query.FirstOrDefault(f => f.Key == 4)?.Sum(s => s.Valor ?? 0) ?? 0,
                      ....
                     Nov = query.FirstOrDefault(f => f.Key == 11)?.Sum(s => s.Valor ?? 0) ?? 0,
                     Dez = query.FirstOrDefault(f => f.Key == 12)?.Sum(s => s.Valor ?? 0) ?? 0 };

Console.WriteLine($"O total de agosto é {resposta.Ago}");

Browser other questions tagged c# sql linq

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