Htykuut

I need to covert below SQL query as LINQ query. How can I put conditional if case statements within the order by clause using LINQ?

 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  

My convertion without case statement below,

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();

Try to use trinary ifelse operator inside the orderby statement itself like this modify according to your needs

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" && td1.TypeDefGroup == "ResponseType" ? 1 : (td1.TypeDefDesc == "Failed" && td1.TypeDefGroup == "ResponseType" ? 2 : (td1.TypeDefDesc == "Failed Attempt" && td1.TypeDefGroup == "ResponseType" ? 3 : 4)))

                    select new TypeDefinition

                    {

                        ResponseTypeReason = (t1.TypeDefDesc + ":" + t2.TypeDefDesc),

                        ResponseTypeCode = t1.TypeDefid + "~" + t2.TypeDefcode

                    }).ToList();

You should be able to do this using a Ternary Operator.

The ternary operator lets you convert a somewhat long-winded if/else statement into a single line. For example:

int a;

if(test) {

    a = 1;

} else {

    a = 2;

}

Can get converted into:

int a = test ? 1 : 2;

More complicated expressions can also be made by using multiple ternary expressions.

e.g.

int a;

if(testA) {

    a = 1;

} else if(testB) {

    a = 2;

} else {

    a = 3;

}

Can be converted into this:

int a = testA ? 1 : // if(testA)

        testB ? 2 : // else if(testB)

        3; // else

Alternatively formatted as:

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

In your scenario, the ternary operator can be used in the orderby clause:

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();

Check out the MSDN docs for more info.

Done this by mywork. using ThenBy

objTypeDefLst = (from t1 in objTypeDefLst

                                     join t2 in objTypeDefLst

                                         on t1.TypeDefid equals TUtil.CheckInt(t2.ParentId, 0)

                                     where t1.TypeDefGroup.ToUpper().Trim() == strTypeDefGrp.ToUpper().Trim()

                                     select new TypeDefinition

                                     {

                                         ResponseTypeReason = (t1.TypeDefDesc + ":" + t2.TypeDefDesc),

                                         ResponseTypeCode = t1.TypeDefid + "~" + t2.TypeDefcode

                                     }).ToList();

                    objTypeDefLst = objTypeDefLst.OrderBy(a => a.TypeDefDesc == strTypeDefDescSuccess && a.TypeDefGroup == strTypeDefGrp)

                                    .ThenBy(a => a.TypeDefDesc == strTypeDefDescFailed && a.TypeDefGroup == strTypeDefGrp)

                                    .ThenBy(a => a.TypeDefDesc == strTypeDefDescFailedAtte && a.TypeDefGroup == strTypeDefGrp).ToList();