CODEWITHSUNDEEP
c#postgresqldapperwhere-in
Jack
Jack

Reputation: 9252

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}

Upvotes: 5

Views: 3454

Answers (1)

Ali Bahrami
Ali Bahrami

Reputation: 6073

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

Upvotes: 11

Related Questions