Npgsql + Dapper ExecuteReader

Question

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

我尝试插入一个记录数组，并返回插入的行以获取插入的ids。记录将作为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接口)

{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复合类型。