Dapper查询可为空的小数字段不起作用

Question

我有一个表，其中包含一个可以为空的小数(12，4)列。当我像这样查询时：

const string sqlQuery = "SELECT Id From DummyTable WHERE Price = @Price";

var sqlParameter = new { Price = (decimal?)null };

此查询返回0条记录。但是当我将查询更改为：

const string sqlQuery = "SELECT Id From DummyTable WHERE Price IS NULL;

此语法有效。那么，如何查询带参数的可空小数列呢？谢谢。

Answer 1

它不是很性感，但你可以试试这样的东西：

[Test]
public void NullParamTest()
{
    _connection.Execute(@"drop table foo; 
                          create table foo(id int not null,price decimal(12, 4) null)");
    _connection.Execute("insert into foo values(1, null)");
    _connection.Execute("insert into foo values(2, 12.99)");

    const string sql = @"select price from foo 
                        where coalesce(price, -1) = coalesce(@Price, -1)";

    var result = _connection.Query<decimal?>(sql,new { Price = (decimal?)null }).FirstOrDefault();
    Assert.That(result, Is.Null);

    result = _connection.Query<decimal?>(sql,new { Price = 12.99 }).FirstOrDefault();
    Assert.That(result, Is.EqualTo(12.99));
}

假设“价格”不能为负..。