Querying and Performing Transactions Across Multiple Database Schemas in a Modular Monolith

Imagine a big school where every class has its own cupboard. The science class keeps science books in its cupboard. The maths class keeps maths books in its cupboard. The library keeps story books in its cupboard. This is tidy. Each teacher knows exactly where their things are, and nobody messes with another class's cupboard.

But one day the principal asks a simple question. "How many books does the whole school have, and which student borrowed what?" Now you have a problem. The books are spread across many cupboards. You cannot open one drawer and see everything. You have to walk to each cupboard, count, and add it all up yourself.

A modular monolith in .NET works the same way. It is one application, but it is split into separate modules. Each module keeps its data in its own database schema, like a separate cupboard. This keeps things clean and safe. But the moment you need data from two modules at once, or you need to change two modules together and keep them in sync, you hit the same school problem. This article shows you how to solve it the right way.

What is a modular monolith, in simple words

A monolith is one application that you build and deploy as a single unit. A modular monolith is still one application, but inside it is divided into clear modules. Think of a delivery company app with three modules:

• Shipments — tracks parcels being sent.
• Stocks — tracks how many items are left in the warehouse.
• Carriers — tracks delivery partners like courier companies.

Each module gets its own schema in the same database. So in SQL Server you might have a shipments schema, a stocks schema, and a carriers schema. Each module also gets its own EF Core DbContext.

The big rule of a modular monolith is simple. A module is not allowed to reach into another module's schema directly. The Shipments module cannot read the stocks tables on its own. If it needs stock data, it must ask the Stocks module politely through a method or an API. This rule is what keeps modules independent, so that one day you can pull a module out and turn it into its own microservice if you need to.

Why separate schemas help

Setting up one DbContext per module

You give each module its own DbContext and point it at its own schema. The trick is the HasDefaultSchema call. It tells EF Core which schema this context lives in.

public sealed class ShipmentsDbContext : DbContext
{
    public ShipmentsDbContext(DbContextOptions<ShipmentsDbContext> options)
        : base(options) { }
 
    public DbSet<Shipment> Shipments => Set<Shipment>();
 
    protected override void OnModelCreating(ModelBuilder modelBuilder)
    {
        // Every table in this context lives in the "shipments" schema.
        modelBuilder.HasDefaultSchema("shipments");
        modelBuilder.ApplyConfigurationsFromAssembly(
            typeof(ShipmentsDbContext).Assembly);
    }
}

You register each context in Program.cs. They can all point at the same connection string, so they share one physical database but keep separate schemas.

var connectionString = builder.Configuration.GetConnectionString("Default");
 
builder.Services.AddDbContext<ShipmentsDbContext>(o =>
    o.UseSqlServer(connectionString));
 
builder.Services.AddDbContext<StocksDbContext>(o =>
    o.UseSqlServer(connectionString));
 
builder.Services.AddDbContext<CarriersDbContext>(o =>
    o.UseSqlServer(connectionString));

One more important habit: do not create foreign keys between tables that belong to different modules. A foreign key from shipments.Shipment to stocks.Product would glue the two modules together at the database level. If you later move Stocks to its own database, that foreign key breaks everything. Keep the link as a plain ID value instead, and let the modules talk through code.

The two hard problems

Once modules are nicely separated, two everyday tasks become tricky.

1. Querying across modules. You want a screen that shows a shipment, its carrier name, and the current stock level. That data lives in three schemas. You cannot write one EF Core query that joins all three.
2. Transactions across modules. Creating a shipment might need you to lower the stock count and register the parcel with a carrier, all together. If one step fails, you do not want a half-done mess.

Let us solve each one.

Problem 1: Querying across schemas

The clean answer is: do not join. Query each module separately, then combine in memory.

The module that owns the data exposes a small read method. Other modules call it. They never touch the foreign tables directly.

// Inside the Stocks module: a public read API for other modules.
public interface IStockReader
{
    Task<int> GetAvailableQuantityAsync(Guid productId, CancellationToken ct);
}
 
// The screen builder in the Shipments side combines results.
public async Task<ShipmentView> BuildViewAsync(Guid shipmentId, CancellationToken ct)
{
    var shipment = await _shipmentsDb.Shipments
        .FirstAsync(s => s.Id == shipmentId, ct);
 
    var carrierName = await _carrierReader
        .GetCarrierNameAsync(shipment.CarrierId, ct);
 
    var stock = await _stockReader
        .GetAvailableQuantityAsync(shipment.ProductId, ct);
 
    return new ShipmentView(shipment.Id, carrierName, stock);
}

This feels like extra work compared to one big SQL join, and it is a little slower. But it keeps your modules independent. Each module stays in charge of its own data and its own rules.

When reads get heavy

If a screen needs to read from many modules very often, building it from many small calls can get slow. A common fix is the composite view pattern (also called Backend for Frontend, or BFF). You build a separate read model that gathers data from each module and stores a ready-made copy just for display. The modules publish small events when their data changes, and the read model updates itself.

Problem 2: Transactions across schemas

Now the harder part. You want to create a shipment and reduce the stock count together. If reducing stock fails, the shipment should not be saved either. This is what a database transaction is for: all steps succeed, or all steps undo.

There are two main strategies. Pick based on whether the modules share one database.

Strategy A: Share one transaction (strong consistency)

When all modules live in the same physical database, EF Core lets several DbContext instances join the same transaction. You open one connection, begin one transaction, and tell each context to use it with Database.UseTransaction.

public async Task CreateShipmentAsync(CreateShipment cmd, CancellationToken ct)
{
    // Both contexts must share ONE open connection.
    var connection = _shipmentsDb.Database.GetDbConnection();
    await connection.OpenAsync(ct);
 
    await using var tx = await connection.BeginTransactionAsync(ct);
 
    // Enlist both contexts in the same transaction.
    await _shipmentsDb.Database.UseTransactionAsync(tx, ct);
    await _stocksDb.Database.UseTransactionAsync(tx, ct);
 
    _shipmentsDb.Shipments.Add(new Shipment(cmd.ProductId, cmd.CarrierId));
    await _shipmentsDb.SaveChangesAsync(ct);
 
    await _stocksDb.ReduceQuantityAsync(cmd.ProductId, cmd.Count, ct);
    await _stocksDb.SaveChangesAsync(ct);
 
    // One commit covers both modules.
    await tx.CommitAsync(ct);
}

This gives strong consistency. Everything happens together or nothing does. The cost is coupling: both modules now depend on the same database and the same connection. If you ever move Stocks to its own database, this code stops working. So use a shared transaction only when you are confident the modules will stay together.

Strategy B: The outbox pattern (eventual consistency)

When modules might live in different databases, or you want them loosely coupled, use the outbox pattern. The idea is gentle but powerful.

Each module saves its own data and a little message in the same transaction. That message is stored in an outbox table inside the same schema. Because the message is saved in the same transaction as the business data, it can never be lost. A background worker then reads the outbox and publishes the message to the other module.

public async Task CreateShipmentAsync(CreateShipment cmd, CancellationToken ct)
{
    // One transaction inside ONE module only.
    await using var tx = await _shipmentsDb.Database.BeginTransactionAsync(ct);
 
    _shipmentsDb.Shipments.Add(new Shipment(cmd.ProductId, cmd.CarrierId));
 
    // Save the event in the SAME transaction as the business data.
    _shipmentsDb.OutboxMessages.Add(OutboxMessage.For(
        new ShipmentCreated(cmd.ProductId, cmd.Count)));
 
    await _shipmentsDb.SaveChangesAsync(ct);
    await tx.CommitAsync(ct);
    // A background worker will later publish ShipmentCreated to Stocks.
}

The Stocks module receives ShipmentCreated and lowers its own stock in its own transaction. The two modules are now eventually consistent. There is a tiny delay between the shipment being created and the stock going down, usually a fraction of a second. In return, the modules stay fully independent. Neither needs to know the other's database.

A note on tools: message libraries help here, but be aware of licensing. MassTransit and MediatR have moved to a commercial license for newer versions. You can still use the outbox pattern with a simple background worker you write yourself, or with EF Core features, without paying for anything.

Comparing the two strategies

Putting it together: a real flow

Here is how creating a shipment looks end to end with the outbox approach, which is the safer default for most teams.

The user gets a fast "Created" reply. Behind the scenes the stock updates a moment later. If the worker crashes, the outbox row is still there, so it will be retried. Nothing is lost.

Common mistakes to avoid

• Joining across schemas in one query. It feels easy but it breaks the boundaries that make a modular monolith worth building.
• Foreign keys between modules. They quietly glue your modules together. Store a plain ID instead.
• Sharing a transaction across different databases. This needs a distributed transaction coordinator, which is heavy and fragile. Use the outbox instead.
• Forgetting to open the connection before UseTransaction. The shared-connection approach only works on one open connection shared by all contexts.
• No retry on the outbox worker. Messages can fail. Always retry and mark rows as processed only after success.

Quick recap

• A modular monolith is one app split into modules, where each module owns its own database schema and its own EF Core DbContext.
• A module must never read another module's schema directly. It asks through a method or small API.
• You cannot JOIN across schemas. Query each module separately and combine the results in memory, or build a composite read model for busy screens.
• You cannot freely share a transaction across modules, unless they share the same physical database. Then you can enlist contexts with Database.UseTransaction for strong consistency.
• When modules may live in different databases, use the outbox pattern for eventual consistency and loose coupling.
• Avoid cross-module foreign keys, and always retry your outbox worker.
• Newer MassTransit and MediatR versions are now commercially licensed, so plan accordingly or roll a simple worker yourself.

References and further reading

• Transactions - EF Core (Microsoft Learn)
• Using Multiple EF Core DbContexts In a Single Application (Milan Jovanovic)
• Modular Monolith Data Isolation (Milan Jovanovic)
• Querying and Performing Transactions Across Multiple Database Schemas in a Modular Monolith (Anton DevTips)
• Modular Monoliths (Wolverine docs)