c# - Npgsql + Dapper ExecuteReader

Question

不确定这是 Npgsql 或 Dapper 中的错误还是我做错了。

我正在尝试插入一个记录数组并返回插入的行以获取插入的 ID。记录作为 Postgres 数组参数传递

C#类

public class test
{
    public int id { get; set; }
    public string name { get; set; }
}

映射到下表

// id is PK sequence
create table test (
    id int,
    name text
)

这是插入代码

var sql = @"
with t as (
    insert into public.test t (id, name)
    select i.id, i.name
    from unnest(@items) i left outer join t
    on i.name = t.name
    where i.id is null
    returning *
)";

var items = new[]
{
    new NpgsqlParameter("items", new []
    {
        new test() {name = "x"},
        new test() {name = "y"},
    }
};

// Not using Dapper for command exec as it doesn't support PG composite types
// Only using the RowParser
IList<T> result = new List<test>();
using (var connection = new NpgsqlConnection(...))
{
    connection.MapComposite<test>("public.test");
    using (NpgsqlCommand cmd = new NpgsqlCommand())
    {
        cmd.Connection = connection;
        cmd.CommandType = CommandType.Text;
        cmd.CommandText = sql;
        cmd.Parameters.Add(parameters);

        using (var reader = await cmd.ExecuteReaderAsync())
        {
            var hasRows = reader.HasRows; // true
            var rowParser = reader.GetRowParser<test>(typeof(test)); // <== Dapper parser
            result.Add(rowParser(reader)); // <== throws the exception below
        }
    }
}

result.Add(parser(reader))抛出如下异常(IRowParser是一个Dapper接口(interface))

{System.InvalidOperationException: No row is available
   at Npgsql.NpgsqlDataReader.CheckRow()
   at Npgsql.NpgsqlDataReader.GetValue(Int32 ordinal)

知道这里出了什么问题吗？

Answer 1

尝试添加一个 reader.Read() 调用。

using (var reader = await cmd.ExecuteReaderAsync())
{
    reader.Read() // <== ADD THIS LINE
    var hasRows = reader.HasRows; // true
    var rowParser = reader.GetRowParser<test>(typeof(test)); // <== Dapper parser
    result.Add(rowParser(reader)); // <== throws the exception below
}

谢谢，您的代码对我很有用，因为我想知道如何使用 Dapper 和 PostgreSQL 复合类型。