QueryMultiple with CommandType.StoredProcedure fails with exception

[J-ohn]

Hello and thanks for creating Dapper.

I have run in to what I believe is a bug with QueryMultiple and commandType= StoredProcedure:

Given this stored procedure:

  CREATE PROCEDURE Test
                        @Value1 int, 
                        @Value2 int
                    AS
                    BEGIN
                        SET NOCOUNT ON;

                        SELECT @Value1 AS [Value]
                        SELECT @Value2 AS [Value]
                    END

The following code which I expected to work fails with an exception:

"The member Comparer of type System.Collections.Generic.IEqualityComparer`1[[System.String, System.Private.CoreLib, Version=4.0.0.0, Culture=neutral, PublicKeyToken=7cec85d7bea7798e]] cannot be used as a parameter value

   var parameters = new Dictionary<string, object>
            {
                { "Value1", "1" },
                { "Value2", "2" }
            };

            using (var connection = new SqlConnection("Data Source=.;Initial Catalog=DapperIssueDemo;Integrated Security=true"))
            {
                using (var reader = await connection.QueryMultipleAsync(new CommandDefinition("Test", commandType: System.Data.CommandType.StoredProcedure, parameters: parameters)))
                {
                    var values1 = (await reader.ReadAsync<Poco>()).ToList();
                    var values2 = (await reader.ReadAsync<Poco>()).ToList();
        }
       }

Here is a workaround that works:

   IEnumerable<Poco> Read(IDataReader reader)
            {
                var parser = reader.GetRowParser<Poco>();

                while (reader.Read())
                {
                    yield return parser(reader);
                }
            }

            using (var connection = new SqlConnection("Data Source=.;Initial Catalog=DapperIssueDemo;Integrated Security=true"))
            {
                using (var reader = await connection.ExecuteReaderAsync(new CommandDefinition("Test", commandType: System.Data.CommandType.StoredProcedure, parameters: parameters)))
                {

                    var values1 = Read(reader).ToList();
                    var values2 = Read(reader).ToList();
        }
       }

A working console app demonstrating the issue can be found here: https://atcomsa-my.sharepoint.com/:u:/g/personal/giannis_korres_atcom_gr/ERy9rSHa1gZBvXCXsx_hpcsBrw54d3Zrq9LPEFaNJmvOeQ?e=rm79Em

[celluj34]

I am still having this problem with QueryMultipleAsync in versions 2.0.78 and 2.0.90.

Edit: Also, here's the corrected custom Read method:

IEnumerable<Poco> Read(IDataReader reader)
{
    var parser = reader.GetRowParser<Poco>();

    while (reader.Read())
    {
        yield return parser(reader);
    }

    reader.NextResult(); //important if you're reading multiple
}

[alexdawes]

For any lost souls also encountering this error, I just hit it and think I understand why its occurring - or at least, I understand it enough to provide a more solid solution than swapping away from QueryMultipleAsync to ExecuteReaderAsync.

The issue is triggered because the parameters are being passed in as an Dictionary<string, object>. Dapper will use reflection to identify the properties on the parameters, and in this case it is identifying the Comparer property on the dictionary and failing to map this to a SQL type.

I don't understand why this is sometimes happening, as in most cases Dapper seems to accept a dictionary fine and resolve the key values instead of the properties.

However, knowing this is the cause, a good solution is to switch to a DynamicParameters object as follows:

var parameters = new Dictionary<string, object>();
...
var dyParameters = new DynamicParameters();
foreach (var kvp in parameters)
{
    dyParameters.Add(kvp.Key, kvp.Value);
}
using (var reader = await connection.QueryMultipleAsync(sql, dyParameters))
{
    ...
}