LINQ to SQL 实现 CASE WHEN THEN 语句

Ø  前言

没有什么特别的，只是觉得 LINQ 的功能其实还是蛮强大的，所以简单记录下，算是工作笔记吧，有可能还能帮助到其他同学呢^_^。

Ø  下面主要使用了 C# 三元运算符实现实现 SQL 中的 CASE WHEN THEN 语句。

1)   C#

const string deliverDM = "派送宣传册", haveKPInfo = "获得KP信息", talkWithKP = "和KP沟通", trial = "产品试样", turnover = "下单成交";

var query = (from t1 in DataContext.CustomerVisitInfo

                where t1.SaleUserId == salesUserId && (t1.VisitTime >= mbdt && t1.VisitTime <= medt)

                group t1 by t1.VisitStatus into g1

                select new

                {

                    VisitStatusName = (

                        g1.Key == (int)VisitStates.DeliverDM ? deliverDM

                        : g1.Key == (int)VisitStates.HaveKPInfo ? haveKPInfo

                        : g1.Key == (int)VisitStates.TalkWithKP ? talkWithKP

                        : g1.Key == (int)VisitStates.Trial ? trial

                        : g1.Key == (int)VisitStates.Turnover ? turnover : "其他"),

                    CustomerCount = g1.Count()

                }).ToList();

2)   生成SQL：

exec sp_executesql N'SELECT

    [GroupBy1].[K1] AS [VisitStatus],

    CASE WHEN (1 = [GroupBy1].[K1]) THEN N''派送宣传册'' WHEN (2 = [GroupBy1].[K1]) THEN N''获得KP信息'' WHEN (3 = [GroupBy1].[K1]) THEN N''和KP沟通'' WHEN (4 = [GroupBy1].[K1]) THEN N''产品试样'' WHEN (5 = [GroupBy1].[K1]) THEN N''下单成交'' ELSE N''其他'' END AS [C1],

    [GroupBy1].[A1] AS [C2]

    FROM ( SELECT

        [Extent1].[VisitStatus] AS [K1],

        COUNT(1) AS [A1]

        FROM [dbo].[CustomerVisitInfo] AS [Extent1]

        WHERE ([Extent1].[SaleUserId] = @p__linq__0) AND ([Extent1].[VisitTime] >= @p__linq__1) AND ([Extent1].[VisitTime] <= @p__linq__2)

        GROUP BY [Extent1].[VisitStatus]

    )  AS [GroupBy1]',N'@p__linq__0 bigint,@p__linq__1 datetime2(7),@p__linq__2 datetime2(7)',@p__linq__0=131,@p__linq__1='2017-05-01 00:00:00',@p__linq__2='2017-05-31 23:59:59'

 

Ø  思考：以上示例 THEN 中只是输出了常量字符串，也可以尝试输出其他语句结果，例如：嵌套子查询等。