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?

Answer 1

try:

const string sql = 
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 });