Dapper AddDynamicParams for IN query with IEnumerable parameter in Postgres
For starters, I have the same problem that was discussed and supposedly fixed about two years ago. See the next question on this matter:
Dapper AddDynamicParams for IN statement with "dynamic" parameter name
The problem I'm running into is that when I execute a similar query ( SELECT * FROM MyTable WHERE MyId IN @myIds
) against my Postgres 9.3 database, I get the following exception:
Npgsql.NpgsqlException : ERROR: 42883: operator does not exist: integer = integer[]
My code for making this request looks like this:
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 (v1.29), the SqlMapper.PackListParameters function on the line if (FeatureSupport.Get(command.Connection).Arrays)
and manually move the execution to the else part, then the query is executed and returns the expected results.
I noticed that the property is .Arrays
explicitly calling Postgres as a maintained database, so I'm wondering: is this a problem with my code, Dapper code, Dapper config, or Postgres config? Can I access work without changing the Dapper codebase? Thank.
source to share
Yes, it looks like a bug with postgres array type handling; this is postgres specific, so it is not related to the "supposedly fixed" post you are linking to. I'll be honest with you: I don't know very much about postgres arrays - this code came from user input, IIRC. I would be very interested to know if it works if you are using the native postgres syntax, i.e.
WHERE MyId = ANY(@myIds)
However, I agree, it would be nice if we could work with the same syntax on an RDBMS.
In fact, however, it is marked with another bug in this code that needs to be fixed (searched FeatureSupport
).
source to share