none
(SQL ISNULL and Case Condition ) put in Dynamic Where Clause RRS feed

  • Question

  •  

    I  have to Use ISnull and Case When (Like we use in SQl ), How to use in Dynamic Where LINQ

    strFrom =118.00

    strTo =122.00

    strWhereClause = strWhereClause  +  " (ISNULL(TCF,0)        >= CASE WHEN DS = 0 THEN ISNULL(" + strFrom + ",ISNULL(TCF,0))     ELSE ISNULL(" + strTo + ",ISNULL(TCF,0))    END " ;

    var varReport = context.BFS.Where(strWhereClause).Where(i => i.ReportDt >= dtDateFrom && i.ReportDt <= dtDateTo);

    How to resolve this Issue ?


    Monday, January 13, 2014 10:21 AM

Answers

  • Okey...

    Since the Entity Framework is based on ADO.NET, we can use way as ADO.NET to pass the T-SQL to database and then return the result. For this, we may need to create a big entity class which has all the possible columns.

    string sqlstring = "your T-SQL";

    db.Database.SqlQuery<T>(sqlstring);

    You can spell the sqlstring as whatever you want.

    Regards.


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.

    Thursday, January 16, 2014 8:33 AM
    Moderator

All replies

  • Hello VikeshKataria1986,

    Not everything is supported in Dynamic LINQ DLL, there are only a limited number of methods are supported.

    >> " (ISNULL(TCF,0)        >= CASE WHEN DS = 0 THEN ISNULL(" + strFrom + ",ISNULL(TCF,0))     ELSE ISNULL(" + strTo + ",ISNULL(TCF,0))    END " ;

    Since this is a fixed where clause, why do you need to use the dynamic linq? I think we just need to use the lambda expression is ok.

    ISNULL and CASE WHNE clauses in LINQ query are both equal with ==?: operation, so the  whereClause should be equal below:

    (TCF == null? 0: TFC) >= ((DS==0 ? strFrom==null? (TCF == null? 0: TFC): strFrom):( strTo ==null? (TCF == null? 0: TFC): strTo)

    Regards.


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.

    Tuesday, January 14, 2014 8:53 AM
    Moderator
  • Hi..

    Thnxs for reply...

    These condition will be set at run time so can not declare it as static.

     Any other way to do this ??

    Wednesday, January 15, 2014 9:46 AM
  • Have a try to use the store procedure.


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.

    Wednesday, January 15, 2014 10:01 AM
    Moderator
  • hello

    But output of stored Procedure is not fixed. In my table around 150 columns and at run time dcide which column i have to show eg 10 or 15 columns.

    for eg :

    It is my actual condition you can check on this link.

    http://social.msdn.microsoft.com/Forums/en-US/afddda03-4a42-4eec-b9f0-6f0f23464caf/call-dynamic-query-in-stored-procedure-from-linq?forum=adodotnetentityframework#d313d33e-abb8-4e10-a839-8366b8dd5869

    How we can do it any solution ??

    Wednesday, January 15, 2014 10:19 AM
  • Okey...

    Since the Entity Framework is based on ADO.NET, we can use way as ADO.NET to pass the T-SQL to database and then return the result. For this, we may need to create a big entity class which has all the possible columns.

    string sqlstring = "your T-SQL";

    db.Database.SqlQuery<T>(sqlstring);

    You can spell the sqlstring as whatever you want.

    Regards.


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.

    Thursday, January 16, 2014 8:33 AM
    Moderator