What is the equivalent of "CASE WHEN THEN" (T-SQL) with Entity Framework?

Question

I have a Transact-SQl request that I use a lot and I want to get the equivalent with Entity Framework. But I don't know how to make a "CASE WHEN" statement with EF. Here is a simplified code of my request :

SELECT Code,
SUM(CASE WHEN Month=1 THEN Days Else 0 END) AS Jan,
FROM MyTable 
GROUP BY Code

Can you tell me if it's possible and how to do this with EF ?

Solution 1

In this case, I'd say the conditional operator (p ? x : y) is a good substitute.

// context.MyTable is an IQueryable<MyTable>
var query = from t in context.MyTable
            group t by t.Code into grp
            select
            new {
                Code = grp.Key,
                Jan = grp.Sum(x => x.Month == 1 ? x.Days : 0),
            };

Or combine a Where and a Sum:

                Jan = grp.Where(x => x.Month == 1).Sum(x => x.Days),

I'm not sure what SQL these translate to exactly, but they should both have the same result.

Solution 2

as illustrated by the following, the linq equivalent of transact sql CASE WHEN THEN is the conditional operator ?: :

from u in Users
select new {
name = u.Login,
imported = (u.ImportedId != null ) ? 1 : 0
}

is translated as

SELECT 
1 AS [C1], 
[Extent1].[Login] AS [Login], 
CASE WHEN ([Extent1].[ImportedId] IS NOT NULL) THEN 1 ELSE 0 END AS [C2]
FROM [dbo].[VIPUsers] AS [Extent1]

Comments

[Kippie]

Doesn't really answer the question. The grouping and sum are nowhere to be found, which I take were the op's main problem.

[tschmit007]

imho the question is: What is the equivalent of “CASE WHEN THEN” (T-SQL) with Entity Framework?

[Kippie]

fair enough. Could you do a meaningless edit so I can undo my vote?

[Devesh]

OP asked the question about CASE WHEN THEN that appears in Select clause.