4

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}
Community
  • 1
  • 1
Jack
  • 9,156
  • 4
  • 50
  • 75
  • 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 Dec 08 '16 at 16:59

1 Answers1

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"} });
Ali Bahrami
  • 5,935
  • 3
  • 34
  • 53
  • 3
    You sir, are a saint. My .NET / SQL Server background is showing. This works, thank you! – Jack Dec 08 '16 at 17:50
  • 2
    Thank you for this, I had been fighting with the query for a while trying to figure out why valid sql wasn't working. – Michael Brown May 11 '18 at 05:44
  • And also see https://stackoverflow.com/questions/11730777/postgres-not-in-array for "NOT IN" equivalent. – Jedidja Jan 19 '21 at 11:31