Why does Dapper generate different SQL for Postgres with/without a mini-profiler connection

Question

Dapper (1.13 Noobget Package) creates different SQL statements depending on if it's used with a plain ADO.NET database connection or with a decorated mini-profiler database connection.

Example code (tested with Postgresql)

Usings:

using System.Linq;
using Dapper;
using Npgsql;
using NUnit.Framework;
using StackExchange.Profiling;
using StackExchange.Profiling.Data;

Test1 uses a plain ADO.NET connection and fails:

[TestFixture]
public class DapperTests {
  private const string cnnstr = "HOST=...;DATABASE=...;USER ID=...;PASSWORD=...;";

  [Test]
  public void Test1() {
    using (var cnn = new NpgsqlConnection(cnnstr)) {
      cnn.Open();

      // The following line fails:
      cnn.Query<int>("SELECT 1 WHERE 42 IN @Items", new {Items = new[] {41, 42, 43}}).Single();

      // Npgsql.NpgsqlException : ERROR: 42883: operator does not exist: integer = integer[]
    }
  }

Test2 uses a mini-profiler connection wrapped around the ADO.NET connection and succeeds:

  [Test]
  public void Test2() {
    using (var cnn = new NpgsqlConnection(cnnstr))
    using (var profiled = new ProfiledDbConnection(cnn, MiniProfiler.Start())) {
      profiled.Open();

      int result = profiled.Query<int>("SELECT 1 WHERE 42 IN @Items", new {Items = new[] {41, 42, 43}}).Single();

      Assert.AreEqual(1, result);
    }
  }
}

Looking at the generated SQL it becomes clear why Test1 fails:

• SQL of Test1: SELECT 1 WHERE 42 IN ((array[41,42,43])::int4[])
• SQL of Test2: SELECT 1 WHERE 42 IN (((41)),((42)),((43)))

Arrays don't support IN.

Why does dapper generate different SQL when it's used with/without a profiled connection?

Why does it generate an array[...] with a plain connection? Due to dapper's docs it should generate a tuple:

Answer 1

There is a class "FeatureSupport" in Dapper that contains settings for special treatment of arrays. Postgresql connections are marked to support arrays, while other connection types (that includes MiniProfiler ProfiledDbConnections) are marked to not support arrays.

If the connection does not support arrays, Dapper manually creates one parameter for each item in the array (like explained in the docs) - it becomes a tuple in SQL, like: SELECT 1 WHERE 42 IN (41,42,43)

If the connection supports arrays (like Postgres' NpgsqlConnection), array parameters are passed straight to the connection, resulting in something ugly like: SELECT 1 WHERE 42 IN ('{41,42,43}'::int4[]) - which actually fails because IN doesn't support arrays.

Relevant code is in the SqlMapper.PackListParameters method.

Therefore switching between ProfiledDbConnections and NpgsqlConnections causes problems because the generated SQL will be different.

To get rid of the array syntax in Postgres connections, the following code can be used (though it only works at a global level...):

using Dapper;
using Npgsql;

using (var cnn = new NpgsqlConnection())
  FeatureSupport.Get(cnn).Arrays = false;

There does not seem to be a way to enable/disable the array syntax on a per-query or per-parameter level.

PS.: I found an issue for this problem at https://code.google.com/p/dapper-dot-net/issues/detail?id=107&q=postgres