在实体框架中将Sum(case)SQL语句转换为LINQ查询 [英] Convert Sum(case) SQL statement to LINQ query in Entity Framework

问题描述

下面是我希望转换为 LINQ 查询的 SQL 查询

Below is my SQL query that I'm looking to convert to LINQ query

SELECT 
    ProcessName,
    SUM(CASE WHEN Status = 'In-Progress' THEN 1 ELSE 0 END) As 'In-Progress',
    SUM(CASE WHEN Status = 'Success' THEN 1 ELSE 0 END) As 'Completed',
    Count(CASE WHEN status in ('In-Progress','Success') then 1 end) as Total
FROM
    TableName
GROUP BY 
    ProcessName

推荐答案

第一部分很简单.SQL表达式 SUM(条件为THEN 1 ELSE 0 END时的情况)直接映射到LINQ Sum(条件为1:0).

The first part is easy. The SQL expression SUM(CASE WHEN condition THEN 1 ELSE 0 END) directly maps to the LINQ Sum(condition ? 1 : 0).

更有趣的是SQL表达式 COUNT(CASE WHEN condition THEN 1 END .这是 COUNT(CASE WHEN condition THEN 1 ELSE NULL END 的快捷方式.现在，考虑到SQL COUNT 函数会跳过NULL值，因此LINQ映射可以是 Count(condition)或 Sum(condition?1:0).根据我在EF方面的经验，后者将转化为更好的SQL(前者会从源表中生成更多的子查询读取结果)，因此我总是在LINQ中比实体查询更喜欢它.

More interesting is the SQL expression COUNT(CASE WHEN condition THEN 1 END. It's a shortcut for COUNT(CASE WHEN condition THEN 1 ELSE NULL END. Now, taking into account that SQL COUNT function skips the NULL values, the LINQ mapping could be either Count(condition) or Sum(condition ? 1 : 0). From my experience with EF, the later translates to a better SQL (the former generates additional subquery reading from the source table), so I always prefer it in LINQ to Entities queries.

话虽如此，LINQ查询可能是这样的:

With that being said, the LINQ query could be something like this:

// constants to avoid typos
const string StatusInProgress = "In-Progress";
const string StatusCompleted = "Success";
// needed for IN clause 
var statuses = new[] { StatusInProgress, StatusCompleted };
// the query
var query =
    from e in db.TableA
    group e by e.ProcessName into g
    select new
    {
        InProgress = g.Sum(e => e.Status == StatusInProgress ? 1 : 0),
        Completed = g.Sum(e => e.Status == StatusCompleted ? 1 : 0),
        C = g.Sum(e => statuses.Contains(e.Status) ? 1 : 0),
    };

，生成的SQL如下所示:

and the generated SQL looks like this:

SELECT
    1 AS [C1],
    [GroupBy1].[K1] AS [ProcessName],
    [GroupBy1].[A1] AS [C2],
    [GroupBy1].[A2] AS [C3],
    [GroupBy1].[A3] AS [C4]
    FROM ( SELECT
        [Extent1].[K1] AS [K1],
        SUM([Extent1].[A1]) AS [A1],
        SUM([Extent1].[A2]) AS [A2],
        SUM([Extent1].[A3]) AS [A3]
        FROM ( SELECT
            [Extent1].[ProcessName] AS [K1],
            CASE WHEN (N'In-Progress' = [Extent1].[Status]) THEN 1 ELSE 0 END AS [A1],
            CASE WHEN (N'Success' = [Extent1].[Status]) THEN 1 ELSE 0 END AS [A2],
            CASE WHEN ([Extent1].[Status] IN (N'In-Progress', N'Success')) THEN 1 ELSE 0 END AS [A3]
            FROM [dbo].[TableName] AS [Extent1]
        )  AS [Extent1]
        GROUP BY [K1]
    )  AS [GroupBy1]

如您所见，除了丑陋的外部部分什么都不做之外，最里面的子查询中包含的主要部分几乎与所讨论的SQL查询相同.

As you can see, except the ugly outer parts that do nothing, the main part contained in the innermost subquery is almost identical to the SQL query in question.

这篇关于在实体框架中将Sum(case)SQL语句转换为LINQ查询的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！