none
conditional Contains - error (Nullable Object must have a value) RRS feed

  • Question

  • Hi y'all,

    I have the following LINQ query giving me problems:

    List<int> lNumbers;
    
    //... some code ...
    
    var oResult = from a in dcTest.Records
                    where (lNumbers== null || lNumbers.Contains(a.Number))
                    select a;

    so - if my variable Number is null, then oResults will contain all records, but if my variable Number contains a value, then i want to check if the field Number exists in my list of Numbers (lNumbers)

    Now, why am I getting a "Nullable Object must have a value." error when running my code?

    why is the part right of the && still evaluated even when Number is null?

    how can I get this working properly?

    I appreciate your help.

    Thursday, May 31, 2012 8:56 AM

Answers

  • Because of LINQ not allowing shortcircuiting -- thus causeing the evaluation of both sides of the || operator, you need to coerce INumbers List on the RIGHT side to something that is not null in order to ensure the null exceptions are not thrown ... something like this:

    List<int> INumbers = null;
    //some code
    var q = from a in dcTest.Records
            where INumbers == null || ((INumbers ?? new List<int>()).Contains(a.Number))
            select a;


    Brent Spaulding | Access MVP

    • Marked as answer by SlimSjakie Friday, June 8, 2012 12:06 PM
    Thursday, June 7, 2012 3:12 PM

All replies

  • why is the part right of the && still evaluated even when Number is null?

    You don't have && statement you have ||.

    So "OR" statement evaluates both sides

    And another reason of this error is that a.Number can be null. That's why I think it says "Nullable object ..."


    Please mark as reply if helped.
    Also visit my blog http://msguy.net/


    Thursday, May 31, 2012 9:42 AM
  • I have adapted my code, but it is still giving the same error:

    List<int> lNumbers;
    
    //... some code ...
    
    var oResult = from a in dcTest.Records
                    where (lNumbers == null || (lNumbers != null && lNumbers.Contains(a.Number)))
                    select a;

    So, LINQ is also evaluating the right part of the && even though the left part returns false.

    how can I get this to work ?

    Thursday, May 31, 2012 11:07 AM
  • That is not the problem at all, you are comparing nullable and non nullable types, either change INUmbers to a List<int?> or adjust either mapping or type on your datasource.

    Regards

    Thursday, May 31, 2012 2:36 PM
  • What you need to do is ensure that a.Number is not null.

    LS


    Lloyd Sheen

    Thursday, May 31, 2012 9:59 PM
  • That is not the case: a.Number is not a nullable type, its an int.
    Friday, June 1, 2012 7:54 AM
  • What you need to do is ensure that a.Number is not null.

    LS


    Lloyd Sheen

    it can't be null as it's an int value.
    • Edited by SlimSjakie Friday, June 1, 2012 7:55 AM
    Friday, June 1, 2012 7:55 AM
  • Hi,

    Try with the below code to avoid error

    List<int> lNumbers;

    //... some code ...

    if(lNumbers.Count>0)

    {
    var oResult = from a in dcTest.Records
                   
    where (lNumbers == null || (lNumbers != null && lNumbers.Contains(a.Number)))
                   
    select a;

    }

    else

    {

      var oResult = from a in dcTest.Records
                   
    where (lNumbers == null )
                   
    select a; 

      }


    PS.Shakeer Hussain

    Friday, June 1, 2012 8:24 AM
  • Thanks for the solution, this is a "work-around" which is not what I'm looking for.

    I have used the following work-around:

    List<int> lNumbers;
    
    //... some code ...
    
    bool UseNumbers;
    
    if (lNumbers == null)
    {
      UseNumbers = false;
      lNumbers = new List<int>();
    }
    
    var oResult = from a in dcTest.Records
                  where (!UseNumbers || lNumbers.Contains(a.Number))
                    select a;

    So now the List<int> lNumbers is never null. However the list could be empty (no elements) which has a different meaning in my code than null. With the use of an extra variable UseNumbers I know when to use the List or not.

    I would like to resolve this matter inside the LINQ query, how can I do this?

    • Edited by SlimSjakie Friday, June 1, 2012 11:30 AM
    Friday, June 1, 2012 11:14 AM
  • What you need to do is ensure that a.Number is not null.

    LS


    Lloyd Sheen

    it can't be null as it's an int value.

    Is it mapped as an int ? Because sometimes they get mapped as nullable int instead no matter how it is defined in the db.

    A quick mock up using linq to object and your code in IDEONE shows no problem unless one of the objects is a nullable so if this is a "translation" problem is linq to sql specific, hmmm, I'll try to make one later and post the code

    Friday, June 1, 2012 12:08 PM
  • What I meant is that the value in the database is null.

    LS


    Lloyd Sheen

    Friday, June 1, 2012 12:53 PM
  • a.Number is marked as 'NOT NULL' in the database and is mapped to an int. Therefor, it cannot be null.
    Friday, June 1, 2012 1:03 PM
  • impossible - it's a 'NOT NULL' field meaning it always has a value.
    Friday, June 1, 2012 1:04 PM
  • Hmm, I tested it at home and I got an ArgumentNullException and then it struck me (sometimes I'm really dumb).

    Look there are two things wrong with what you are trying to do here:

    • You are trying to be smart and use short circuit logic, this is a bad idea sometimes, not all languages support it and TSQL (which is what this is translated to at the end) does not guarantee that statements in a where statement will be executed in the given order so there is that.
    • LINQ to SQL checks the whole expresion and that is why you get the error.  This is because the expression needs to be converted to TSQL, in LINQ to Object this works fine.

    In short words, use the work around.

    Regards

    Monday, June 4, 2012 1:02 PM
  • Your theory sounds about right Serguey123.

    How about if I create a custom C# function like so:

    private bool checkIfContains(int Number, List<int> Numbers)
    {
       if (lNumbers == null) return true;
       else return (lNumbers.Contains(Number));
    }

    and I use the following LINQ query:

    var oResult = from a in dcTest.Records
                        where (checkIfContains(a.Number, lNumbers))
                        select a;

    could this be a possible solution?

    Monday, June 4, 2012 1:56 PM
  • Perhaps, you should try it.

    private int checkIfContains(int Number, List<int> Numbers)
    {
       return (lNumbers.Contains(Number));
    }
    
    
    var oResult = from a in dcTest.Records
                        where (Numbers==null||checkIfContains(a.Number, lNumbers))
                        select a;

    This one works for me.

    Regards

    Tuesday, June 5, 2012 12:10 PM
  • Serguey123,

    according to your theory earlier (that LINQ evaluates both sides in the OR expression) it would also evaluate checkIfContains(a.Number, lNumbers) even when lNumbers is null. This would give an error, as in your evaluation function you are not checking if lNumbers is null. In my example, the checking for null is in the evaluation function and wil not give an error if lNumbers is null.

    However, this theory is good but it won't work. When I implement my sample above with the checkIfContains method, i get the following error:

    Method 'Boolean checkIfContains(Int32, System.Collections.Generic.List`1[System.Int32])' has no supported translation to SQL.

    funny enough, i only get the error when Numbers != null, when Numbers == null I do not get the error => implying LINQ to SQL does not check the right part of the OR when the left part is true.

    now i'm really confused.


    • Edited by SlimSjakie Tuesday, June 5, 2012 1:24 PM
    Tuesday, June 5, 2012 1:16 PM
  • OK - this is my REAL code.

    When I run this, i get the error "System.ArgumentNullException: Value cannot be null."  only when CustomerIDs is null:

    var oResult = from k in _dcTest.Complaints
    where (CustomerIDs == null || CustomerIDs.Contains(k.KlantID))
    select k;

    So this implies that the right part of the || is still evaluated, even when the left part is true.

    My workaround is this:

    bool UseCustomers = true;
    
    if (CustomerIDs == null)
    {
        UseCustomers = false;
        CustomerIDs = new List<int>();
    }
    
    var oResult = from k in _dcTest.Complaints
    where (!UseCustomers || CustomerIDs.Contains(k.KlantID))
    select k;

    In this example, the right part is NOT evaluated when the left part is true.

    So: why does LINQ evaluate the right part in my first query but not in my 2nd query?



    • Edited by SlimSjakie Tuesday, June 5, 2012 1:40 PM
    Tuesday, June 5, 2012 1:40 PM
  • Look, the code I posted works fine,  I tested it at home in a VM with VS.

    As to why your samples behaves differently it has to do with how the query is formed and optimized.

    Basically in your first example, the query is not short circuited before it is compiled because it doesn't know the result of the conditional without evaluating it, because it is going to be translated to tsql it evaluates all the expressions in the where statement to build the query.  In the second sample as the first part is a bool, short circuiting occurs before everything else.

    In short words, as I said before, do not trust short circuiting

    Tuesday, June 5, 2012 2:23 PM
  • Well, at my end it doesn't work when the list is unassigned.

    so what is this "short-circuit" exactly - what is the difference of evaluating a boolean value or if a value is null (also boolean value) ?

    Tuesday, June 5, 2012 4:59 PM
  • "what is this "short-circuit" exactly"

    I'll let wikipedia explain it for me.

    http://en.wikipedia.org/wiki/Short-circuit_evaluation

    "What is the difference of evaluating a boolean value or if a value is null (also boolean value) ?"

    Let see:

    • bool flag:  This is a boolean
    • a==b: This is a boolean expression, an expresion that the compiler need to evaluate and will return a boolean
    • private bool Myflag(object a, object b){return a==b;} : This is a function that returns a boolean

    Normally there is no difference in behavior but in linq there is because of how the query string get optimized.  Linq is fine with the first and third and only feeds the result to the query compiler (aka a bool) but it chokes with the second because before parsing it, it checks it and finds the object to be null.  Something like this:

    • boolean||SomethingThatCouldBeNull: hmm, how nice, a boolean, let me shorcircuit it before parsing
    • a==b||SomethingThatCouldBeNull: Ohh, a expression, I better parse it.

    "Well, at my end it doesn't work when the list is unassigned."

    Odd. it worked fine for me... I'll double check later.

    Tuesday, June 5, 2012 5:25 PM
  • Firstly, I really appreciate the time you are spending on explaining this subject to me Serguey.

    so, short-circuiting is getting the value of a boolean or a boolean expression before it is passed to the Linq Optimizer?

    in case 3:

    private bool Myflag(object a, object b){return a==b;} : This is a function that returns a boolean
    this is roughly the same as i did with my function 'CheckIfContains' right?  but this gave me an error as Linq couldn't translate my function into SQL. 

    how do i get the latter to work?


    • Edited by _R34P3R_ Wednesday, June 6, 2012 7:12 AM
    Wednesday, June 6, 2012 7:10 AM
  • p.s. previous post - that was me posting on my tablet... (other account apparently..)
    Wednesday, June 6, 2012 9:19 AM
  • Hi,

    I guess you might be want to read following link which has the similar issue like yours:

    Best Regards,


    Please mark this as answer if it helps with this issue!

    Wednesday, June 6, 2012 11:37 AM
  • I rechecked what I posted and what compiled on my machine and it is different, fixed it now

    private bool checkIfContains(int Number, List<int> Numbers)
    {
       return (lNumbers.Contains(Number));
    }
    
    
    var oResult = from a in dcTest.Records
                        where (Numbers==null||checkIfContains(a.Number, lNumbers))
                        select a;

    Method CheckIfConstains returns a bool, in hindsight pretty obvious because Contains return a bool.  This works for me in VS2010 Ultimate with Resharper and the data is comming from a db in SQL Server 2008 R2.  It is odd that this doesn't work for you.

    " I really appreciate the time you are spending on explaining this subject to me Serguey."

    Thanks, I'm glad to help

    "short-circuiting is getting the value of a boolean or a boolean expression before it is passed to the Linq Optimizer?"

    No, shortcircuiting is the behavior of not fully evaluating a boolean expression because the result is known from a partial evaluation.

    Ex:

    (a||b) -->if a or b is true then the expresion is true

    (a&&B)-->if a or b is false then the expresion is false.

    Read the wikipedia article I linked you to for more info on the subject.

    My explanation is more about when shortcircuiting occurs: in linq to sql when checking for null for it to work is has to happen before the query is transformed.

    Wednesday, June 6, 2012 12:24 PM
  • Hi,

    Try with the below code to avoid error

    List<int> lNumbers;

    //... some code ...

    if(lNumbers.Count>0)

    {
    var oResult = from a in dcTest.Records
                   
    where (lNumbers == null || (lNumbers != null && lNumbers.Contains(a.Number)))
                   
    select a;

    }

    else

    {

      var oResult = from a in dcTest.Records
                   
    where (lNumbers == null )
                   
    select a; 

      }


    PS.Shakeer Hussain

    Shakeer,

    Why use a where clause in your else block?  Also, no need for the test of INumbers == null in your if block.


    Brent Spaulding | Access MVP

    Thursday, June 7, 2012 3:08 AM
  • Why not initialize your List of int ...

    List<int> lNumbers = new List<int>
    //... some code ...
    var oResult = from a in dcTest.Records
                  where (lNumbers.Count == 0 || lNumbers.Contains(a.Number))
                  select a;

     


    Brent Spaulding | Access MVP

    Thursday, June 7, 2012 3:42 AM
  • My explanation is more about when shortcircuiting occurs: in linq to sql when checking for null for it to work is has to happen before the query is transformed.

    So - how can you make sure that the check for null occurs before the query is transformed?

    B. What is the difference between the following 2 queries: (first using || and second using |)

    var oResult = from a in dcTest.Records
                        where (Numbers==null||checkIfContains(a.Number, lNumbers))
                        select a;

    and

    var oResult = from a in dcTest.Records
                        where (Numbers==null | checkIfContains(a.Number, lNumbers))
                        select a;
    (is this even possible?)

    Thursday, June 7, 2012 9:48 AM
  • Because there is a difference in my code between an empty list and null.

    null means the variable has not been initialized so don't even bother checking.

    empty means there are simply on values to check against, still check though.

    (see my workaround code using the additional boolean value to accomplish this)

    Thursday, June 7, 2012 9:49 AM
  • There is effectly no difference, in C#, in testing for .Count == 0 and <object> == null, however, in LINQ, (IIRC) everything come across as parameters, but in the final SQL command text passed to the engine, the parameters could be optimized out -- I am unsure of all the inner workings of LINQ.  So with that, in your LINQ query when INumbers is null, how can you (or LINQ) represent a parameter in T-SQL on the left to have the ability to be a null in its quest to create an SQL statement to send to the database server ...

    DECLARE @p1 <what>
    SELECT * FROM someTable WHERE @p Is Null

    @p must be a scalar value of some sort (not an object), and therefore LINQ cannot build (or evalutate) the T-SQL to pass to the database engine as evidenced by the error being thrown.

    Now with the method I am encouraging (INumbers.Count == 0), LINQ, when INumbers does not have any element, has a scalar value in order to create a properly formed T-SQL statement for SQL Server to consume.

    DECLARE @p int
    SELECT * FROM someTable WHERE @p = 0

    Then with that, LINQ further optimize the T-SQL since @p will have of 0, and form a command text will end up being:

    SELECT * FROM someTable

    The esoteric details may be different, but effectively that is why LINQ cannot use the original LINQ query you created.

    EDIT (2012-06-07 ~11:00 EST):

    While all the above sounds great and has some value, it is not entirely accurate, Serguey123 is on the right track --- shortcircuiting -- because the RIGHT side of the || is what is causing the issue (I thought it was the LEFT).  The following is evidence that my line of thinking was not on the right path ...

    List<int> lNumbers = null;
    //... some code ...
    var oResult = from a in dcTest.Records
                  where lNumbers == null
                  select a;

    Which works just fine ...

    Ultimately, the key is that LINQ needs to evaluate EVERYTHING in the LINQ query before it can go on its merry way to an optimized T-SQL statement.


    Brent Spaulding | Access MVP





    Thursday, June 7, 2012 11:33 AM
  • "So - how can you make sure that the check for null occurs before the query is transformed?"

    There is no way that guarantees it 100% inside the query, the code I posted works for me but it seems it doesn't for you,

    "What is the difference between the following 2 queries: (first using || and second using |)"

    The second query won't work, the | operator is a logical or, basically the same as || operator (the conditional or) but without shortcircuit evaluation.


    Thursday, June 7, 2012 1:01 PM
  • Please read my recent edits ... <dazed>

    Brent Spaulding | Access MVP

    Thursday, June 7, 2012 3:03 PM
  • Because of LINQ not allowing shortcircuiting -- thus causeing the evaluation of both sides of the || operator, you need to coerce INumbers List on the RIGHT side to something that is not null in order to ensure the null exceptions are not thrown ... something like this:

    List<int> INumbers = null;
    //some code
    var q = from a in dcTest.Records
            where INumbers == null || ((INumbers ?? new List<int>()).Contains(a.Number))
            select a;


    Brent Spaulding | Access MVP

    • Marked as answer by SlimSjakie Friday, June 8, 2012 12:06 PM
    Thursday, June 7, 2012 3:12 PM
  • That looks like the kind of solution that I was hoping to find...   and from the results of my test page, it looks like it works for my simple case....

    I have created a "Test Page" to test the different types of trying to get my original Linq Query to work.

    it works on a SQL table called "_Errors" which has an ID (int) and some other fields (not used in my test page).

    The TestPage has the following code in the Page_Load event:

    dcTest dcMAW = new dcTest();
    
    IEnumerable<_Error> lErrors;
    List<int> lNumbers = null;
    
    //--Test 1 --
    try
    {
        lErrors =   from a in dcMAW._Errors
                    where (lNumbers == null || lNumbers.Contains(a.ID))
                    select a;
    
        lblResult1.Text = displayValue(lErrors);
    }
    catch (Exception e1)
    {
        lblResult1.Text = "Exception: " + e1.Message;
    }
    
    //--Test 2 --
    lNumbers = new List<int>() { 1, 2, 3, 4, 5 };
    
    try
    {
        lErrors = from a in dcMAW._Errors
                    where (lNumbers == null || lNumbers.Contains(a.ID))
                    select a;
    
        lblResult2.Text = displayValue(lErrors);
    }
    catch (Exception e2)
    {
        lblResult2.Text = "Exception: " + e2.Message;
    }
    
    //-- Test 3 --
    lNumbers = null;
    
    try
    {
        lErrors =   from a in dcMAW._Errors
                    where (lNumbers == null || checkIfContains(a.ID, lNumbers))
                    select a;
    
        lblResult3.Text = displayValue(lErrors);
    }
    catch (Exception e3)
    {
        lblResult3.Text = "Exception: " + e3.Message;
    }
                
    
    //-- Test 4 --
    lNumbers = new List<int>() { 1, 2, 3, 4, 5 };
    
    try
    {
        lErrors =   from a in dcMAW._Errors
                    where (lNumbers == null || checkIfContains(a.ID, lNumbers))
                    select a;
    
        lblResult4.Text = displayValue(lErrors);
    }
    catch (Exception e4)
    {
        lblResult4.Text = "Exception: " + e4.Message;
    }
    
    //-- Test 5 --
    lNumbers = null;
    
    try
    {
        lErrors =   from a in dcMAW._Errors
                    where ( (lNumbers ?? new List<int>()).Contains(a.ID))
                    select a;
    
        lblResult5.Text = displayValue(lErrors);
    }
    catch (Exception e5)
    {
        lblResult5.Text = "Exception: " + e5.Message;
    }
    
    //-- Test 6 --
    lNumbers = new List<int>() { 1, 2, 3, 4, 5 };
    
    try{
        lErrors =   from a in dcMAW._Errors
                    where ((lNumbers ?? new List<int>()).Contains(a.ID))
                    select a;
    
        lblResult6.Text = displayValue(lErrors);
    }
    catch (Exception e6)
    {
        lblResult6.Text = "Exception: " + e6.Message;
    }

    and the following 2 private functions used:

    private bool checkIfContains(int Number, List<int> lNumbers)
    {
        return (lNumbers.Contains(Number));
    }
    
    private string displayValue(IEnumerable<_Error> oResults)
    {
        return (oResults == null ? "null" : oResults.Count().ToString());
    }

    The results are as following:

    Test 1:	Exception: Value cannot be null. Parameter name: source
    Test 2:	4
    Test 3:	4
    Test 4:	Exception: Method 'Boolean checkIfContains(Int32, System.Collections.Generic.List`1[System.Int32])' has no supported translation to SQL.
    Test 5:	0
    Test 6:	4

    So it looks as if the coalescing (??) operator does the job. 

    http://weblogs.asp.net/scottgu/archive/2007/09/20/the-new-c-null-coalescing-operator-and-using-it-with-linq.aspx

    I will have to try this out yet in my real code........
    • Edited by SlimSjakie Friday, June 8, 2012 7:34 AM
    Friday, June 8, 2012 7:33 AM
  • It works properly in my code, so the coalescing (??) operator does the job for me!

    Final Solution:

    List<int> lNumbers;
    
    //... some code ...
    
    var oResult = from a in dcTest.Records
                    where (lNumbers== null || (lNumbers ?? new List<int>()).lNumbers.Contains(a.Number))
                    select a;


    • Marked as answer by SlimSjakie Friday, June 8, 2012 7:52 AM
    • Unmarked as answer by SlimSjakie Friday, June 8, 2012 12:06 PM
    Friday, June 8, 2012 7:52 AM
  • SlimSjakie,

    Not that I am a point monger or anything, but curious as to why you marked your reply as the answer when you implmented my suggestion?  The way this forum is intended to work is that you mark the reply (or replies) that provided the solution, or a significant portion of it.  It is good, however, that you have posted what you implimented, but when you implement a solution that was suggested, then mark your own post answer is not how the process is supposed to work.  So, in this situation, you can unmark your post as the 'answer' and subsequently mark the post (or posts) that provided the answer/solution you implemented.  In situatiations in which you implement a solution that has not been presented, then by all means the 'netiquette thing' to do is to post your solution and mark it as the answer, then if appropriate, mark other posts as helpful.  Again, please note that I am indicating this to you to let you know the expected process, not because I want the points. I hope there is no offense taken with my commentary as none was intended!

    Glad you now have an acceptable solution! <thumbup>


    Brent Spaulding | Access MVP

    Friday, June 8, 2012 11:42 AM
  • Brent,

    I don't get any points for marking my own post as the solution, but I thought I would state the full solution to the original problem code in this post and mark that as the answer, in case someone has the same problem... They could then easily see what the final solution is...

    You are right though - I will try mark your last post as the solution.

    I have marked all your posts as Helpful b.t.w.

    and............. very many thanks to you and Serguey to leading me to the solution....

    • Edited by SlimSjakie Friday, June 8, 2012 12:07 PM
    Friday, June 8, 2012 12:06 PM
  • >> but I thought I would state the full solution to the original problem code in this post and mark that as the answer, in case someone has the same problem... They could then easily see what the final solution is... <<

    Most definately a GREAT practice to post what you implemented!  Also, I wanted to apologize since I recommended that you unmark your post as an answer.  The reason for the apology is that you do indeed have the "answer" in your post, and since the forum here has the ability to have more than one answer for a thread, I personally would not find it inappropriate to have it marked as well --- but I am not a moderator so opinions may differ, plus, in the end its all minor details.  I, just as you do, like to ensure that future readers can see what the solution was.

    Thank you for managing your thread, it makes reading it a much more rewarding experience!

    Best of luck to you on your project!


    Brent Spaulding | Access MVP

    Friday, June 8, 2012 1:58 PM