Async Pipelined execution does not work with Microsoft.Data.SqlClient.

[adameasten]

When migrating from System.Data.SqlClient to Microsoft.Data.SqlClient i stumbled on a strange behavior. I'm using IDbConnection.ExecuteAsync(new CommandDefinition(...parameters: IEnumerable<T>, flags: CommandFlags.Pipelined)) on a few places in my code. Works fine with System.Data.SqlClient but fails with Microsoft.Data.SqlClient. The exception i get is:

"System.InvalidOperationException: The method 'EndExecuteNonQuery' cannot be called more the once for the.."

I've looked in to the code, might found the problem, and tried a solution. The current state of the Task management when running async pipline mode with multiple parameters is described in short below. full version here

Foreach param, create a command and =>

var task = cmd.ExecuteNonQueryAsync(command.CancellationToken); 
pending.Enqueue(new AsyncExecState(cmd, task));

When creation of tasks based on multiple params is done =>

while (pending.Count != 0)
{
    var pair = pending.Dequeue();
    using (pair.Command) { /* dispose commands */ }
    total += await pair.Task.ConfigureAwait(false);
}

The exception seems to arise when the pair.Command is disposed and then the pair.Task is awaited.

What seems to fix the problem is to infer the using of the executing command with the corresponding work looking something like e.g:

Command creation based on multiple params =>

async Task<int> ExecuteQueryAsync(DbCommand cmd)
{
   using (cmd)
   {
       return await cmd.ExecuteNonQueryAsync(command.CancellationToken);
   }
}

var task = ExecuteQueryAsync(cmd);
pending.Enqueue(task);

Continuation =>

while (pending.Count != 0)
{
    var task = pending.Dequeue();
    total += await task.ConfigureAwait(false);
}

I'm to novice to get an overview on how this affects the management of the commands, and why it was splitted in the first place. I've tested it locally and the happy path works, but have not tested it extensivly.

[mgravell]

and why it was splitted in the first place.

to avoid the overhead of an extra async state-machine per entry

The exception seems to arise when the pair.Command is disposed and then the pair.Task is awaited.

Then I would propose a simpler fix would be to simply swap the order:

while (pending.Count != 0)
{
    var pair = pending.Dequeue();
    using (pair.Command) // dispose commands
    {
        total += await pair.Task.ConfigureAwait(false);
    }
}

This achieves the same aim, but would allow the same async state to be reused for all of the operations

[adameasten]

aa i see, dissapointed that i didn't see that solution, much better! I'll try it out locally!