Geek Answers Handbook

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

I have a Transact-SQl query that I use a lot and I want to get the equivalent with Entity Framework. But I do not know how to make the expression "CASE WHEN" with EF. Here is the simplified code for 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 this is possible and how to do it with EF?

+4
source share
3 answers

In this case, I would say that conditional operator ( p ? x : y) is a good replacement.

// 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 Whereand Sum:

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

I'm not sure if these SQL translate accurately, but they should have the same result.

+9
source

: linq transact sql CASE THEN ?::

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


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]
+6

- .ThenBy.
, , , 3 ( ), MANAGER, ADMIN, SUPPORT :

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

linq :

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

More articles:


All Articles