2
votes

Not sure if this a bug in Npgsql or Dapper or I'm doing it wrong.

I'm trying insert an array of records and return the inserted rows to get the inserted ids. The records are passed as a Postgres array parameter

c# class

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

It is mapped to the following table

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

Here's the insertion code

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)) throws the following exception (IRowParser is a Dapper interface)

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

Any ideas what's wrong here?

1
is your rowParser has a value? is your result is a class of rows? - Vijunav Vastivch
rowParser is a IRowParser which is a Dapper interface. result is a List - ubi
result is a list of what? - Vijunav Vastivch
IList<T> result = new List<test>(); - ubi
try to debug this var rowParser if it is a list of test Content. if not then that is why you got an error returns - Vijunav Vastivch

1 Answers

2
votes

Try adding a reader.Read() call.

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
}

Thank, your code was useful for me as I wanted to know how I could use Dapper and PostgreSQL composite types.