CODEWITHSUNDEEP
c#sqlpostgresqldappernpgsql
Oliver Weichhold
Oliver Weichhold

Reputation: 10306

Issue with inserting null value array property using Dapper and Npgsql2

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?

Upvotes: 1

Views: 1187

Answers (1)

Marc Gravell
Marc Gravell

Reputation: 1063814

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).

Upvotes: 1

Related Questions