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?

Had same problem just now and I just removed the brackets/parentheses around @zips. Just iike jaxxbo says. — Bjørn Fridal, Nov 19 '15 at 08:43

score 16 · Accepted Answer · edited Mar 21 '23 at 11:44

16

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

edited Mar 21 '23 at 11:44

Kalle

2,282
1
24
30

answered Apr 20 '12 at 14:21

Sam Saffron

128,308
78
326
506

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(query).ToList(); – Marko Nov 28 '12 at 21:40
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 – Marko Nov 28 '12 at 21:49

Dapper and In Condition

1 Answers1