SELECT WHERE IN list with Dapper ORM; exception if NULL check

Question

SQL Server with Dapper

List<string> valueList = new List<string> { "20230626", "20230808"};
string sql = "SELECT * FROM MyTable WHERE @Values IS NULL OR Value IN @Values";
var result = connection.Query<dynamic>(sql, new { Values = valueList });

SqlException: an expression of non-boolean type specified in a context where a condition is expected, near ','

If I use this, then it works OK:

List<string> valueList = null

Without NULL check, it works OK

SELECT * FROM MyTable WHERE Value IN @Values

1. Is Dapper rewriting the query when using IN parameter?
2. Is there a workaround that does not use dynamic SQL or table-valued parameters or similar?

Answer 1

1. Is Dapper rewriting the query when using IN parameter?

Yes that's exactly what it does. This is one of the reasons you should tend to avoid this if you have a large dataset: the maximum number of parameters in SQL Server is 2100, and you would hit performance problems well before that.

2. Is there a workaround that does not use dynamic SQL or Table Valued params or similar?

A Table-Valued Parameter would be ideal. These answers shows how to use it, it's pretty simple.

Otherwise, you can do the following:

var valueList = new List<string> { "20230626", "20230808"};
string sql = @"
SELECT *
FROM MyTable
WHERE @ValuesEmpty = 1
   OR Value IN @Values;
";

using var result = connection.Query<dynamic>(sql, new { Values = valueList, ValuesEmpty = valueList.Count == 0 });

Having said that, it's probably better to build up the query dynamically, in order to get the best query plan. Note that parameters are still used, do not inject actual data into the query.

This solution can also be used with a TVP.

var valueList = new List<string> { "20230626", "20230808"};
string sql = @"
SELECT *
FROM MyTable";

if (valueList.Count > 0)
    sql += "
WHERE Value IN @Values;
";

using var result = connection.Query<dynamic>(sql, new { Values = valueList });