c# - 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 ?

Answer 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.