Check null for list parameter in Dapper

Question

Is there a way to check for null when the parameter type is an IEnumerable? I know Dapper will translate the list parameter into a parametrized query so I'm suspecting that's the reason why the list parameter cannot be checked for null but I was wondering if there's a way to achieve that behaviour.

The idea is to do something like this:

select * from Table1 where (@ids IS NULL OR id in @ids)

Right now that query throws SqlException with the message: Must declare the scalar variable "@ids". Incorrect syntax near ')'.

Answer 1

The id in @ids is a pattern that is recognized by dapper and treated as an expansion - so depending on the number of items in your input ids, this could become one of:

(1 = 0) -- empty sequence
(id = @ids_0) -- sequence with 1 element
(id in (@ids_0, @ids_1, ...)) -- sequence with multiple elements

Because of this, after the expansion, there is no @ids parameter / variable - so @ids IS NULL is not going to work. As such, I propose the best way to do this in your case is simply: don't add that part of the tsql. For example:

var sql = new StringBuilder("select * from Table1");
if(ids != null && ids.Any())
{
    sql.Append(" where id in @ids");
}
var data = conn.Query<SomeType>(sb.ToString(), new { ids }).ToList();