RabbitMq, SqlServer, EF Core and Outbox

Do you have a sample showing how a DbContext (scoped in the container) can participate in the Outbox Transaction?

I expect to use my Services, Repositories and therefore DbContext as always via dependencies in the MessageHandler (no newing)

Thanks

@Stig_Christensen:

We have a few samples/articles that may help you:

My colleague Ramon also has a sample that may help that uses Outbox with EF and the handler. It is a bit old but it will show you the basics:

Thanks. I might be wrong, but the linked documentation and the GitHub repo doesn’t show this combination.

The problem is that the scoped DbContext from the IOC container (MS) should use the same transaction/connection as the Outbox logic. This without newing the DbContext in the handler.

If you are willing to take a dependency on NSB within your EF configuration, you can use a DbContextFactory approach to set the external outbox connection and transaction.

Take a look at this EF Core article: Advanced Performance Topics | Microsoft Learn

The concept will be the same (paraphrasing to your use case):

Register a context factory as a Singleton service:

builder.Services.AddDbContextFactory<SomeDbContext>(
    o => o.UseSqlServer() // use this override that doesnt provide a connection or connection string);

Next, write a custom context factory which gets a context from the Singleton factory we registered, and injects the outbox connection into context instances it hands out:

public class OutboxDbContextFactory<TDbContext> : IDbContextFactory<TDbContext>
{
    private readonly ISqlStorageSession _storage;
    private readonly IDbContextFactory<TDbContext> _innerFactory
    public OutboxDbContextFactory(IDbContextFactory<TDbContext> innerFactory, ISqlStorageSession storage)
    {
        //add null checks       

        _storage = storage; 
        _innerFactory = innerFactory;
    }

    public TDbContext CreateDbContext()
    {
        //potentially check if storage.Connection / Transaction are null

        //if you don't expect all injections to happen within a handler,
        //a better approach is to inject IServiceProvider and call _services.GetService<ISqlStorageSession>() and check for null

        var context = _innerFactory.CreateDbContext();
        context.Database.SetDbConnection(_storage.Connection);

        //Use the same underlying ADO.NET transaction
        context.Database.UseTransaction(_storage.Transaction);

        //Ensure context is flushed before the transaction is committed
        _storage.OnSaveChanges((s, token) => context.SaveChangesAsync(token));

        return context;
    }
}

Once we have our custom context factory, register it as a Scoped service:

builder.Services.AddScoped<OutboxDbContextFactory<SomeDbContext>>();

Finally, arrange for a context to get injected from our Scoped factory:

builder.Services.AddScoped(
    sp => sp.GetRequiredService<OutboxDbContextFactory<SomeDbContext>>().CreateDbContext());

As this point, your endpoints automatically get injected with a context instance that has the right outbox connection, without having to know anything about it.

Bonus points if you want to create an extension method to wire it all up:

public static class OutboxDbContextExtensions
{
    public static void AddDbContextWithOutboxConnection<TDbContext>(this IServiceCollection services, Action<DbContextOptionsBuilder<TDbContext>> builder)
        where TDbContext : DbContext
    {
        services.AddDbContextFactory<TDbContext>(builder);
        services.AddScoped<OutboxDbContextFactory<TDbContext>>();
        builder.Services.AddScoped(
            sp => sp.GetRequiredService<OutboxDbContextFactory<TDbContext>>().CreateDbContext());
    }
}

Note, you can also used a pooled context factory to squeeze some more perf out of EF Core

Great, finally had the time to look into this.

    //potentially check if storage.Connection / Transaction are null
    //if you don't expect all injections to happen within a handler,
    //a better approach is to inject IServiceProvider and call _services.GetService<ISqlStorageSession>() and check for null

Yes I am also using the DbContext outside MessageHandlers. But I get this Exception when requesting it and not null

In stead of a try-catch do you have another approach to detect when not inside a MessageHandler?

Thanks

This is how you could support an optional shared connection without needing try…catch blocks

note that this code is untested as I just wrote it here, but it should be a good starting point.

public TDbContext CreateDbContext()
{
  var context = _innerFactory.CreateDbContext();
  var storage= _serviceProvider.GetService<ISqlStorageSession>();

  if (storage is null || storage.Connection is null)
  {
    //at this point if a connection string was not set you will get a runtime exception that EF can't create a connection.
    //options: a. hardcode a connection string
    //b. inject IConfiguration and get a connection string by name
    // create a new OutboxDbContextFactoryOptions class which allows callers to set a fallback connection string
  
    context.Database.SetConnectionString(????);
    return context;
  }
  
  context.Database.SetDbConnection(storage.Connection);
  if (storage.Transaction is not null)
  {
    //Use the same underlying ADO.NET transaction
    context.Database.UseTransaction(_storage.Transaction);
  }

  return context;
}

But the problem is that code below throws the Exception from the screen capture. I am using NServiceBus 7.8.1

I get it.

even though you can likely get this to work with some effort, here is a complementary solution:

create an Accessor class similar to HttpContextAccessor that uses AsyncLocal<ISqlStorageSession> to store the session.

public class SqlStorageSessionAccessor
{
  private static AsyncLocal<ISqlStorageSession> _local = new ();

  public ISqlStorageSession? Current
  {
    get => _local.Value;
    set => _local.Value = value;
  }
}

register this as a singleton.
note that this is very simplistic and you may want to make it more robust just like HttpContextAccessor.

in your DBContextFactory inject the accessor and check for null.
when calling from outside a handler nothing should break as you never set the value in the accessor

create a SetSqlStorageSessionAccessor Behavior that injects both ISqlStorageSession and SqlStorageSessionAccessor, and then sets the Current value.

this may seem a bit roundabout but:

“All problems in computer science can be solved by another level of indirection, except for the problem of too many layers of indirection.” Fundamental theorem of software engineering - Wikipedia

Really? :slight_smile: It seems like I am the first person to integrate Outbox with EF. Should some of this code be a nuget extension?

And which IBehaviorContext should I use?

Any hints to this question?