none
SQL Query in LINQ RRS feed

  • Question

  • Hi

    I am trying to build a SQL query that works 100% in SQL server in stored procedure for fetching records on dynamic criteria like it will filter the record if parameter value is given, if left Null it will not include it in criteria and will fetch all records like:

    DECLARE @LocationId int;
    DECLARE @IsPaid bit;
    
    SET @IsPaid = NULL;
    --SET @LocationId = 1;
    
    SELECT * FROM [TicketInfo]
    WHERE	((@LocationID IS NOT NULL AND [LocationId] = @LocationID) OR @LocationId IS NULL) 
    AND	((@IsPaid IS NOT NULL AND [IsPaid] = @IsPaid) OR @IsPaid IS NULL)
    

    so if I SET @IsPaid = 1; it will fetch those records which are paid, if I also include SET @LocationId = 1 then it will get "Paid record with location equals 1"

    Problem: I want to make this query in LINQ but that doesn't seem to be working

    return dc.TicketInfos.Where(t =>
    ((locationId != null && t.LocationId == locationId) || t.LocationId == null) &&
    ((issuerId != null && t.IssuerId == issuerId) || t.IssuerId == null) &&
    ((isPaid != null && t.IsPaid == isPaid) || t.IsPaid == null)).ToList();
    
    I have also trimmed this query to one condition "isPaid" but that also isn't working. Any help in this regard will be highly appreciated

     

    Monday, July 25, 2011 2:30 PM

Answers

  • Hi Syed,

    I meant about your local variable (isPaid)

    If IsPaid is "not NULL" then the t.IsPaid == null cannot be true...

    Could you please try this one below?

    return dc.TicketInfos.Where(t =>
    ((locationId != null && t.LocationId == locationId) || t.LocationId == null) &&
    ((issuerId != null && t.IssuerId == issuerId) || t.IssuerId == null) &&
    (isPaid == null || t.IsPaid == isPaid)).ToList();
    



    Regards, Peter

    //If a post answers your question, please click "Mark As Answer".
    //Also if a post seems to be helpful, please click "Mark as Helpful" on that post.
    • Proposed as answer by Crick3t Thursday, July 28, 2011 1:06 PM
    • Marked as answer by Larcolais Gong Thursday, August 4, 2011 7:03 AM
    Monday, July 25, 2011 3:51 PM

All replies

  • Hi Syed,

     

    Is isPaid a bool in your code?

    If yes then it cannot be null, unless you defined it as "bool?".

    If it is a bool and not a bool? then you probably got a warning message at build like this:

    "The result of the expression is always 'true' since a value of type 'bool' is never equal to 'null' of type 'bool?' "

     

    Otherwise I think it should work as you did. I did this way many times before and it always worked.

    If it is not the case then let us know.

     


    Regards, Peter

    //If a post answers your question, please click "Mark As Answer".
    //Also if a post seems to be helpful, please click "Mark as Helpful" on that post.
    Monday, July 25, 2011 2:59 PM
  • Hi Peter!

    Thanks for replying. Yes IsPaid is bool and "not NULL" in generated dbml file and in Database as well. But why it executes perfectly on SQL server end and not by code? 

    By the way I don't get any warning at compile time

    Monday, July 25, 2011 3:26 PM
  • Hi Syed,

    I meant about your local variable (isPaid)

    If IsPaid is "not NULL" then the t.IsPaid == null cannot be true...

    Could you please try this one below?

    return dc.TicketInfos.Where(t =>
    ((locationId != null && t.LocationId == locationId) || t.LocationId == null) &&
    ((issuerId != null && t.IssuerId == issuerId) || t.IssuerId == null) &&
    (isPaid == null || t.IsPaid == isPaid)).ToList();
    



    Regards, Peter

    //If a post answers your question, please click "Mark As Answer".
    //Also if a post seems to be helpful, please click "Mark as Helpful" on that post.
    • Proposed as answer by Crick3t Thursday, July 28, 2011 1:06 PM
    • Marked as answer by Larcolais Gong Thursday, August 4, 2011 7:03 AM
    Monday, July 25, 2011 3:51 PM
  • Any update? Would you mind letting us know how's it going now?

    Best Regards,


    Larcolais Gong[MSFT]
    MSDN Community Support | Feedback to us
    Get or Request Code Sample from Microsoft
    Please remember to mark the replies as answers if they help and unmark them if they provide no help.

    Wednesday, July 27, 2011 8:05 AM
  • Oh sorry, completely forgot to update the thread. It worked actually and it was all my mistake or I overlooked the concept myself.

    Like mentioned in native SQL a condition should be " ((@IsPaid IS NOT NULL AND [IsPaid] = @IsPaid) OR @IsPaid IS NULL "

    and in code I was using:  ((isPaid != null && t.IsPaid == isPaid) || t.IsPaid == null)) 

    Error was in last condition after || where it says t.IsPaid it should had been ((isPaid != null && t.IsPaid == isPaid) || isPaid == null)) (i.e the supplied parameter). 

    Hope its clear now :)

    Wednesday, July 27, 2011 12:31 PM
  • Good to hear.

    You can simplify your conditions and you will get the following. (which is exactly what I suggested before)

     

    (isPaid == null || t.IsPaid == isPaid)
    

    If you have any questions let me know.

     


    Regards, Peter

    //If a post answers your question, please click "Mark As Answer".
    //Also if a post seems to be helpful, please click "Mark as Helpful" on that post.
    Wednesday, July 27, 2011 12:47 PM