CODEWITHSUNDEEP
dappersqlexception
Tim Scott
Tim Scott

Reputation: 15205

Dapper and In Condition

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?

Upvotes: 17

Views: 5249

Answers (1)

Sam Saffron
Sam Saffron

Reputation: 131142

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

Upvotes: 16

Related Questions