C# Where clause won't return the null values from a nullable bool

Question

I have a nullable bool which contains true / false / null on a SQL server database. I am trying to query the server to return a dataset which contains false FlagForDelete records and another to return null FlagForDelete records. I've tried several things and cannot isolate the two data sets I need for my target condition.

Can someone provide a hint on how I can accomplish this? Thanks very much.

Target condition

Dataset1 to contain only false records.  
Dataset2 to contain only null records.

I've tried the below code and can't get it to work. I get all records regardless of true/false/null.

Dataset

MaterialID  |   StockQty    |   FlagForDelete
----------------------------------------------
MAT1        |   10          |   true
MAT2        |   20          |   false
MAT3        |   30          |   null

Model

public class Material
{
    public int MaterialID { get; set; } 
    public decimal? StockQty { get; set; }      
    public bool? FlagForDelete { get; set; }     
}

Controller

IQueryable<Material> Dataset1 = _context.Material.AsNoTracking().Where(ffd => ffd.FlagForDelete == false);
IQueryable<Material> Dataset2 = _context.Material.AsNoTracking().Where(ffd => ffd.FlagForDelete == null);

Use this to check the DB field for NULL, `ffd.FlagForDelete == DBNull.Value` ... [DBNull.Value vs. null](https://stackoverflow.com/questions/4958379/what-is-the-difference-between-null-and-system-dbnull-value) — quaabaam, Oct 22 '20 at 23:47
`I've tried the below code and can't get it to work. I get all records regardless of true/false/null.` So _both_ of your `controller` lines of code return all records? — mjwills, Oct 23 '20 at 00:44
@quaabaam That will not even compile as you cannot compare a `bool?` to a `DBNull`. You'd use `DBNull` when dealing with ADO.Net, not with Linq. — juharr, Oct 23 '20 at 01:26

score 1 · Accepted Answer · answered Oct 23 '20 at 05:08

Reproduced this with SQL server.
Added your Material class. Correction to you keys were made.
The HasValue as stated was fine. Also Convert.ToBoolean helped with translation. 
Some options for various results have been provided.
 

       [HttpGet]  
       public IActionResult GetNulls()
       {
           List<Material> mats = new List<Material>
           {
               new Material { MaterialID = 1, StockQty = 10, FlagForDelete = true },
               new Material { MaterialID = 2, StockQty = 20, FlagForDelete = false },
               new Material { MaterialID = 3, StockQty = 30 }
           };
           string Response = "SUCCESS:";
           try
           {
               List<Material> NotFalse = mats.Where(m => m.FlagForDelete != Convert.ToBoolean(false)).ToList();
               List<Material> NotTrue = mats.Where(m => m.FlagForDelete != Convert.ToBoolean(true)).ToList();
               List<Material> NotEither = mats.Where(m => m.FlagForDelete != Convert.ToBoolean(true) &&  m.FlagForDelete != Convert.ToBoolean(false)).ToList();
               List<Material> Lakemann = mats.Where(m => !m.FlagForDelete.HasValue).ToList();
               var combine = new { NotFalse, NotTrue, NotEither , Lakemann };
               var options = new JsonSerializerOptions { WriteIndented = true };
               string json = JsonSerializer.Serialize(combine, options);
               return Ok(json);
           }
           catch (Exception ex)
           {Response = "ERROR: **" + ex.Message;}
           finally
           {};
           return NoContent();
       }

JSON
{
  "NotFalse": [
    {
      "MaterialID": 1,
      "StockQty": 10,
      "FlagForDelete": true
    },
    {
      "MaterialID": 3,
      "StockQty": 30,
      "FlagForDelete": null
    }
  ],
  "NotTrue": [
    {
      "MaterialID": 2,
      "StockQty": 20,
      "FlagForDelete": false
    },
    {
      "MaterialID": 3,
      "StockQty": 30,
      "FlagForDelete": null
    }
  ],
  "NotEither": [
    {
      "MaterialID": 3,
      "StockQty": 30,
      "FlagForDelete": null
    }
  ],
  "Lakemann": [
    {
      "MaterialID": 3,
      "StockQty": 30,
      "FlagForDelete": null
    }
  ]
}

The conversion to bool is very helpful with those using SQL back ends. The HasVAlue is perfect for those looking for only null. Thank you @Rooster Man. — cmill, Oct 23 '20 at 15:11

score -1 · Answer 2 · answered Oct 22 '20 at 23:53

-1

In C# a boolean column that can been null would map to a bool?;

...Where(ffd => ffd.FlagForDelete == false);
...Where(ffd => !ffd.FlagForDelete.HasValue);

answered Oct 22 '20 at 23:53

Jeremy Lakeman

9,515
25
29

C# Where clause won't return the null values from a nullable bool

2 Answers2