Search code examples
c#postgresqldapperwhere-in

Dapper WHERE IN string statement with Postgres

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}
Solution

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