Dapper AddDynamicParams for IN query with IEnumerable parameter in Postgres

Question

To start, I am having the same problem that was discussed, and allegedly fixed about two years ago. See the following question for that issue:

Dapper AddDynamicParams for IN statement with "dynamic" parameter name

The problem that I am experiencing is that when I perform a similar query (SELECT * FROM MyTable WHERE MyId IN @myIds) against my Postgres 9.3 database, I am getting the following exception:

Npgsql.NpgsqlException : ERROR: 42883: operator does not exist: integer = integer[]

My code to perform this query is as follows:

List<MyTable> result;

var query = "SELECT * FROM MyTable WHERE MyId IN @myIds";
var queryParams = new Dictionary<string, object> {
    { "myIds", new [] { 5, 6 } }
};

var dynamicParams = new DynamicParameters(queryParams);
using (var connection = new NpgsqlConnection(connectionString)) {
    result = connection.Query<MyTable>(query, dynamicParams).ToList();
}

return result;

If instead, I put a breakpoint in Dapper's (v1.29) SqlMapper.PackListParameters function on the line if (FeatureSupport.Get(command.Connection).Arrays) and manually move execution to the else portion, then the query runs and returns the expected results.

I noticed that the .Arrays property explicitly calls out Postgres as a supported database, so I am wondering: is this a problem with my code, Dapper code, Dapper configuration, or Postgres configuration? Is there a work-around available without having to modify the Dapper code base? Thanks.

Answer 1

Yes, that looks like a bug related to the handling of array types in postgres; this is specific to postgres, so is unrelated to the "allegedly fixed" post you refer to. I'll be honest with you: I don't know a lot about postgres arrays - that code came from a user contribution, IIRC. I would be very interested to know if it works if you use the native postgres syntax, i.e.

WHERE MyId = ANY(@myIds)

However, I agree it would be nice if we could make the same syntax work on either RDBMS.

Actually, though, it has flagged up another bug in that code that needs fixing (in the FeatureSupport lookup).