代码之家  ›  专栏  ›  技术社区  ›  cmill

C#Where子句不会从可为null的bool返回null值

  •  -1
  • cmill  · 技术社区  · 5 年前

    我有一个可以为null的bool,它在SQL server数据库上包含true/false/null。我试图查询服务器以返回包含false的数据集 FlagForDelete records和另一个返回null 标记禁止删除 记录。我尝试了几种方法,但无法隔离我的目标状况所需的两个数据集。

    有人能给我一个提示,告诉我如何实现这一点吗?非常感谢。

    目标条件

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

    我尝试了下面的代码,但无法使其工作。我得到了所有记录,无论是真/假/空。

    数据集

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

    模型

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

    控制器

    IQueryable<Material> Dataset1 = _context.Material.AsNoTracking().Where(ffd => ffd.FlagForDelete == false);
    IQueryable<Material> Dataset2 = _context.Material.AsNoTracking().Where(ffd => ffd.FlagForDelete == null);
    
    1 回复  |  直到 5 年前
        1
  •  1
  •   Rooster    5 年前
    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
        }
      ]
    }
    
    
    
        2
  •  -1
  •   Jeremy Lakeman    5 年前

    在C#中,一个可以为null的布尔列将映射到 bool? ;

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