CODEWITHSUNDEEP
c#.netlinq-to-sqlsqlitedblinq
Jader Dias
Jader Dias

Reputation: 90475

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

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.

Upvotes: 4

Views: 3428

Answers (5)

Jader Dias
Jader Dias

Reputation: 90475

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();

Upvotes: 0

BlueRaja - Danny Pflughoeft
BlueRaja - Danny Pflughoeft

Reputation: 86023

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.

Upvotes: 1

Homam
Homam

Reputation: 23861

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();

Upvotes: 1

Alaeddin Hussein
Alaeddin Hussein

Reputation: 756

Try :

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

Upvotes: -1

Nix
Nix

Reputation: 58542

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();

Upvotes: 2

Related Questions