问题:传递多维Npgsql参数?

我在 asp.net 核心项目中有以下工作 c# 代码:

... /* words is of type List<string> */
var query = $"SELECT * FROM a_table WHERE word = ANY(@words) AND token = 'some string'";
using (var connection = new NpgsqlConnection(ConnectionString))
        {
            connection.Open();
            var cmd = new NpgsqlCommand(query, connection);
            cmd.Parameters.Add("@words", NpgsqlDbType.Array | NpgsqlDbType.Text).Value = words;
            var reader = cmd.ExecuteReader();

            while (reader.Read())
            {
                ...

这行得通。我正在检查当令牌是精确的something else时单词的结果是否被缓存。

当words中的每个单词的token不同时,问题就开始了。我想检查数据库是否有任何匹配项。当我直接在数据库中运行以下 SQL 时,它可以工作:

SELECT * FROM a_table WHERE (word, token) IN (('able', 'something else'), ('pizza', 'something else entirely'))

我现在想像这样在 c# 中使用它:

... /* words was a List<> of a custom type with two string auto properties, but I changed it to List<List<string>> when that didn't work. Didn't help much. */
var query = $"SELECT * FROM a_table WHERE (word, token) IN @words";
using (var connection = new NpgsqlConnection(ConnectionString))
        {
            connection.Open();
            var cmd = new NpgsqlCommand(query, connection);
            cmd.Parameters.Add("@words", NpgsqlDbType.Array | NpgsqlDbType.Array | NpgsqlDbType.Text).Value = words;
            var reader = cmd.ExecuteReader();

            while (reader.Read())
            {
                ...

但我猜 npgsql 无法处理 - 没有类型是字符串数组的数组?

Microsoft.AspNetCore.Server.Kestrel[13] 连接 ID“0HLFDVA1DP743”,请求 ID“0HLFDVA1DP743:00000002”:应用程序引发了未处理的异常。 System.Exception:尝试写入数组时,其中一个元素验证失败。您可能正在尝试在非泛型 IList 中混合类型,或编写锯齿状数组。 ---> System.InvalidCastException: Can't write CLR type System.Collections.Generic.List`1[System.String] with handler type TextHandler at lambda_method(Closure, NpgsqlTypeHandler, Object, NpgsqlLengthCache&, NpgsqlParameter) ...

是否有传入多维数组的正确方法?还是我必须自己构建它,并失去注入保护?我认为参数应该使整个事情变得更简单!请注意,我也不知道单词列表中有多少项目。

解答

但我猜 npgsql 无法处理 - 没有类型是字符串数组的数组吗?

PostgreSQL 支持多维数组 (int[,]),但不支持锯齿数组 (int[][])。

有没有正确的方法传入多维数组?

是的,这是一个示例:

[Test, Description("Roundtrips a two-dimensional array of ints")]
public void TwoDimensionalInts()
{
    using (var conn = OpenConnection())
    using (var cmd = new NpgsqlCommand("SELECT @p1, @p2", conn))
    {
        var expected = new[,] { { 1, 2, 3 }, { 7, 8, 9 } };
        var p1 = new NpgsqlParameter("p1", NpgsqlDbType.Array | NpgsqlDbType.Integer);
        var p2 = new NpgsqlParameter { ParameterName = "p2", Value = expected };
        cmd.Parameters.Add(p1);
        cmd.Parameters.Add(p2);
        p1.Value = expected;
        var reader = cmd.ExecuteReader();
        reader.Read();
        Assert.That(reader.GetValue(0), Is.EqualTo(expected));
        Assert.That(reader.GetProviderSpecificValue(0), Is.EqualTo(expected));
        Assert.That(reader.GetFieldValue<int[,]>(0), Is.EqualTo(expected));
    }
}

当我直接在数据库中运行以下 SQL 时,它可以工作:

选择 *

从表中

WHERE (word, token) IN (('able', 'something else'), ('pizza', 'something else fully'))

这确实有效......但它不涉及任何数组。您正在搜索行集合中的一行:

postgres=# SELECT ('able', 'something else');

           row
-------------------------
 (able,"something else")
(1 row)


postgres=# SELECT (0, 1) IN (u.t) FROM (SELECT unnest(ARRAY[(0, 1), (2, 3)]) AS t) AS u;

 ?column?
----------
 t
(1 row)