.Any() with Nullables - Returns False When I Expect True

Question

I have a view model with a nullable int...

public ObjectViewModel (){
    public int? Total
}

... and there are several rows in my DB where the total is null.

Despite that, this always returns false:

bool exists = repo.AllRows() // renamed this for clarity; returns IQueryable
                  .Any(r => r.Total == vm.Total); // I know r.Total and vm.Total
                                                  // are both null

But the following returns true (as expected):

bool exists = repo.All().Any(r => r.Total == null);

Any idea what I am doing wrong here?

From where do you get vm and what value has it? From the results I would expect that it has a value which does not exist in your result set. — Alois Kraus, Apr 25 '11 at 21:26
regarding vm, eventhought you havent stated you are doing this in loop be carefull if you do so: http://blogs.msdn.com/b/ericlippert/archive/2009/11/12/closing-over-the-loop-variable-considered-harmful.aspx — Boris Bucha, Apr 25 '11 at 21:33
What's the predicate in `All`? As far as I know it can't be called without one. — Jonas Elfström, Apr 25 '11 at 21:33
`All` also returns a boolean so I suspect it wasn't meant to be in the example. — Josh, Apr 25 '11 at 21:35
I have renamed All() in the example to be clearer. It's a repo method, not the All() extension. Mea Culpa. — Scott Baker, Apr 25 '11 at 22:01

Stuart · Accepted Answer · 2011-04-25T21:44:00.140

3

Assuming you meant "vm.Total is null" and that All() was a typo...

I think your problem is the way this is translated into SQL:

the first query gets translated as a WHERE clause with r.Total = @param1
the second query gets translated as a WHERE clause using IS NULL

MSDN has a good description on NULL:

A value of NULL indicates the value is unknown. A value of NULL is different from an empty or zero value. No two null values are equal. Comparisons between two null values, or between a NULL and any other value, return unknown because the value of each NULL is unknown.

This means that you cannot use Comparison operators in SQL - and hence also you can't in Linq to sql either.

Some ways around this are:

to test for null in vm.Total before you choose which form of query to use.
to use Object.Equals for the comparison - see this blog post for more info on this - http://www.brentlamborn.com/post/LINQ-to-SQL-Null-check-in-Where-Clause.aspx

edited Apr 25 '11 at 21:44

answered Apr 25 '11 at 21:31

Stuart

66,722
7
114
165

+1, this is surely an example of a mismatch in C# and SQL. I believe you can use the null coalescing operator (??) in C# to have LINQ use ISNULL(foo, 0) instead. As for the `All()` call, not only doesn't he need it but it won't compile. I suspect it was a mistake. – Josh Apr 25 '11 at 21:38
Added link to blog post - http://www.brentlamborn.com/post/LINQ-to-SQL-Null-check-in-Where-Clause.aspx – Stuart Apr 25 '11 at 21:40
The .All() confusion arises from the fact that I have an All() method in my repository that returns the result of `from m in context select m`. I am not referring to the .All() extension method. It compiles just fine. :-) – Scott Baker Apr 25 '11 at 21:53
@ScottSEA doesn't that seem like a bad idea? – Josh Apr 25 '11 at 21:58
@Josh - yep, refactoring as we speak. :-) – Scott Baker Apr 25 '11 at 22:03
This turned out to be the solution to the problem - I didn't realize that `null == null` will always return false. – Scott Baker Apr 25 '11 at 22:11

score 2 · Answer 2 · answered May 25 '11 at 17:55

Josh's answer seems the most accurate to me. Simply use the null coalescing operator :

bool exists = repo.AllRows().Any(r => r.Total ?? 0 == vm.Total ?? 0);

... and you won't have a kind of "WHERE NULL = NULL" anymore, but a "WHERE 0 = 0" which is okay.

score 0 · Answer 3 · answered Apr 25 '11 at 21:32

As Bala R says, if vm is null so you'll not be able to access to Total property and it must throw NullReferenceException.

Your query should be:

bool exists = repo.Any(r => r.Total == null);

exists will be true if there is a record at least with null in Total property.

Bob · Answer 4 · 2011-04-25T21:40:58.223

The Any method returns true if any of the items in your collection meet the condition specified by the lambda. So none of the items in repo have a Total which equals vm.Total however there are items which are null so the second returns true.

To verify, thrown a little debug code in there,

Console.WriteLine("vm.Total=" + vm.Total.ToString());
foreach (var r in repo)
    Console.WriteLine("r.Total=" r.Total == null ? "null" : r.Total.ToString());

And take a look at the items, you should not see r.Total which is equal to vm.Total and you will see at least one null.

score 0 · Answer 5 · answered Apr 25 '11 at 21:36

0

Your code should give an exception but either way you could try:

Any(r => r.Total == vm==null ? null : vm.Total)

answered Apr 25 '11 at 21:36

Jonas Elfström

30,834
6
70
106

I see what you're saying, but don't you need some parenthesis in there? `Any(r => r.Total == (vm==null) ? null: vm.Total)` – Scott Baker Apr 25 '11 at 22:05
I didn't actually run the code but I believe that you don't. Look up the ternary operator or the conditional operator as C# likes to call it. http://msdn.microsoft.com/en-us/library/ty67wk28(v=VS.100).aspx – Jonas Elfström Apr 25 '11 at 22:38

.Any() with Nullables - Returns False When I Expect True

5 Answers5