How can I correctly use NpgsqlTransaction inside a IAsyncEnumerable emitter function?

Question

I don't need to catch the exception, but I do need to Rollback if there is an exception:

public async IAsyncEnumerable<Item> Select()
{
    var t = await con.BeginTransactionAsync(token);
    try {
        var batchOfItems = new List<Item>(); //Buffer, so the one connection can be used while enumerating items
        using (var reader = await com.ExecuteReaderAsync(SQL, token)) 
        {
            while (await reader.ReadAsync(token))
            {
                var M = await Materializer(reader, token);
                batchOfItems.Add(M);
            }
        }

        foreach (var item in batchOfItems)
        {
            yield return item;
        }

        await t.CommitAsync();
    }
    catch
    {
        await t.RollbackAsync();
    }
    finally
    {
        await t.DisposeAsync();
    }
}

(This code is a simplified version of what I am doing, for illustration purposes)

This fails with the message:

cannot yield a value in the body of a try block with a catch clause

---

This is similar to Yield return from a try/catch block, but this has novel context:

• "IAsyncEnumerable" which is relatively new.
• Postgresql (for which the answer uses an internal property)
• This question has a better title, explicitly referring to "Transaction" context. Other contexts with the same error message won't have the same answer.

This is not the same as Why can't yield return appear inside a try block with a catch?. In my case, the context is more specific: I need the catch block to Rollback, not to do anything else. Also, as you can see, I already know the answer and created this as a Q&A combo. As you can see from the answer, that answer isn't relevant to Why can't yield return appear inside a try block with a catch?

Answer 1

You can move the Rollback to the finally block if you can check whether or not the transaction was committed, which you can do using IsCompleted

public async IAsyncEnumerable<Item> Select()
{
    var t = await con.BeginTransactionAsync(token);
    try {
        var batchOfItems = new List<Item>(); //Buffer, so the one connection can be used while enumerating items
        async using (var reader = await com.ExecuteReaderAsync(SQL, token)) 
        {
            while (await reader.ReadAsync(token))
            {
                var M = await Materializer(reader, token);
                batchOfItems.Add(M);
            }
        }

        foreach (var item in batchOfItems)
        {
            yield return item;
        }

        await t.CommitAsync();
    }
    finally
    {
        if (t.IsCompleted == false) //Implemented on NpgsqlTransaction, but not DbTransaction
            await t.RollbackAsync();
        await t.DisposeAsync();
    }
}

Note: The catch block has been removed, and the finally block has two lines added to the start.

This same approach can also work on other DbTransaction implementations that don't have IsCompleted

see https://stackoverflow.com/a/7245193/887092

Answer 2

DbTransaction is considered the best way to manage transactions on SqlConnections, but TransactionScope is also valid, and might help others in related scenarios

public async IAsyncEnumerable<Item> Select()
{
    using (var scope = new TransactionScope(TransactionScopeAsyncFlowOption.Enabled))
    {
        con.EnlistTransaction(Transaction.Current); //it's better to open the connection here, then dispose, but this will work
        com = con.CreateCommand(); //Probably need a new command object so it has the transaction context
        var batchOfItems = new List<Item>(); //Buffer, so the one connection can be used while enumerating items
        
        async using (var reader = await com.ExecuteReaderAsync(SQL, token)) 
        {
            while (await reader.ReadAsync(token))
            {
                var M = await Materializer(reader, token);
                batchOfItems.Add(M);
            }
        }

        foreach (var item in batchOfItems)
        {
            yield return item;
        }

        scope.Complete(); //Asynch option not available
        //No need to have explicit rollback call, instead it's standard for that to happen upon disposal if not completed
    }
}

Answer 3

An alternative to creating the IAsyncEnumerable by using a C# iterator could be to use the third-party library AsyncEnumerator (package).

This library was the main resource for creating asynchronous enumerables before the advent of C# 8, and it may still be useful because AFAIK it doesn't suffer by the limitations of the native yield. You are allowed to have try, catch and finally blocks in the body of the lambda passed to the AsyncEnumerable constructor, and invoke the yield.ReturnAsync method from any of these blocks.

Usage example:

using Dasync.Collections;

//...

public IAsyncEnumerable<Item> Select()
{
    return new AsyncEnumerable<Item>(async yield => // This yield is a normal argument
    {
        await using var transaction = await con.BeginTransactionAsync(token);
        try
        {
            var batchOfItems = new List<Item>();
            await using (var reader = await com.ExecuteReaderAsync(SQL, token))
            {
                while (await reader.ReadAsync(token))
                {
                    var M = await Materializer(reader, token);
                    batchOfItems.Add(M);
                }
            }
            foreach (var item in batchOfItems)
            {
                await yield.ReturnAsync(item); // Instead of yield return item;
            }
            await transaction.CommitAsync();
        }
        catch (Exception ex)
        {
            await transaction.RollbackAsync();
        }
    });
}

The yield in the above example is not the C# yield contextual keyword, but just an argument with the same name. You could give it another name if you want.