.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?

Answer 1

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

Answer 2

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.

Answer 3

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.

Answer 4

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

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

Answer 5

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.