Nullable Bit field true or false detection

Question

If I have a nullable bit field (named 'Disabled') and the data is as follows:

ID | Name | Disabled
--------------------
1  | Mine | null
2  | Yours| 1

If I then execute the following Linq To Entities statement no values are returned:

from r in Rates
where r.Disabled != true
select r

But If I execute this Linq To Entities statement:

from r in Rates
where r.Disabled == true
select r

It returns the expected one row with an ID of 2.

I want the first statement to return row 1 where the value of the 'Disabled' field is null.

Am I missing a trick here? Why does the first statement not return the null valued row?

Edit let me rephrase the question... Why isn't it returning the null rows? I know I can put in a simple null check.

try replacing `where r.Disabled != true` with `where (r.Disabled ?? false) != true` This will replace any null found with false for this comparison — Alfie Goodacre, Nov 16 '16 at 16:12
@JuanCarlosOropeza That's most illogical... NULL != true returning false is implying that NULL == true ?!?!?!?!?!? — Paul Zahra, Nov 16 '16 at 16:15
@PaulZahra Is a design thing. `NULL != false` is also false. You can read this [**why-null-never-compares-false**](https://www.xaprb.com/blog/2006/05/18/why-null-never-compares-false-to-anything-in-sql/) That is why they create the `IS` operator for `fieldName IS NULL` — Juan Carlos Oropeza, Nov 16 '16 at 16:18
@PaulZahra `null` is not true or false. `null` has no value, and by definition will never be equal to anything except other `null`s. — Abion47, Nov 16 '16 at 16:21
SQL uses three valued logic - `true`, `false`, `unknown` - `NULL != true` is `unknown`. negating `unknown` returns `unknown` — Martin Smith, Jan 13 '23 at 07:50

Juan Carlos Oropeza · Accepted Answer · 2016-11-16T17:27:22.237

1

from r in Rates
where r.Disabled != true
   || r.Disabled IS NULL
select r

edited Nov 16 '16 at 17:27

answered Nov 16 '16 at 16:12

1

That is invalid code for Linq To Entities... it should read r.Disabled != true || r.Disabled == null – Paul Zahra Nov 16 '16 at 16:38
Taking into account the information you presented me with I will mark this as the answer... thanks! – Paul Zahra Nov 16 '16 at 16:39
By the way... I decided to go with the slightly more logical ... r.Disabled == false || r.Disabled == null – Paul Zahra Nov 16 '16 at 16:41
Yes I was still in SQL mode. Yes second version is more readeable – Juan Carlos Oropeza Nov 16 '16 at 17:28

Vivek Nuna · Answer 2 · 2016-11-16T16:30:23.943

0

As you said, you have nullable bool. You can check for null by HasValue.

bool? nullableBool = null;
if (nullableBool.HasValue == false)
    {
      //Null
    }

If you want to treat false and null as same as "NOT TRUE", then you can use GetValueOrDefault propertry

bool? nullableBool = false;

or bool? nullableBool = false;

In either of the two cases, this if condition will meet.

if (!nullableBool.GetValueOrDefault(false))
    {

    }

You can use these logic in your code.

edited Nov 16 '16 at 16:30

answered Nov 16 '16 at 16:22

Vivek Nuna

Thanks but I need to do it in the query... and if I do i get this error 'NotSupportedException: LINQ to Entities does not recognize the method 'Boolean GetValueOrDefault(Boolean)' method, and this method cannot be translated into a store expression.' – Paul Zahra Nov 16 '16 at 16:34
what about HasValue? – Vivek Nuna Nov 16 '16 at 16:35

2 Answers2