Unbuffered QueryAsnyc multi-mapping throws an InvalidOperationException

[mburbea]

using (var conn = new SqlConnection(appToken.ConnectionString))
{
    conn.Open();
    var ret = conn.Query("select a='dog',b=3", (string a, int b) => a + b, splitOn: "b", buffered: false);
    Console.WriteLine(string.Join("", ret));
    ret = conn.QueryAsync("select a='dog',b=3", (string a, int b) => a + b, splitOn: "b", buffered: false).Result;
    Console.WriteLine(string.Join("", ret));
}

The call to Query returns dog3, the QueryAsync throws the following exception:

System.InvalidOperationException was unhandled
  HResult=-2146233079
  Message=Invalid attempt to call FieldCount when reader is closed.
  Source=System.Data
  StackTrace:
       at System.Data.SqlClient.SqlDataReader.get_FieldCount()
       at Dapper.SqlMapper.GetColumnHash(IDataReader reader, Int32 startBound, Int32 length)
       at Dapper.SqlMapper.<MultiMapImpl>d__140`8.MoveNext()
       at System.String.Join(String separator, IEnumerable`1 values)
       at Program.Main(String[] args) in 

The store procedure I am calling takes a while to execute and I'd rather let it be called async before I start streaming data out of the query.

[mburbea]

Figured out the cause.
https://github.com/StackExchange/dapper-dot-net/blob/master/Dapper/SqlMapper.Async.cs#L650
The error is that it returns the enumerable, but the using block closes the connection before enumeration begins.

                using (var reader = await ExecuteReaderWithFlagsFallbackAsync(cmd, wasClosed, CommandBehavior.SequentialAccess | CommandBehavior.SingleResult, command.CancellationToken).ConfigureAwait(false))
                {
                    if (!command.Buffered) wasClosed = false; // handing back open reader; rely on command-behavior
                    var results = MultiMapImpl<TFirst, TSecond, TThird, TFourth, TFifth, TSixth, TSeventh, TReturn>(null, CommandDefinition.ForCallback(command.Parameters), map, splitOn, reader, identity, true);
                    return command.Buffered ? results.ToList() : results;
                }

I'll spin up a PR to fix.

[RodrigoMaf]

Read about and use async/await

Ex.:
using (var conn = new SqlConnection(appToken.ConnectionString))
{
conn.Open();
var ret = conn.Query("select a='dog',b=3", (string a, int b) => a + b, splitOn: "b", buffered: false);
Console.WriteLine(string.Join("", ret));
ret = await conn.QueryAsync("select a='dog',b=3", (string a, int b) => a + b, splitOn: "b", buffered: false).Result;
Console.WriteLine(string.Join("", ret));
}

[0xorial]

Any chance to get this patched?

[kaldas]

Please - Is there any way I can assist in resolving this?

[NickCraver]

There isn't a change to be made here overall - a method using a connection and expected data needs to await the results before closing the connection (at the end of the using). Overall: this is a usage problem, and leaving the reader open won't help...since the connection door will be shut at the end of the using regardless.

I'll cleanup this and the PR, but overall: when reading data, that needs to be completed before severing the connection to the data source.

[bgrainger]

I agree with the OP that this is a Dapper bug, not a usage problem. Here's the source of MultiMapAsync:

Dapper/Dapper/SqlMapper.Async.cs

Lines 912 to 915 in b452fb1

	using var reader = await ExecuteReaderWithFlagsFallbackAsync(cmd, wasClosed, CommandBehavior.SequentialAccess | CommandBehavior.SingleResult, command.CancellationToken).ConfigureAwait(false);
	if (!command.Buffered) wasClosed = false; // handing back open reader; rely on command-behavior
	var results = MultiMapImpl<TFirst, TSecond, TThird, TFourth, TFifth, TSixth, TSeventh, TReturn>(null, CommandDefinition.ForCallback(command.Parameters), map, splitOn, reader, identity, true);
	return command.Buffered ? results.ToList() : results;

On line 912, a DbDataReader is created using a using declaration. On line 915, an IEnumerable<T> from MultiMapImpl is returned. This is an iterator method that's implemented by a state machine. It still has a reference to the DbDataReader passed to the method, but that DbDataReader will be disposed after the return on line 915 is executed.

Thus, the IEnumerable state machine has a reference to a disposed DbDataReader, and reading it will fail.

The solution might be to transfer ownership of the DbDataReader to MultiMapImpl for non-buffered async reads.

[NickCraver]

@bgrainger Oh geez, I misread the example missing the .Result up there - agreed, reopening this one.

[wojtek-viirtue]

Bump. Is there a reason @mburbea 's PR can't be merged? Is our only recourse to use the sync version?