时间戳

首页 > 解决方案 > 使用多个 case 语句转换 ORDER BY

c# - 使用多个 case 语句转换 ORDER BY

问题描述

我需要将 SQL 查询隐藏为 LINQ 查询。如何使用 LINQ 在 order by 子句中放置条件 if case 语句？

 SELECT   
     td1.TYPEDEFDESC +':'+ td2.TypeDefDesc as ResponseTypeReason,  
     convert(varchar(20),td1.TypeDefid) +'~'+ convert(varchar(20),td2.TypeDefcode) as ResponseTypeCode,  
     td1.TYPEDEFID,  
     td1.TYPEDEFGROUP,  
     td1.TYPEDEFCODE,  
     td1.TYPEDEFDESC,  
     td1.PARENTID  
     FROM TYPEDEFINITION td1 WITH (NOLOCK)  
     join TypeDefinition td2 with (nolock)   
     on td1.TypeDefid = td2.ParentId  
     WHERE td1.TypeDefGroup='ResponseType'  
     and td1.Active=1   
     and td2.Active=1  
     order by   
     case when td1.TypeDefDesc='Successful' and td1.TypeDefGroup='ResponseType' then 1  
     when td1.TypeDefDesc='Failed' and td1.TypeDefGroup='ResponseType' then 2  
     when td1.TypeDefDesc='Failed Attempt' and td1.TypeDefGroup='ResponseType' then 3 end asc

我的转换没有下面的案例陈述， 

objTypeDefLst = (from t1 in objTypeDefLst join t2 in objTypeDefLst
                 on t1.TypeDefid equals TUtil.CheckInt(t2.ParentId,0)
                 where t1.TypeDefGroup == strTypeDefGrp  
                 orderby(t1.TypeDefGroup)                                     
                 select new TypeDefinition {  
                       ResponseTypeReason = (t1.TypeDefDesc +":" +t2.TypeDefDesc),
                       ResponseTypeCode = t1.TypeDefid +"~" + t2.TypeDefcode
                                        }).ToList();

标签： c#linq

解决方案

您应该能够使用三元运算符来做到这一点。

三元运算符允许您将有点冗长的if/else语句转换为单行。例如：

int a;
if(test) {
    a = 1;
} else {
    a = 2;
}

可以转换成：

int a = test ? 1 : 2;

使用多个三元表达式也可以做出更复杂的表达式。

例如

int a;
if(testA) {
    a = 1;
} else if(testB) {
    a = 2;
} else {
    a = 3;
}

可以转换成这样：

int a = testA ? 1 : // if(testA)
        testB ? 2 : // else if(testB)
        3; // else

或者格式化为：

int a = (testA ? 1 : (testB ? 2 : 3));

在您的场景中，可以在orderby子句中使用三元运算符：

objTypeDefLst = (from t1 in objTypeDefLst join t2 in objTypeDefLst
            on t1.TypeDefid equals TUtil.CheckInt(t2.ParentId,0)
            where t1.TypeDefGroup == strTypeDefGrp  
            orderby 
                ((t1.TypeDefDesc == "Successful" && 
                 t1.TypeDefGroup == "ResponseType") ? 1 :
                (t1.TypeDefDesc == "Failed" && 
                 t1.TypeDefGroup == "ResponseType") ? 2 :
                (t1.TypeDefDesc == "Failed Attempt" && 
                 t1.TypeDefGroup == "ResponseType") ? 3 :
                 4) // Whatever the "else" group is
            select new TypeDefinition {  
                   ResponseTypeReason = (t1.TypeDefDesc +":" +t2.TypeDefDesc),
                   ResponseTypeCode = t1.TypeDefid +"~" + t2.TypeDefcode
            }).ToList();

查看MSDN 文档以获取更多信息。

推荐阅读