Dapper WHERE IN string statement with Postgres

Question

I've seen Dapper WHERE IN statement with ODBC

But I'm not sure Dapper supports WHERE IN ("String1", "String2") syntax for Postgres. Is this supported? I tried digging through the code but I really don't have the time at the moment. So far I've only seen examples of integers.

Example:

_connection.QueryAsync<Lookup>("select * from lookup where lower(discriminator) in @types", new { types = new[] {"Prefix", "Suffix"} });

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

Statement:

{select * from lookup where lower(discriminator) in $1}

Not sure about Postgres, but in TSql you have to put the parenthesis around the paramter like in (@types) because dapper just replaces it with a comma separated list of parameters names that it generates and sets to the values in the collection you pass to it. — juharr
– juharr, Commented Dec 8, 2016 at 16:59

Ali Bahrami · Accepted Answer · 2016-12-08 17:33:09Z

11

If I'm not wrong IN operator in Postgres won't support arrays as parameters. Instead of IN try ANY operator like below:

var query = "SELECT * FROM lookup WHERE LOWER(discriminator) = ANY(@types)";

_connection.QueryAsync<Lookup>(query, new { types = new[] {"Prefix", "Suffix"} });

answered Dec 8, 2016 at 17:33

Ali Bahrami

6,0833 gold badges36 silver badges53 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Jack Over a year ago

You sir, are a saint. My .NET / SQL Server background is showing. This works, thank you!

Michael Brown Over a year ago

Thank you for this, I had been fighting with the query for a while trying to figure out why valid sql wasn't working.

Jedidja Over a year ago

And also see stackoverflow.com/questions/11730777/postgres-not-in-array for "NOT IN" equivalent.

Collectives™ on Stack Overflow

Dapper WHERE IN string statement with Postgres

1 Answer 1

3 Comments

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Linked

Related