[Feature] Does Outbox supports multiple connections?

[IlSocio]

Hello,
I'm trying to use the Outbox implementation of Brighter, but it is not clear to use the Outbox when we have multiple SQL connections.

eg.

        [HttpGet]
        [Route("greeting")]
        public async Task<HttpResponseMessage> TestPubCtx1Async(CancellationToken cancellationToken)
        {
            using (var trx1 = _greetingsCtx.Database.BeginTransaction())
            {
                var entity = new GreetingEntity() { GreetingText = "asdasd1" };
                _greetingsCtx.Greetings.Add(entity);
                _greetingsCtx.SaveChanges();

                var integrEvent = new GreetingChangedEvent("Hello from the web1");
                await _outbox.DepositPostAsync(integrEvent);
                trx1.Commit();
            }
            return new HttpResponseMessage(System.Net.HttpStatusCode.NoContent);
        }

        [HttpGet]
        [Route("another")]
        public async Task<HttpResponseMessage> TestCtx2Async(CancellationToken cancellationToken)
        {
            using (var trx2 = _anotherCtx.Database.BeginTransaction())
            {
                var entity = new TestEntity() { Text = "asdasd2" };
                _anotherCtx.SomeEntities.Add(entity);
                _anotherCtx.SaveChanges();

                var integrEvent = new TestChangedEvent("Hello from the web2");
                await _outbox.DepositPostAsync(integrEvent);
                trx2.Commit();
            }
            return new HttpResponseMessage(System.Net.HttpStatusCode.NoContent);
        }

In the IOC I'm registering the ConnectionProvider using
.UseMsSqlTransactionConnectionProvider()
Which will point to only 1 of the 2 possible Contexts.

Ideally, there exists this overload, which would allow me to explicitly provide the transaction, but, for some reason, it is marked as "private"

Brighter/src/Paramore.Brighter/CommandProcessor.cs

Line 455 in 7ad1a91

private Guid DepositPost<T>(T request, IAmABoxTransactionConnectionProvider connectionProvider) where T : class, IRequest

Would you accept a pull-request to make it available as Public, so that I use the Outbox on multiple connections?
Or there exists other way to archive the same result?

[holytshirt]

@preardon would you have some ideas on how best to do this?

[preardon]

It's specifically marked as private as it isn't considered as something we should be parsing in / a concern of your application. However this approach doesn't allow multiple dB contexts.

Let me have a think about this requirement and get back to you

[IlSocio]

@preardon, note that this limitation doesn't apply only on DbContexts / EF, but it applies also when using pure SQL connections/transactions.
Currently, you can't use the Outbox with 2 different connections.

I write here a couple of options based on that overload which should be part of the IAmACommandProcessor interface.
So we can explicitly provide the transaction at runtime, with the double benefit of supporting the use-case and make it clear that the outbox is operating within a very specific transaction (currently, this is a hidden dependency)

After the method will be public, we'll have many options to consume it:
Option1:

        [HttpGet]
        [Route("greeting")]
        public async Task<HttpResponseMessage> TestPubCtx1Async(CancellationToken cancellationToken)
        {
            using (var trx1 = _greetingsCtx.Database.BeginTransaction())
            {
                var entity = new GreetingEntity() { GreetingText = "asdasd1" };
                _greetingsCtx.Greetings.Add(entity);
                _greetingsCtx.SaveChanges();

                var integrEvent = new GreetingChangedEvent("Hello from the web1");
                
                // Option1:
                _commandProcessor.DepositPost(greeting, _context);
                
                trx1.Commit();
            }
            return new HttpResponseMessage(System.Net.HttpStatusCode.NoContent);
        }

    static class CommandProcessor_EntityFrameworkCore_Extensions
    {
        public static Guid DepositPost<TRequest, TCont>(this IAmACommandProcessor processor, TRequest request, TCont context)
            where TRequest : class, IRequest
            where TCont : DbContext
        {
            var connectionProvider = new MsSqlEntityFrameworkCoreConnectionProvider<TCont>(context);
            return processor.DepositPost(request, connectionProvider);
        }
    }

Options2: uses a more generic TransactionConnectionProvider, which doesn't depend on EntityFramework, so we should be able to reuse it with Dapper as well with pure ADO net transactions...

        [HttpGet]
        [Route("greeting")]
        public async Task<HttpResponseMessage> TestPubCtx1Async(CancellationToken cancellationToken)
        {
            using (var trx1 = _greetingsCtx.Database.BeginTransaction())
            {
                var entity = new GreetingEntity() { GreetingText = "asdasd1" };
                _greetingsCtx.Greetings.Add(entity);
                _greetingsCtx.SaveChanges();

                var integrEvent = new GreetingChangedEvent("Hello from the web1");
                
                // Option2:
                _commandProcessor.DepositPost(greeting, transaction.GetDbTransaction());
                
                trx1.Commit();
            }
            return new HttpResponseMessage(System.Net.HttpStatusCode.NoContent);
        }

    static class CommandProcessor_DbTransaction_Extensions
    {
        public static Guid DepositPost<TRequest>(this IAmACommandProcessor processor, TRequest request, DbTransaction dbTransaction)
            where TRequest : class, IRequest
        {
            var transactionProvider = new MsSqlTransactionConnectionProvider(dbTransaction);
            return processor.DepositPost(request, transactionProvider);
        }
    }

    public class MsSqlTransactionConnectionProvider : IMsSqlTransactionConnectionProvider
    {
        private readonly DbTransaction _dbTransaction;

        public MsSqlTransactionConnectionProvider(DbTransaction dbTransaction)
        {
            _dbTransaction = dbTransaction;
        }

        public SqlConnection GetConnection()
        {
            return (SqlConnection) _dbTransaction.Connection;
        }

        public Task<SqlConnection> GetConnectionAsync(CancellationToken cancellationToken = default)
        {
            return Task.FromResult((SqlConnection)_dbTransaction.Connection);
        }

        public SqlTransaction GetTransaction()
        {
            return (SqlTransaction) _dbTransaction;
        }

        public bool HasOpenTransaction => _dbTransaction != null;
        public bool IsSharedConnection => true;
    }

[preardon]

So the Problem with option two is you have then tied the command processor to adodb Transaction.

I was thinking of something more towards a Transaction provider that takes a "Name" or identifier where you can then you can specify a name/identifier if you want to pick one, this way it's generic enough that it will work for all connection types.

[IlSocio]

Actually, this is the signature:
Guid DepositPost<T>(T request, IAmABoxTransactionConnectionProvider connectionProvider) where T : class, IRequest;
so, any implementation of the IAmABoxTransactionConnectionProvider will work, for both the options.
Of course, the provided implementation at runtime, must be consistent with the underling Database you're using (as it must be already right now)

For the option2 I didn't update the sample, but the idea is to create the connection/transaction using pure ADO net.
and let the EF DbContext bind to that same transaction with DbContext.UseTransaction(trx1)

[preardon]

If we inject the IAmABoxTransactionProvider this would work, however that is a Brighter internal and I feel it's better to set a Default and give the ability to Pass a name, this also means you don't have to rely on DI either

[IlSocio]

Unless there are any particular drawbacks, imho, it would be perfect if we could have the option to choose between the two different approaches:

1. current one:, by letting brighter infer the transaction leveragin the UseMsSqlTransactionConnectionProvider()
2. explicitly provide the transaction to use, by exposing that private overload.

The explicit approach would give to Brighter more flexibility because we'll be able to adopt it in more complex and generic scenarios, like the one below, which involves an ADO transaction created explicitly and shared by many DbContexts.

            using (var conn = new SqlConnection(_dbConnString))
            {
                var greetingsCtx = new GreetingContext(conn);
                var anotherCtx = new AnotherContext(conn);
                conn.Open();
                using (var trx1 = conn.BeginTransaction())
                {
                    var entity1 = new GreetingEntity() { GreetingText = "asdasd1" };
                    greetingsCtx.Database.UseTransaction(trx1);
                    greetingsCtx.Greetings.Add(entity1);
                    greetingsCtx.SaveChanges();

                     var integrEvent1 = new GreetingChangedEvent("Hello from the web1");
                     _outbox.DepositPost(integrEvent1, trx1);
                
                    var entity2 = new TestEntity() { Text = "asdasd2" };
                    anotherCtx.Database.UseTransaction(trx1);
                    anotherCtx.SomeEntities.Add(entity2);
                    anotherCtx.SaveChanges();
               
                     var integrEvent2 = new TestChangedEvent("Hello from the web2");
                    _outbox.DepositPost(integrEvent2, trx1);

                    trx1.Commit(); // commits changes to _greetingsCtx + _anotherCtx + _outbox
                }
            }

By looking at the code above, you can clearly see which are all the components involved in the same transaction, because each component is explicitly referencing the trx1.

      _greetingsCtx.Database.UseTransaction(trx1);
      _outbox.DepositPost(integrEvent1, trx1);
      _anotherCtx.Database.UseTransaction(trx1);
      _outbox.DepositPost(integrEvent2, trx1);

Do you see any drawback in providing this additional option?

[preardon]

@IlSocio doing what you've stated above would close this to other providers as well as bring along additional dependencies, I will knock out a quick Draft PR of something that I think would be a little cleaner and submit it in an hour or so when I've got it working