Translating a sql Case When statement to LINQ

Question

How can I do this CASE WHEN statement in LINQ ?

SELECT c.nm_grupo, c.cd_grupo, YEAR(GETDATE()),
    CASE WHEN (
                SELECT SUM(p.valor)
                FROM ind_receita p
                JOIN ind_equipto o ON p.cd_equipto = o.cd_equipto
                JOIN ind_grupo c2 ON o.cd_grupo = c2.cd_grupo
                WHERE c2.cd_grupo = c.cd_grupo
                AND YEAR(p.dt_emissao) = YEAR(GETDATE())
              ) 
              IS NULL THEN 0 ELSE 
              (
                SELECT SUM(p.valor)
                FROM ind_receita p
                JOIN ind_equipto o ON p.cd_equipto = o.cd_equipto
                JOIN ind_grupo c2 ON o.cd_grupo = c2.cd_grupo
                WHERE c2.cd_grupo = c.cd_grupo
                AND YEAR(p.dt_emissao) = YEAR(GETDATE())
              ) 
      END AS valor
FROM ind_grupo c
ORDER BY c.nm_grupo

MY TRY

        // SELECT * FROM IND_GRUPO IN LINQ
        var GetAllGroups = (from c in _repositorio.GetGrupoEquipamentos()
                           select c);

        // THE SAME SUBQUERY IN LINQ
        var MySubQuery= (from p in _repositorio.GetReceitas()
                     join o in _repositorio.GetEquipamentos() on p.CodigoEquipamento.Equipamento_Id equals o.Equipamento_Id
                     join a in _repositorio.GetGrupoEquipamentos() on o.CodigoGrupo.Grupo_Id equals a.Grupo_Id
                     where p.DataHoraCriacaoRegistro.Year == DateTime.Now.Year
                     && a.Grupo_Id == GetAllGroups.Select(a => a.Grupo_Id)
                     select p.Valor).Sum();

your select statments translate to a LINQ statment and the `case when then` translate. to a ternary operator — RadioSpace, Oct 16 '14 at 18:22
possible duplicate of [Select case in LINQ](http://stackoverflow.com/questions/936028/linq-case-statement) — AdamBT, Oct 16 '14 at 18:25
I updated my question with how far I did. I need a third variable to get the result ? — Lucas_Santos, Oct 16 '14 at 18:31

Sergey Kalinichenko · Accepted Answer · 2014-10-17T19:54:30.183

First, let's remove the CASE from your SQL: it can be replaced by ISNULL function:

SELECT
    c.nm_grupo
,   c.cd_grupo
,   YEAR(GETDATE())
,   ISNULL((
        SELECT SUM(p.valor)
        FROM ind_receita p
        JOIN ind_equipto o ON p.cd_equipto = o.cd_equipto
        JOIN ind_grupo c2 ON o.cd_grupo = c2.cd_grupo
        WHERE c2.cd_grupo = c.cd_grupo
        AND YEAR(p.dt_emissao) = YEAR(GETDATE())
        )
    ,  0) AS valor
FROM ind_grupo c
ORDER BY c.nm_grupo

Now let's convert this to LINQ:

var GetAllGroups = _repositorio
    .GetGrupoEquipamentos()
    .Select(c => new {
        c.nm_grupo // Use C# fields that you mapped to these fields in DB
    ,   c.cd_grupo // Same as above
    ,   valor = (from p in _repositorio.GetReceitas()
                 join o in _repositorio.GetEquipamentos() on p.CodigoEquipamento.Equipamento_Id equals o.Equipamento_Id
                 join a in _repositorio.GetGrupoEquipamentos() on o.CodigoGrupo.Grupo_Id equals a.Grupo_Id
                 where c.Grupo_Id == a.Grupo_Id && p.DataHoraCriacaoRegistro.Year == DateTime.Now.Year
                 select (decimal?)p.Valor).Sum() ?? 0
    });

Note a cast of p.Valor to a nullable decimal. If you want a different type, replace this cast as appropriate.

In the where clause in LINQ, in the `GetAllGroups.Select(x => x.Grupo_Id)` in this case I can't get the `x.Grupo_Id` or any variables. — Lucas_Santos, Oct 17 '14 at 19:48
@Lucas_Santos Please see the edit - I changed the join condition to match the one from your SQL. Did SQL with `ISNULL` produce the same result as the one that you have? — Sergey Kalinichenko, Oct 17 '14 at 19:55
Yes, the result of the sentence was exactly the same. I'll try the LINQ query as your update — Lucas_Santos, Oct 17 '14 at 20:57

Translating a sql Case When statement to LINQ

1 Answers1