How to select null values with LINQ to SQL and DbLinq?

Question

When I

bool? isApproved = null;
db.Table.Where(item => item.IsApproved == isApproved).Count();

the last line value is 0. But when I

db.Table.Where(item => item.IsApproved == null).Count();

the value is correct.

I'm using SQLite, DbLinq and DbMetal.

Answer 1

I have seen it done like this:

 db.Table.Where(
            item => item.IsApproved.HasValue == isApproved.HasValue && 
            (!item.IsApproved.HasValue || item.IsApproved.Value==isApproved.Value ) 
 ).Count();

Answer 2

Well, I had this problem before, I remember that the problem is in converting the LINQ query to a SQL statement.

The second expression has an equal in SQL that: Where IsAproved is null

but the first expression does not because it is a comparision between a value in the database with a C# nullable variable.

To solve it, I would suggest to try:

db.Table.Where(item => isApproved != null ? item.IsApproved == isApproved.Value 
                                          : item.IsApproved == null).Count();

Answer 3

See this post

You should use

db.Table.Where(item => item.IsApproved.Equals(isApproved)).Count();

Then you should contact Microsoft and let them know how terrible this behavior is.

Answer 4

I don't know about the performance hit, but it works

bool? isApproved = null;
db.Table.Where(item => item.IsApproved == isApproved || 
                     !(item.IsApproved.HasValue || isApproved.HasValue))
    .Count();

Answer 5

Try :

db.Table.Where(item => item.IsApproved == isApproved.Value).Count();