Issue with inserting null value array property using Dapper and Npgsql2

Question

I'm currently investigating a problem with executing an insert using Dapper's Execute in conjunction with the Npgsql2 managed provider for PostgreSQL 9.3.

Table Structure (simplified):

CREATE TABLE posts
(
  id SERIAL NOT NULL PRIMARY KEY,
  title TEXT NULL,
  tags TEXT[] NULL,
  created timestamp NOT NULL,
);

Related Entity (simplified):

[Table("posts")]
internal class Post
{
  public int Id { get; set; }
  public string Title { get; set; }
  public string[] Tags { get; set; }
  public DateTime Created { get; set; }
}

Offending Code

using (var ts = CreateTransactionScope())
{
    using (var con = ConnectionFactory.GetConnection())
    {
        var post = new Post();
        post.Created = DateTime.UtcNow;
        post.Title = "Foo bar baz";
        con.Insert(post);

        ts.Complete();
    }
}

The code above utilizes Dapper.Contrib's SqlMapperExtensions which calls Dapper.Execute with the following SQL and param:

SQL:

insert into posts (title, tags, created) values (@Title, @Tags, @Created)

Param:

Id  0  int
Created  {14.07.2010 19:15:51}  System.DateTime
Title  "Foo bar baz"  string
Tags  null  string[]

What happening when the command is executed is that the driver complains about parameter @Tags being referenced in the insert statement but not present in the command's Parameters collection. Dapper simply fails to emit it. This only seems to happen for array type properties. Suggestions?

Marc Gravell · Accepted Answer · 2014-08-21 09:41:48Z

1

This is probably simply a bug; there is special handling on postgres for arrays, which changes the way dapper works in a few places; it looks like one of those places (PackListParameters) only considered the non-null scenario. I've moved some code around; if you are open to pulling the code from github and trying it locally, I'd be very grateful. Hopefully it now works (note: NuGet not yet updated).

answered Aug 21, 2014 at 9:41

Marc Gravell

1.1m273 gold badges2.6k silver badges3k bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Oliver Weichhold Over a year ago

I just ran a rather big batch import against the latest dapper commit and the problem appears to be gone. No other issues either.

Marc Gravell Over a year ago

@OliverWeichhold k, thanks; that'll be in the next NuGet drop

Collectives™ on Stack Overflow

Issue with inserting null value array property using Dapper and Npgsql2

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related