c# - Managing transactions

Question

How to properly implement transactions that span across multiple repositories?

Assume we have 2 repositories (transient)

internal sealed class UserRepository : IUserRepository
{
    public UserRepository(IDbConnection connection) {}

    public Task<int> Create(int addressId, string name) {}
}

internal sealed class AddressRepository : IAddressRepository
{

    public AddressRepository(IDbConnection connection, IDbTransaction transaction, ILogger<AddressRepository> logger){}
    public Task<int> Create(string country){}
}

I want to call both IUserRepository.Create and IAddressRepository.Create on single transaction.

What I tried so far:

1. Get transactions from repositories

internal sealed class UserRepository : IUserRepository
{
   public void OpenConnection(){}
   public void CloseConnection(){}
   public IDbTransaction BeginTransaction(){}

   public async Task<int> Create(int addressId, string name)
   {
       OpenConnection();
       using var transaction = BeginTransaction();
       var result = await Create(addressId, name, transaction);
       transaction.Commit();
       CloseConnection();
       return result;
   }
   public Task<int> Create(int addressId, string name, IDbTransaction transaction)
   {
       ArgumentNullException.ThrowIfNull(transaction.Connection);
       return transaction.Connection.ExecuteScalarAsync<int>(command, params, transaction);
   }
}

This however doesn't look clean to me, each method will need overload with transaction.

2. Repository as static class ... bad idea, not testable, can't inject other dependencies like ILogger<T>.

3. Repository and transaction factories

This would require small tweaks to repositories

internal sealed class UserRepository : IUserRepository
{
    public UserRepository(IDbConnection connection, IDbTransaction transaction){}
    public Task<int> Create(int addressId, string name){}
}

and couple additional classes that manage creation of transaction and repositories

internal sealed class RepositoryFactory
{

    public RepositoryFactory(Func<IDbConnection, IDbTransaction, IAddressRepository> addressFactory,
        Func<IDbConnection, IDbTransaction, IUserRepository> userFactory){}

    public IAddressRepository CreateAddressRepository(IDbConnection connection, IDbTransaction transaction){}

    public IUserRepository CreateUserRepository(IDbConnection connection, IDbTransaction transaction){}
}


internal sealed class Transaction : ITransaction
{
    private readonly IDbConnection _connection;
    private readonly RepositoryFactory _repositoryFactory;
    private readonly IDbTransaction _transaction;

    public Transaction(IDbConnection connection, RepositoryFactory repositoryFactory){}

    public IAddressRepository CreateAddressRepository()
    {
        return _repositoryFactory.CreateAddressRepository(_connection, _transaction);
    }

    public IUserRepository CreateUserRepository()
    {
        return _repositoryFactory.CreateUserRepository(_connection, _transaction);
    }


    public void Commit(){}
    public void Rollback(){}
    public void Dispose(){}
}


internal sealed class TransactionFactory : ITransactionFactory
{
    private readonly Func<ITransaction> _transaction;

    public TransactionFactory(Func<ITransaction> transaction){}
    public ITransaction Create()
    {
        return _transaction();
    }
}

Example of use

internal sealed class HighLevelService : IHighLevelService
{
    private readonly ITransactionFactory _transactionFactory;

    public HighLevelService(ITransactionFactory transactionFactory)
    {
        _transactionFactory = transactionFactory;
    }

    public async Task CreateUser(string country, string name)
    {
        using var transaction = _transactionFactory.Create();

        var ar = transaction.CreateAddressRepository();
        var ur = transaction.CreateUserRepository();

        var addressId = await ar.Create(country);
        var userId = await ur.Create(addressId, name);

        transaction.Commit();
    }
}

This is nice... almost. I get new transaction on each call of ITransactionFactory.Create and repositories which under the hood have same connection/transaction. I don't need to worry about opening and closing connection anymore.

However I think it's still flawed. ITransaction shouldn't be responsible for creating repositories, it's job of RepositoryFactory. It would be nice if I could somehow invert dependency.

internal sealed class RepositoryFactory
{

    public RepositoryFactory(Func<IDbConnection, IDbTransaction, IAddressRepository> addressFactory,
        Func<IDbConnection, IDbTransaction, IUserRepository> userFactory){}

    public IAddressRepository CreateAddressRepository(ITransaction transaction){}

    public IUserRepository CreateUserRepository(ITransaction transaction){}
}

but this would require ITransaction to look like this.

internal interface ITransaction : IDisposable
{

    IDbConnection Connection {get;}
    IDbTransaction Transaction {get;}
    void Commit();
    void Rollback();
}

which I don't like.

Is there any way to fix it without exposing underlying connection and transaction. Or perhaps there is other pattern that I could use.

Depdency injection for completnes

builder.Services.AddSingleton<RepositoryFactory>(sp =>
{
    return new RepositoryFactory(
        (connection, transaction) =>
            new AddressRepository(connection, transaction, sp.GetRequiredService<ILogger<AddressRepository>>()),
        (connection, transaction) => new UserRepository(connection, transaction));
});
builder.Services.AddTransient<ITransaction, Transaction>();
builder.Services.AddSingleton<ITransactionFactory>(sp =>
{
    var connection = sp.GetRequiredService<IDbConnection>();
    var repositoryFactory = sp.GetRequiredService<RepositoryFactory>();
    return new TransactionFactory(() => new Transaction(connection, repositoryFactory));
});
builder.Services.AddTransient<IHighLevelService, HighLevelService>();

Answer 1

That looks incredible complex. Why?

A repository needs an open database connection to work on. It is not their responsibility to open it, close it or create transactions on it.

Your flow should look like this:

• start
• open database connection
• begin transaction
• create user repository passing open connection
• create address repository passing open connection
• call user repository method
• call address repository method
• commit transaction
• close database connection

How you build that is up to you and your program. You are probably using dependency injection to create new repositories and inject the database connection. You may have a middleware that opens a transaction and commits it on success and rolls it back on exceptions. Or it is just part of your program flow. Anything else is just too complex, having two repositories on a single connection and transaction is not rocket science, keep it simple.

using var connection = OpenDatabase();

using var transaction = connection.BeginTransaction();

var userRepository = new UserRepository(connection);
var addressRepository = new AddressRepository(connection);

var address = addressRepository.Create(data, data, data);
var user = userRepository.Create(data, data, data, address);

transaction.Commit();
connection.Close();

Using dependency injection, that code might look like this:

call(IDbConnection connection, IUserRepository userRepository, IAddressRepository addressRepository)
{
    using var transaction = connection.BeginTransaction();

    var address = addressRepository.Create(data, data, data);
    var user = userRepository.Create(data, data, data, address);

    transaction.Commit();
}