Dapper and In Condition

Question

Using Dapper, the following throws Incorrect syntax near ','.

const string sql = 
    "select * from ZipToZipDistance z where z.NoRouteFound = 0" +
    " and z.OriginZip in (@zips) or z.DestZip in (@zips)";
var zipStrings = zips.Select(x => x.ToString()).ToArray();
var result = connection.Query<ZipToZipDistance>(sql, 
    new { zips = zipStrings });

Hmm, the SQL has no commas. It must have something to do with the parameter. OriginZip and DestZip are varchar(10). zips is IEnumerable<int>. I tried using zips as the parameter without the converting to strings. Same error.

Seems very straightforward. What am I doing wrong?

Confine

Remove the brackets from @zips – Skirting

Had same problem just now and I just removed the brackets/parentheses around @zips. Just iike jaxxbo says. – Bashee

Answer 1

try:

const string sql = 
    "select * from ZipToZipDistance z where z.NoRouteFound = 0" +
    " and z.OriginZip in @zips or z.DestZip in @zips";
var zipStrings = zips.Select(x => x.ToString());
var result = connection.Query<ZipToZipDistance>(sql, 
    new { zips = zipStrings });

Pharaoh

Sam the problem I'm running into is that I can only pass 2100 items in the IN clause which seems to be a dapper limitation because when I construct the query using the string concatenation it works just fine. Here is what I mean: Working Code: var query = @"SELECT * FROM QuestionTags WHERE QuestionID IN (" + idsCsvString + ")"; var results = conn.Query<QuestionTag>(query).ToList(); – Radioman

When I do it the way you're suggesting I get the following error: The incoming tabular data stream (TDS) remote procedure call (RPC) protocol stream is incorrect. Too many parameters were provided in this RPC request. The maximum is 2100 – Radioman