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);

}

Why don't you use Linq ? There is a helper library for Dapper that does the job for you : http://sqlinq.codeplex.com/releases/view/88056 — aybe, Nov 12 '13 at 18:00
@Aybe there's no need to; dapper explicitly handles this situation — Marc Gravell, Nov 12 '13 at 19:43

score 60 · Accepted Answer · answered Nov 12 '13 at 19:41

60

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.

answered Nov 12 '13 at 19:41

Marc Gravell

1,026,079
266
2,566
2,900

@Marc does this not fall over if the list in exceeds a maximum? See error: The incoming request has too many parameters. The server supports a maximum of 2100 – cs0815 Oct 20 '14 at 14:27
2

@csetzkorn if you are using very large sets, a TVP and `INNER JOIN` is probably a much better idea than `IN`; dapper has full support for TVPs. What I don't want to do is hard-code every different vendor's different technical limits into the library – Marc Gravell Oct 20 '14 at 14:35
Thanks TVP sounds good. I suppose I can just include it in my parametised sql? (e.g. http://stackoverflow.com/questions/26466485/generic-parameterised-sql-query-with-dapper) – cs0815 Oct 20 '14 at 14:37
Thanks. I am still not sure how to do it. Can you please have a look at: http://stackoverflow.com/questions/26468710/use-table-valued-parameter-tvp-instead-of-where-in – cs0815 Oct 20 '14 at 16:51
1

@MarcGravell does that only work if there's only one IEnumerable parameter on the passed in object, or can there be other properties too? `var parameters = new { assemblyName, entityIds = listOfIds.ToArray() };` – jcolebrand Sep 16 '15 at 23:03
@jcolebrand it should be fine with arbitrary numbers, as long as they don't exceed the parameter limits of the provider – Marc Gravell Sep 17 '15 at 09:19
@MarcGravell rubber duck debugging last night revealed operator error :-\ feel free to destroy these three comments for me – jcolebrand Sep 17 '15 at 16:24
@MarcGravell I have been using this functionality and it seems very slow. It is usually taking over 600 ms for one simple select. Straight T-SQL takes under 10 ms. Should we use Dapper.DBString in some way? Also, my list is always 10 items or less. – Ben Hoffman Apr 14 '17 at 17:45
@Ben if the database is varchar rather than nvarchar, then yes: DbString. If you use varchar extensively, IIRC we exposed a default setting. – Marc Gravell Apr 14 '17 at 17:59
@MarcGravell That helps. What is the default setting? I know how to use DbString but how is it done with an array? – Ben Hoffman Apr 14 '17 at 18:43
@Ben I'm not at a PC, but I would expect an array of DbString to work... – Marc Gravell Apr 14 '17 at 18:45
Does it works in ExecuteScalar extension too? Using the suggested syntax it gives me the "incorrect syntax" runtime exception. – Simone Aug 25 '17 at 11:04
Worth calling out that the rationale for this behavior is more fully explained @ https://code.google.com/archive/p/dapper-dot-net/issues/114. – Peter Majeed Sep 18 '17 at 21:33

score 3 · Answer 2 · edited Nov 29 '17 at 12:13

I want to add an important note if you are interested in being able to handle an empty list, aka make the IN clause optional. I did this by adding a property to contain the count such as public int InClauseCount => InClauseList?.Length ?? 0;

Then use the count within the sql like this...

Select field1, field2
from Table1
where (some condition)
AND (@InClauseCount = 0 OR field1 IN @InClauseList)

I hope this can help someone out there. I spent a little too long trying to solve this, partially because I'm new to Dapper.

How do I use 'Where In' in Dapper

2 Answers2

Linked