What is the equivalent of “CASE WHEN THEN” (T-SQL)

问题:

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 ?

回答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.

回答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]

回答3:

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")