Await using on enumerable

Question

I have a block of code where I want to apply the using statement to each command in the commands enumerable. What is the C# syntax for this?

await using var transaction = await conn.BeginTransactionAsync(cancel);
IEnumerable<DbCommand> commands = BuildSnowflakeCommands(conn, tenantId);

var commandTasks = new List<Task>();
foreach (var command in commands)
{
    command.CommandTimeout = commandTimeout;
    command.Transaction = transaction;
    commandTasks.Add(command.ExecuteNonQueryAsync(cancel));
}

try
{
    await Task.WhenAll(commandTasks);
}
catch (SnowflakeDbException)
{
    await transaction.RollbackAsync(cancel);
    return;
}

await transaction.CommitAsync(cancel);

Edit: Rider / ReSharper seems to think these two are equivalent, or at least I get a prompt to convert the for into a foreach (this is clearly wrong):

for (var i = 0; i < commands.Count; i++)
{
    await using var command = commands[i];
    command.CommandTimeout = commandTimeout;
    command.Transaction = transaction;
    commandTasks.Add(command.ExecuteNonQueryAsync(cancel));
}

and

foreach (var command in commands)
{
    command.CommandTimeout = commandTimeout;
    command.Transaction = transaction;
    commandTasks.Add(command.ExecuteNonQueryAsync(cancel));
}

Edit 2: After some discussion and a few helpful answers, this is what I'm going with:

var transaction = await conn.BeginTransactionAsync(cancel);
var commands = BuildSnowflakeCommands(conn, tenantId);

var commandTasks = commands.Select(async command =>
{
    await using (command)
    {
        command.CommandTimeout = commandTimeout;
        command.Transaction = transaction;
        await command.ExecuteNonQueryAsync(cancel);
    }
});

try
{
    await Task.WhenAll(commandTasks);
    await transaction.CommitAsync(cancel);
}
catch (SnowflakeDbException)
{
    await transaction.RollbackAsync(cancel);
}
finally
{
    await transaction.DisposeAsync();
}

Answer 1

You can use LINQ:

var commandTasks = commands.Select(async command =>
{
    using (command)
    {
        command.CommandTimeout = commandTimeout;
        command.Transaction = transaction;
        await command.ExecuteNonQueryAsync(cancel);
    }
});

The command will then be disposed as soon as it exits scope.

Full code:

await using var transaction = await conn.BeginTransactionAsync(cancel);
IEnumerable<DbCommand> commands = BuildSnowflakeCommands(conn, tenantId);

var commandTasks = commands.Select(async command =>
{
    using (command)
    {
        command.CommandTimeout = commandTimeout;
        command.Transaction = transaction;
        await command.ExecuteNonQueryAsync(cancel);
    }
});

try
{
    await Task.WhenAll(commandTasks);
}
catch (SnowflakeDbException)
{
    await transaction.RollbackAsync(cancel);
    return;
}

await transaction.CommitAsync(cancel);

Definitely don't use the for loop example; the await will cause each command to happen in serial, as the completion of each query must be awaited before the next is initiated.

Answer 2

You can't call Dispose or DisposeAsync a set of IDisposables or IAsyncDisposables using the language syntax alone.

You can iterate each one and call the appropriate method. I would cache all the commands as an array or readonly collection before re-enumerating.

I would avoid disposing in your loop as your JetBrains tooling recommends. The command has to live longer than just that.

Personally, I would do something like this:

var commands = BuildSnowflakeCommands(conn, tenantId).ToArray();
var commandTasks = new List<Task>(commands.Length);

foreach (var command in commands)
{
    command.CommandTimeout = commandTimeout;
    command.Transaction = transaction;
    commandTasks.Add(command.ExecuteNonQueryAsync(cancel));
}

/// later...
foreach (var command in commands)
{
    command.Dispose();
}