在Postgres中使用IEnumerable参数进行IN查询的Dapper AddDynamicParams [英] Dapper AddDynamicParams for IN query with IEnumerable parameter in Postgres

问题描述

首先，我遇到的问题已经讨论过，据称大约两年前已解决。有关该问题，请参见以下问题：

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

我遇到的问题是，当我执行类似的查询时（ SELECT * FROM MyTable WHERE MyId IN @ myIds ）对我的Postgres 9.3数据库，出现以下异常：

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[]

我执行此查询的代码是

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;

如果相反，我在Dapper的（v1.29）SqlMapper.PackListParameters函数中放置了一个断点 if（FeatureSupport.Get（command.Connection）.Arrays）并手动将执行移至else部分，然后查询运行并返回预期结果。

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.

我注意到 .Arrays 属性明确将Postgres称为受支持的数据库，所以我想知道：这是我的问题吗？代码，Dapper代码，Dapper配置还是Postgres配置？是否有解决方法，而无需修改Dapper代码库？谢谢。

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.

推荐答案

是的，它看起来像一个与postgres中处理数组类型有关的错误；这是特定于postgres的，因此与您引用的据说固定的帖子无关。我对你说老实话：我对postgres数组了解不多-该代码来自用户IIRC。如果您使用本机postgres语法，我将非常感兴趣，如果它有效，即

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)

但是，我同意，如果我们可以在两个RDBMS上使用相同的语法，那就太好了。

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

实际上，它已经在该代码中标记了另一个需要修复的错误（在 FeatureSupport 查找中）

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

这篇关于在Postgres中使用IEnumerable参数进行IN查询的Dapper AddDynamicParams的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！