How do I use 'Where In' in Dapper

Question

I've been trying unsuccessfully now for a while to use an IEnumerable<string> with a WHERE IN clause in Dapper.

In the documentation, it does say that IEnumerable<int> is supported for use in a WHERE IN but I can't even get that to work.

Dapper allow you to pass in IEnumerable<int> and will automatically parameterize your query.

The error message I keep receiving is an Sql syntax error. Incorrect syntax near ','.

I've put together some test code that I hope will demonstrate what I am trying to achieve.

---

string connString = "Server=*.*.*.*;Database=*;User Id=*;Password=*;";  string sqlStringIn = @"SELECT StringText FROM                  (SELECT 1 ID, 'A' StringID, 'This is a test' StringText                 UNION SELECT 2 ID, 'B' StringID, 'Another test' StringText                 UNION SELECT 3 ID, 'C' StringID, 'And another' StringText                 UNION SELECT 4 ID, 'D' StringID, 'and again' StringText                 UNION SELECT 5 ID, 'E' StringID, 'yet again' StringText) data                 WHERE StringId IN (@str)";  string sqlIntegerIn = @"SELECT StringText FROM                  (SELECT 1 ID, 'A' StringID, 'This is a test' StringText                 UNION SELECT 2 ID, 'B' StringID, 'Another test' StringText                 UNION SELECT 3 ID, 'C' StringID, 'And another' StringText                 UNION SELECT 4 ID, 'D' StringID, 'and again' StringText                 UNION SELECT 5 ID, 'E' StringID, 'yet again' StringText) data                 WHERE ID IN (@integer)";   using (SqlConnection conn = new SqlConnection(connString)) {     conn.Open();      List<int> integers = new List<int>{ 1, 2, 3 };     List<string> strings = new List<string> { "A", "B", "C" };      var parameters = new {str = strings, integer = integers };      //fails here     IEnumerable<string> intTest = conn.Query<string>(sqlIntegerIn, parameters, commandType: System.Data.CommandType.Text);      //and here     IEnumerable<string> stringTest = conn.Query<string>(sqlStringIn, parameters, commandType: System.Data.CommandType.Text);  } 

Answer 1

To do what is needed here, dapper needs to alter the SQL on the fly - so it needs to be really sure that it is doing the right thing. The regular valid SQL syntax includes parenthesis:

WHERE StringId IN (@str) 

To disambiguate from this, the voodoo dapper syntax omits the parenthesis:

WHERE StringId IN @str 

If it detects this, it looks for a parameter called str, and expands it, to one of:

WHERE 1=0 -- if no values WHERE StringId = @str -- if exactly one value WHERE StringId IN (@str0, @str1, ...) -- if more than one value 

But short version: remove the parenthesis.