DotNet Core, async/await and TransactionScope

Most of our code uses a Repository Pattern for database access. We create a new connection in each method, make the call, usually with Dapper, then Dispose the connection. This has served us well, until recently when we tried to use a TransactionScope and async/await calls in the repositories.

public async Task<IEnumerable<int>> GetBatch(int count, int status)
{
    using (var connection = _orderDbConnectionProvider.GetNewConnection())
    {
        return await connection.QueryAsync<int>("GET_ORDER_BATCH", 
            new { count, status }, 
            commandType: CommandType.StoredProcedure);
    }
}

Internally, TransactionScope uses thead-local storage by default, which doesn’t work with async/await, as you don’t know what thread the call will be made with. That’s an easy fix. It’s even easier if you have a helper class to create transactions.

public TransactionScope CreateTransactionScope()
{
    var transactionOptions = new TransactionOptions
    {
        IsolationLevel = IsolationLevel.ReadCommitted,
        Timeout = TransactionManager.MaximumTimeout // use a sensible value here
    };
 
    return new TransactionScope(TransactionScopeOption.Required, 
        transactionOptions, 
        TransactionScopeAsyncFlowOption.Enabled);
}

The “TransactionScopeAsyncFlowOption.Enabled” bit it what fixes async/await. Once that was fixed, we still had a failing integration test. We tend to test using NUnit, calling our database calls, to make sure everything is mapped to the stored procedure correctly. We often use a TransactionScope there to roll back changes made to the database during the test. It is out development database, but we still don’t want to clutter it.

[TestFixture, Category("Integration")]
public class OrderRepositoryTest
{
    private IOrderRepository _orderRepository;
    private TransactionScope _transactionScope;
 
    private const string _connectionString = "Data Source=devdb;Initial Catalog=somedatabase;Integrated Security=SSPI;";
 
    [SetUp]
    public void Setup()
    {
        _transactionScope = new TransactionScope(TransactionScopeOption.Required, 
            new TransactionOptions
            {
                IsolationLevel = IsolationLevel.ReadCommitted
            }, TransactionScopeAsyncFlowOption.Enabled);
 
        _orderRepository = new OrderRepository(new OrderDbConnectionProvider(_connectionString));
    }
 
    [TearDown]
    public void TearDown()
    {
        _transactionScope.Dispose();
    }
 
    [Test]
    public async Task UpdateOrderStatusTest()
    {
        var orderId = await _orderRepository.SaveOrder(1234, DateTimeOffset.Now, "1234", "1234", "1234", DateTimeOffset.Now, "1234", DateTimeOffset.Now);
 
        await _orderRepository.UpdateStatus(orderId, 2);
 
        var result = await _orderRepository.GetOrder(orderId);
 
        Assert.AreEqual((int)result.OrderStatusId, 2);
    }
}

The test code above works. However a previous version was missing one await call in the test method: await _orderRepository.UpdateStatus(orderId, 2);

The developer forgot the await because we don’t return a value, just a Task. This caused the next line to execute too soon and messed up our Transaction. Once this was fixed, it all worked.