Sava B. Sava B. - 1 year ago 232
C# Question

"WHERE x IN y" clause with dapper and postgresql throwing 42601: syntax error at or near \"$1\"

I have an array of strings, and I'd like to have a query containing an IN clause, like:

"... WHERE IN ('foo', 'bar', 'baz')..>"

Here's the final bit of my query, which contains a "where X in Y" clause:

left join genre_tag_band_join tj on = tj.band_id or = tj.band_id
left join genre_tags t on tj.genre_tag_id =
inner join venues v on e.venue_id =

where IN @tagsParam...

I make a Dapper call like this

var shows = con.Query<Event, Band, Band, GenreTag, Venue, Event>(query, (e, hb, ob, gt, v) =>
Event show;
return e;
new { tagsParam = tagsArr}).AsQueryable();

where tagsArr is a string[].

I get exception:

{"42601: syntax error at or near \"$1\""}

Answer Source

In PostgreSQL, you can't use IN to check whether a value is inside an array, you have to use the following PostgreSQL-specific syntax: where = ANY (@tagsParam). See the section 8.15.5 in the PostgreSQL docs.