.IsNotEqualTo doesn't compare Nulls

Question

This query will only return all records where Active=true and Exempt=false. It should be returning any records also where Active=true and Exempt IS NULL. I guess the .IsNotEqualTo doesn't compare to any records with a null value? Is there a way around this without setting a default?

UserCollection ActiveUsersNotExempt = new UserCollection();
ActiveUsersNotExempt = DB.Select().From<User>()
                .Where(User.Columns.Active).IsEqualTo(true)
                .And(User.Columns.Exempt).IsNotEqualTo(true)
                .ExecuteAsCollection<UserCollection>();`

Answer 1

Add a coalescing operator to your AND clause, causing NULLS to be "false"

.And(User.Columns.Exempt ?? false).IsNotEqualTo(true)

Answer 2

In SQL, operators applied to null do not return true OR false -- instead they return null. (One exception to this rule is the "IS" operator).

That is to say, the expression exempt != true is false when exempt is true, true when exempt is false, and null when exempt is null.

If you want your condition to match when exempt is false OR null, you need to construct a query like:

active = true AND (exempt = false OR exempt IS NULL)

or

active = true AND COALESCE(exempt, false) = false

Hopefully this gives you some insight into what's going on under the hood.

Answer 3

Use AndExpression as follows to get a nested constraint (Exempt is not true or is null):

UserCollection ActiveUsersNotExempt = DB.Select().From<User>()
  .Where(User.Columns.Active).IsEqualTo(true)
  .AndExpression(User.Columns.Exempt).IsNotEqualTo(true)
  .Or(User.Columns.Exempt).IsNull()
  .ExecuteAsCollection<UserCollection>();`