How to create generic method of Dapper QueryMultipleAsync?

Question

I am trying to create a generic method to get multiple data sets using Dapper. I will specify the list of expected data types and the method will give me the list of objects of the specific data type.

public async Task<List<object>> GetMultipleAsync<U>(string storedProcedure, U parameters, List<Type> types)
{
    var result = new List<object>();
    try
    {
        using (var multi = await _connection.QueryMultipleAsync(storedProcedure, parameters, commandType: CommandType.StoredProcedure, transaction: _transaction))
        {
            foreach (var type in types)
            {
                if (type.IsGenericType && type.GetGenericTypeDefinition() == typeof(List<>))
                {
                    // Handle list types
                    var elementType = type.GetGenericArguments()[0];
                    var method = typeof(SqlMapper.GridReader)
                        .GetMethods()
                        .FirstOrDefault(m => m.Name == nameof(SqlMapper.GridReader.ReadAsync) &&
                                             m.IsGenericMethod);

                    var genericMethod = method.MakeGenericMethod(elementType);
                    var task = (Task)genericMethod.Invoke(multi, null);
                    await task.ConfigureAwait(false);

                    var resultProperty = task.GetType().GetProperty("Result");
                    var data = resultProperty.GetValue(task);

                    result.Add(data);
                }
                else
                {
                    // Handle single value types
                    var method = typeof(SqlMapper.GridReader)
                        .GetMethods()
                        .FirstOrDefault(m => m.Name == nameof(SqlMapper.GridReader.ReadSingleAsync) &&
                                             m.IsGenericMethod);


                    var genericMethod = method.MakeGenericMethod(type);
                    var task = (Task)genericMethod.Invoke(multi, null);
                    await task.ConfigureAwait(false);

                    var resultProperty = task.GetType().GetProperty("Result");
                    var data = resultProperty.GetValue(task);

                    result.Add(data);
                }
            }
        }
    }
    catch (Exception ex)
    { // Handle exception;
    }
    return result;
}

I will send a data type list of expected type like

var types = new List<Type>
{
    typeof(List<ModelA>),
    typeof(List<ModelB>),
    typeof(long)
};

var resultMultiple = await _dataAccess.GetMultipleAsync<DynamicParameters>(storedProcedure, parameters, types);

and the output should be

var ListA = resultMultiple[0] as List<ModelA>;
var ListB = resultMultiple[1] as List<ModelB>;
var val = (long)resultMultiple[2];

this method throws error Parameter count mismatch.

Answer 1

You are massively over-complicating this. Dapper already allows you to pass a Type object to construct it dynamically, you don't need to do this yourself.

Your only issue is around constrcuting List<T> as Dapper returns an IEnumerable<object>, so you need to dynamically call Cast<T>().ToList<T>().

public async Task<List<object>> GetMultipleAsync<U>(string storedProcedure, U parameters, List<Type> types)
{
    var result = new List<object>();
    try
    {
        using var multi = await _connection.QueryMultipleAsync(storedProcedure, parameters, commandType: CommandType.StoredProcedure, transaction: _transaction);
        foreach (var type in types)
        {
            if (type.IsGenericType && type.GetGenericTypeDefinition() == typeof(List<>))
            {
                // Handle list types
                var elementType = type.GetGenericArguments()[0];
                var data = await multi.ReadAsync(elementType);
                var casted = typeof(Enumerable)
                    .GetMethod(nameof(Enumerable.Cast))
                    .MakeGenericMethod(new[]{ elementType })
                    .Invoke(null, new object[] {data});
                var list = typeof(Enumerable)
                    .GetMethod(nameof(Enumerable.ToList))
                    .MakeGenericMethod(new[]{ elementType })
                    .Invoke(null, new object[] {casted});
                result.Add(list);
            }
            else
            {
                // Handle single value types
                var data = await multi.ReadFirstOrDefaultAsync(type);
                result.Add(data);
            }
        }
    }
    catch (Exception ex)
    { // Handle exception;
    }
    return result;
}

I must say that holding a connection and transaction in a field is a bit of a code smell, and should only be done on some kind of Unit-Of-Work object which is properly disposed (and in turn disposes its connection and transaction). Do not cache these over the lifetime of the application, or any length of time. They are designed to be created and disposed frequently, preventing locking issues on the database.