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 ?

score 20 · Accepted Answer · answered Dec 10 '13 at 14:25

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.

tschmit007 · Answer 2 · 2013-12-10T15:12:28.617

11

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]

edited Dec 10 '13 at 15:12

answered Dec 10 '13 at 14:24

tschmit007

7,559
2
35
43

Doesn't really answer the question. The grouping and sum are nowhere to be found, which I take were the op's main problem. – Kippie Dec 10 '13 at 14:43
2

imho the question is: What is the equivalent of “CASE WHEN THEN” (T-SQL) with Entity Framework? – tschmit007 Dec 10 '13 at 14:48
fair enough. Could you do a meaningless edit so I can undo my vote? – Kippie Dec 10 '13 at 15:03

score 1 · Answer 3 · answered Jun 18 '21 at 06:51

1

If you're using method syntax, this is what I use and find readable

Status = 
    s.Status == "Active" ? "1 Active" :
    s.Status == "Storage" ? "2 Storage" :
    s.Status == "Deactivated" ? "3 Deactivated" :
    "9 Unknown",

where the last value, is the else or catch all

answered Jun 18 '21 at 06:51

Dale Fraser

4,623
7
39
76

Beautiful! I've never seen nested ternary statements written that way and never thought to do it. I've always seen lots of parenthesis that just convolute it and didn't until this moment realize that they actually do, in effect, work just like a proper CASE or switch statement. – BVernon May 31 '23 at 22:54

score -5 · Answer 4 · answered Apr 18 '17 at 21:43

One way to accomplish this is to simply use .ThenBy.
For example, if query looks like this, where you want the 3 possible values ordered first (in order of the when case), the table records will be ordered by MANAGER, ADMIN, SUPPORT, and all other types:

select * from people
    order by
    case people_type 
         when 'MANAGER' Then 0
         when 'ADMINISTRATOR' Then 1
         when 'SUPPORT' Then 2
         else 3 end

The equivalent linq would then be:

dbContext
.OrderBy(x => x.people_Type == x => x.people_Type == "SUPPORT")
.ThenBy("ADMINISTRATOR")
.ThenBy(x => x.people_Type == "MANAGER")

OP asked the question about CASE WHEN THEN that appears in Select clause. — Devesh, Jan 09 '19 at 05:11

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

4 Answers4

Linked