CODEWITHSUNDEEP
entity-frameworkentity-framework-core
Mariana
Mariana

Reputation: 170

Select SQL case when for Entity Framework

I have this select which works in T-SQL, but need to convert to Entity Framework. How can I use case in Entity Framework?

select 
    *
from 
    Contrato c
where 
    c.QtdParcelasFalta > 0 
    and (case
            when c.DiaEspecificoMarcar = 1 
               then c.DiaEspecifico
            when c.DiaEspecificoMarcar = 0
               then (select convert(int, substring(DataCobranca, 2, 2)) 
                     from Contrato 
                     where Contrato.Id = c.Id) - 
                    (select e.DataProcessamentoNota 
                     from Contrato t 
                     inner join PedidoVenda p on p.id = t.PedidoVendaId 
                     inner join Empresas e on p.EmpresaID = e.Id 
                     where p.id = t.PedidoVendaId 
                       and t.id = c.Id and p.EmpresaID = 1)
         end) = (SELECT DAY(GETDATE()))

I tried it like this, but it didn't work out, did not pass the correct values, because I'm not aware of pass to Entity Framework:

var contrato = db.Contrato
                 .Include(a => a.PedidoVenda)
                 .Include(a => a.Cliente)
                 .Where(a => a.PedidoVenda.EmpresaID == model.EmpresaID 
                             && a.Cancelado == false 
                             && a.DiaEspecificoMarcar == true
                             && a.DiaEspecifico == int.Parse(DateTime.Now.ToString("dd")) ||
                        (int.Parse(a.DataCobranca.Substring(1, 2)) - a.PedidoVenda.Empresa.DataProcessamentoNota)
              == int.Parse(DateTime.Now.ToString("dd"))
              ).ToList();

Upvotes: 0

Views: 88

Answers (1)

ilkerkaran
ilkerkaran

Reputation: 4354

You are actually on the right track. You just need to add when conditions to your lef and right conditions like this:

var contrato = db.Contrato.Include(a => a.PedidoVenda).Include(a => a.Cliente).Where
                  (a => a.PedidoVenda.EmpresaID == model.EmpresaID && a.Cancelado == false &&
              a.DiaEspecificoMarcar == false && a.DiaEspecifico == int.Parse(DateTime.Now.ToString("dd")) ||
              ( a.DiaEspecificoMarcar == true && int.Parse(a.DataCobranca.Substring(1, 2)) - a.PedidoVenda.Empresa.DataProcessamentoNota)
              == int.Parse(DateTime.Now.ToString("dd"))
              ).ToList();

I also suggest you use DateTime.Now from a variable to be sure both int.Parse gets the same input

Upvotes: 2

Related Questions