Npgsql.PostgresException: 42809: op ANY/ALL (array) requires array on right side

[babakarj]

Following this issue https://github.com/MiniProfiler/dotnet/issues/319 . The problem is still there.

When changing IDbConnection connection = new NpgsqlConnection(connectionString); to IDbConnection connection = new StackExchange.Profiling.Data.ProfiledDbConnection(new NpgsqlConnection(connectionString), MiniProfiler.Current);

Queries containing Any() fails with following error:

Npgsql.PostgresException
  HResult=0x80004005
  Message=42809: op ANY/ALL (array) requires array on right side
  Source=Npgsql
  StackTrace:
   at Npgsql.NpgsqlConnector.<>c__DisplayClass161_0.<<ReadMessage>g__ReadMessageLong|0>d.MoveNext()
   at System.Runtime.ExceptionServices.ExceptionDispatchInfo.Throw()
   at Npgsql.NpgsqlConnector.<>c__DisplayClass161_0.<<ReadMessage>g__ReadMessageLong|0>d.MoveNext()
   at System.Runtime.ExceptionServices.ExceptionDispatchInfo.Throw()
   at Npgsql.NpgsqlDataReader.<NextResult>d__46.MoveNext()
   at System.Runtime.ExceptionServices.ExceptionDispatchInfo.Throw()
   at System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task)
   at Npgsql.NpgsqlDataReader.NextResult()
   at Npgsql.NpgsqlCommand.<ExecuteDbDataReader>d__100.MoveNext()
   at System.Runtime.ExceptionServices.ExceptionDispatchInfo.Throw()
   at System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task)
   at Npgsql.NpgsqlCommand.ExecuteDbDataReader(CommandBehavior behavior)
   at StackExchange.Profiling.Data.ProfiledDbCommand.ExecuteDbDataReader(CommandBehavior behavior) in C:\projects\dotnet\src\MiniProfiler.Shared\Data\ProfiledDbCommand.cs:line 205
   at System.Data.Common.DbCommand.System.Data.IDbCommand.ExecuteReader(CommandBehavior behavior)
   at Dapper.SqlMapper.ExecuteReaderWithFlagsFallback(IDbCommand cmd, Boolean wasClosed, CommandBehavior behavior) in C:\projects\dapper\Dapper\SqlMapper.cs:line 1064
   at Dapper.SqlMapper.<QueryImpl>d__138`1.MoveNext() in C:\projects\dapper\Dapper\SqlMapper.cs:line 1081
   at System.Collections.Generic.List`1.AddEnumerable(IEnumerable`1 enumerable)
   at System.Linq.Enumerable.ToList[TSource](IEnumerable`1 source)
   at Dapper.SqlMapper.Query[T](IDbConnection cnn, String sql, Object param, IDbTransaction transaction, Boolean buffered, Nullable`1 commandTimeout, Nullable`1 commandType) in C:\projects\dapper\Dapper\SqlMapper.cs:line 723
   ...

Sample query: SELECT * FROM public."tblcars" WHERE "Id"=ANY(@Ids);

private IDbConnection OpenConnection(string connectionString)
{
	IDbConnection connection = new StackExchange.Profiling.Data.ProfiledDbConnection(new NpgsqlConnection(connectionString), MiniProfiler.Current);
	// IDbConnection connection = new NpgsqlConnection(connectionString);
	connection.Open();
	return connection;
}

public IEnumerable<Car> Get(int[] ids)
{
	string query = $"SELECT * FROM public.\"tblCar\" WHERE \"Id\"=ANY(@Ids);";

	using (IDbConnection connection = OpenConnection(this.ConnectionString))
	{
		return connection.Query<Car>(query, new { Ids = ids }).ToList();
	}
}

[znyet]

you can do like this

public IEnumerable<Car> Get(int[] ids)
{
	string query = $"SELECT * FROM public.\"tblCar\" WHERE \"Id\"=ANY(@Ids);";

	using (IDbConnection connection = OpenConnection(this.ConnectionString))
	{
                DynamicParameters dpar = new DynamicParameters();
                dpar.Add("@ids", ids);
		return connection.Query<Car>(query, dpar).ToList();
	}
}

[NinoFloris]

@NickCraver hitting this during testing with miniprofiler as well.

Found out why: Going through https://github.com/StackExchange/Dapper/blob/68eea4ff2629e9cc9e36ed6a996b4eebc5a4f87a/Dapper/SqlMapper.cs#L1970-L1975 Found a call to FeatureSupport.Get(command.Connection).Arrays

Which is defined here https://github.com/StackExchange/Dapper/blob/4e62055c5775aced61b848282ed180de0ab62161/Dapper/FeatureSupport.cs#L19-L24 Where it tries to grab the connection type name. Which obviously in the case of a ProfileDbConnection won't be NpgsqlConnection.

Seems you'd have to choose between:

• Special casing ProfileDbConnection to grab inner connection.
• Making the FeatureSupport class configurable (static mutable type list).
• Making the array/list-expansion behavior configurable.

As everything coming out of ProfileDbConnection is wrapped or might incur a database call I don't see pretty solutions to fingerprinting the NpgsqlConnection via any DbConnection methods without adding knowledge of ProfileDbConnection.

EDIT: Or you have to do changes on the MiniProfiler side to have ProfiledDbCommand.DbConnection return underlying connection instead. That could be quite unexpected and messy too though. https://github.com/MiniProfiler/dotnet/blob/master/src/MiniProfiler.Shared/Data/ProfiledDbCommand.cs#L139-L147

[NinoFloris]

We'll be running with this temporary fix for now

type TransparentProfiledDbConnection(connection: DbConnection, profiler: IDbProfiler) =
    inherit ProfiledDbConnection(connection, profiler)

    override __.CreateDbCommand(original: DbCommand, profiler: IDbProfiler) =
        // Don't pass `this` as the connection, like the original implementation
        // instead pass the underlying `connection` to help Dapper detect we're using npgsql.
        upcast new ProfiledDbCommand(original, connection, profiler)


[...]
services.AddScoped<DbConnection>(fun _ ->
    new TransparentProfiledDbConnection(new NpgsqlConnection(connectionString), MiniProfiler.Current) :> DbConnection)

[NinoFloris]

@mgravell any way we can help on this?

[rwasef1830]

Here's a simple workaround for people suffering from this:

public static class DatabaseFacadeExtensions
{
    public static DbConnection GetProfiledNpgsqlDbConnection(this DatabaseFacade databaseFacade, IDbProfiler dbProfiler)
    {
        // Dapper issue: https://github.com/DapperLib/Dapper/issues/1153
        var connection = databaseFacade.GetDbConnection();
        return new NpgsqlConnection(connection, dbProfiler);
    }

    // Workaround: https://github.com/DapperLib/Dapper/blob/4e62055c5775aced61b848282ed180de0ab62161/Dapper/FeatureSupport.cs#L19-L24
    class NpgsqlConnection : ProfiledDbConnection
    {
        public NpgsqlConnection(DbConnection connection, IDbProfiler profiler) : base(connection, profiler)
        {
        }
    }
}