Why Dapper generates another SQL with / without connecting to the mini profiler

Question

Dapper (1.13 Noobget Package) creates various SQL statements depending on whether it is used with a simple ADO.NET database connection or with a mini profiler database connection.

Sample code (verified using Postgresql)

Usings:

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

Test1 uses a normal ADO.NET connection and does not work :

 [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 the mini profiler connection associated with the ADO.NET connection and successfully :

  [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 Test1: SELECT 1 WHERE 42 IN ( (array [41,42,43]) :: int4 [] )
• SQL Test2: SELECT 1 WHERE 42 IN ((((41)), ((42)), ((43)))

Arrays do not support IN.

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

Why does it generate an array [...] with a simple join? Due to dapper docs, it should generate a tuple:

Answer 1

Dapper has a “FeatureSupport” class that contains settings for special handling of arrays. Postgresql connections are marked for array support, while other types of connections ( which include MiniProfiler ProfiledDbConnections ) are marked as non-supporting arrays.

If the connection does not support arrays, Dapper manually creates one parameter for each element of the array (as described in the documents) - it becomes a tuple in SQL, for example: SELECT 1 WHERE 42 IN (41, 42,43)

If the connection supports arrays (for example, Postgres' NpgsqlConnection), the parameters of the array are passed directly to the connection, which leads to something ugly: SELECT 1 WHERE 42 IN ( '{41,42,43}' :: int4 [] ) - which not actually executed because IN does not support arrays.

The corresponding code is in the SqlMapper.PackListParameters method.

Therefore, switching between ProfiledDbConnections and NpgsqlConnections is causing problems because the generated SQL will be different.

To get rid of array syntax in Postgres connections, you can use the following code (although it only works on a global level ...):

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

There seems to be no way to enable / disable array syntax at the level of each query or at the parameter level.

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