Data Isolation per Module: Schema per Module with EF Core and Cross-Module Queries

Version note: The code in this post was built and run with C# 14, .NET 10, Entity Framework Core (EF Core) 10 and SQL (Structured Query Language) Server 2025.

Guess First

Bookstore's modules now work with their own DbContexts and can't see each other's entities because the compiler won't allow it. But they all live in the same SQL Server database. Someone from finance asks for a "copies sold and revenue per book" report, and the developer who wrote Ordering writes this query:

SELECT b.Title, SUM(i.Quantity) AS Copies
FROM ordering.OrderItems i
JOIN catalog.Books b ON b.Id = i.BookId
GROUP BY b.Title;

Did this single line just breach every boundary on the C# side? If the Catalog team splits the Books.Title column into Title and Subtitle next month, what happens to this report, and who notices? And can the database itself refuse to run this query?

Levels of Isolation

There isn't one way to separate modules' data; there are several levels, ordered by increasing isolation and increasing cost:

Bookstore's choice is schema per module. The team is small, a single SQL Server is operated and independent deployability is "not now". Schema per module makes ownership visible in the database and turns moving a module to a separate database, when the day comes, into a mechanical job.

A DbContext and HasDefaultSchema per Module

Each module has its own DbContext and puts all its tables into its own schema with HasDefaultSchema. Catalog as it stands in this post:

internal sealed class CatalogDbContext(DbContextOptions<CatalogDbContext> options) : DbContext(options)
{
    public const string Schema = "catalog";

    public DbSet<Book> Books => Set<Book>();
    public DbSet<BookSales> Sales => Set<BookSales>();

    protected override void OnModelCreating(ModelBuilder modelBuilder)
    {
        modelBuilder.HasDefaultSchema(Schema);
        modelBuilder.Entity<Book>().Property(b => b.ListPrice).HasPrecision(10, 2);
        modelBuilder.Entity<BookSales>().HasKey(s => s.BookId);
        modelBuilder.AddInbox();
    }
}

The DbContext is internal; no other module can inject it, and the Schema constant is not visible from outside either. All Fluent API (Application Programming Interface) configuration stays inside the module here; we covered that configuration in detail in the EF Core series post "Data Annotations or Fluent API in EF Core? OnModelCreating and IEntityTypeConfiguration". The outbox and inbox tables land in the same schema through AddOutbox()/AddInbox(), because they are module data too.

A Migrations History Table per Module

By default EF Core keeps applied migrations in a single table named __EFMigrationsHistory. When five DbContexts apply migrations to the same database, they all write to the same table, the one in the default schema (dbo). The Microsoft Learn page "Custom Migrations History Table" shows how to change that table's name and schema with MigrationsHistoryTable(). Each module keeps its own history table in its own schema:

public static IServiceCollection AddOrderingModule(this IServiceCollection services, string connectionString, bool useOutbox)
{
    services.AddDbContext<OrderingDbContext>(o => o.UseSqlServer(connectionString,
        sql => sql.MigrationsHistoryTable("__EFMigrationsHistory", OrderingDbContext.Schema)));
    services.AddSingleton(new OrderingOptions(useOutbox));
    services.AddScoped<PlaceOrderHandler>();
    services.AddIntegrationEvent<OrderPlaced>();
    if (useOutbox)
        services.AddOutboxProcessor<OrderingDbContext>(TimeSpan.FromSeconds(5));
    return services;
}

public static Task InitializeOrderingModuleAsync(this IServiceProvider services) =>
    services.MigrateModuleAsync<OrderingDbContext>();

Each module also generates migrations in its own project. The commands from the EF Core series post "EF Core Migrations: migrations add, database update, Scripts and Bundles" run per module with --context and --project; we installed the dotnet-ef tool locally through the tool manifest in the solution folder, not globally:

# Each module's migrations live in its own project, with its own context
for m in Catalog Inventory Ordering Shipping Payments; do
  dotnet ef migrations add Initial \
    --project src/Modules/$m/Bookstore.$m.Core \
    --startup-project src/Bookstore.Host \
    --context ${m}DbContext \
    --output-dir Persistence/Migrations
done

The generated migration creates the schema itself:

protected override void Up(MigrationBuilder migrationBuilder)
{
    migrationBuilder.EnsureSchema(
        name: "catalog");

    migrationBuilder.CreateTable(
        name: "Books",
        schema: "catalog",
        columns: table => new
        {
            Id = table.Column<Guid>(type: "uniqueidentifier", nullable: false),
            Isbn = table.Column<string>(type: "nvarchar(max)", nullable: false),
            Title = table.Column<string>(type: "nvarchar(max)", nullable: false),
            ListPrice = table.Column<decimal>(type: "decimal(10,2)", precision: 10, scale: 2, nullable: false),
            Currency = table.Column<string>(type: "nvarchar(max)", nullable: false)
        },
        constraints: table =>
        {
            table.PrimaryKey("PK_Books", x => x.Id);
        });

When the Host starts, each module applies only its own migrations with MigrateAsync. The tables in the database after placing one order:

schema table
------ -----
catalog __EFMigrationsHistory
catalog Books
catalog ProcessedMessages
catalog Sales
inventory __EFMigrationsHistory
inventory ProcessedMessages
inventory Reservations
inventory StockItems
ordering __EFMigrationsHistory
ordering OrderItems
ordering Orders
ordering OutboxMessages
payments __EFMigrationsHistory
payments Payments
payments ProcessedMessages
shipping __EFMigrationsHistory
shipping ProcessedMessages
shipping Shipments

Every schema has its own __EFMigrationsHistory table, and Ordering's knows only Ordering's migrations:

MigrationId
-----------
20261108002704_Initial
20261108002759_ReportingView

A single shared history table works, but it causes two problems. First, the dbo schema belongs to no module; shortly we will grant modules permissions only on their own schemas, and the migrating user's need to write to a shared table breaks that separation. Second, when you later move a module into a separate database, you have to pick its migration history out of the shared table. Note the warning on the same page too: if you change the history table after migrations have been applied, updating the existing table is up to you. On an existing system, prepare a script that moves the old records into the new table before turning this setting on.

Enforcing the Boundary in the Database: Schema Permissions

The real power of schemas is that they map straight onto SQL Server's permission model. We gave each module its own database user and defined permissions at schema level:

/* Each module's application user can only touch its own schema */
CREATE USER catalog_app WITHOUT LOGIN;
CREATE USER ordering_app WITHOUT LOGIN;
GRANT SELECT, INSERT, UPDATE, DELETE ON SCHEMA::catalog TO catalog_app;
GRANT SELECT, INSERT, UPDATE, DELETE ON SCHEMA::ordering TO ordering_app;
GO

We ran the opening query as Ordering's user:

/* Ordering's user can read its own table */
EXECUTE AS USER = 'ordering_app';
SELECT COUNT(*) AS MyOrders FROM ordering.Orders;
/* ... but cannot join Catalog's table */
SELECT i.Quantity, b.Title
FROM ordering.OrderItems i JOIN catalog.Books b ON b.Id = i.BookId;
REVERT;

MyOrders
--------
1

(1 rows affected)
Msg 229, Level 14, State 5, Line 5
The SELECT permission was denied on the object 'Books', database 'mm06', schema 'catalog'.

Ordering reads its own table but cannot join Catalog's; the boundary now exists in the database as well as the compiler. For this to work, the modular monolith has to be able to give each module its own connection string. That is why the AddOrderingModule(connectionString, ...) signature takes one parameter per module: today all five point to the same user, tomorrow each can point to its own. The user that applies migrations should be kept separate; it needs only ALTER on its own schema and CREATE TABLE in the database.

What to Do Instead of a Cross-Module Join

The report's need is real; only the method is forbidden. There are three alternatives, and which one to pick depends on the kind of question.

The first is the contract call from post 04. If a screen needs the book titles of a single order, Ordering calls Catalog's GetBooksAsync. The right choice for interactive, low-volume reads.

The second is a local read model kept up to date by events. If Catalog wants to show a "best sellers" list, instead of going to Ordering's table it can listen to the OrderPlaced event from the previous post and keep copies sold in its own schema:

using Bookstore.BuildingBlocks.Infrastructure.Messaging;
using Bookstore.BuildingBlocks.Messaging;
using Bookstore.Ordering.Contracts;
using Microsoft.EntityFrameworkCore;

namespace Bookstore.Catalog.Core;

// No join to Ordering's table for "best sellers": a local read model from the event
internal sealed class UpdateSalesWhenOrderPlaced(CatalogDbContext db, TimeProvider clock) : IIntegrationEventHandler<OrderPlaced>
{
    public async Task HandleAsync(OrderPlaced @event, CancellationToken ct)
    {
        if (await db.AlreadyProcessedAsync(@event.EventId, nameof(UpdateSalesWhenOrderPlaced), ct))
            return;

        foreach (OrderPlacedLine line in @event.Lines)
        {
            BookSales? sales = await db.Sales.FindAsync([line.BookId], ct);
            if (sales is null)
                db.Sales.Add(new BookSales { BookId = line.BookId, CopiesSold = line.Quantity });
            else
                sales.CopiesSold += line.Quantity;
        }
        db.MarkProcessed(@event.EventId, nameof(UpdateSalesWhenOrderPlaced), clock.GetUtcNow().UtcDateTime);
        await db.SaveChangesAsync(ct);
    }
}

Title CopiesSold
----- ----------
Classic #1 2
Classic #2 1

This table is Catalog's data; it is unaffected if Ordering's column names change, because Catalog knows only the event's contract. The cost is eventual consistency: it is updated a few seconds after the order is placed. For a "best sellers" list that is more than enough. The CQRS (Command Query Responsibility Segregation) and Event Sourcing series covers read models from this angle in detail.

The third is views that modules publish for reporting. The finance report combines data from several modules at high volume and genuinely needs SQL. The solution here is for each module to publish a view for reporting through its own migration, with that view's columns acting as the module's reporting contract:

using Microsoft.EntityFrameworkCore.Migrations;

#nullable disable

namespace Bookstore.Ordering.Core.Persistence.Migrations
{
    /// <inheritdoc />
    public partial class ReportingView : Migration
    {
        // Ordering's reporting contract: order lines, independent of internal column names
        protected override void Up(MigrationBuilder migrationBuilder) =>
            migrationBuilder.Sql("""
                CREATE VIEW ordering.OrderLinesForReporting AS
                SELECT o.Id AS OrderId, o.PlacedAt, i.BookId, i.Quantity, i.UnitPrice
                FROM ordering.Orders o JOIN ordering.OrderItems i ON i.OrderId = o.Id;
                """);

        protected override void Down(MigrationBuilder migrationBuilder) =>
            migrationBuilder.Sql("DROP VIEW ordering.OrderLinesForReporting;");
    }
}

Catalog publishes catalog.BooksForReporting the same way. Reporting combines the two views in its own schema, and the reporting user can read only that schema:

/* Reporting: a separate, read-only schema that combines the views modules publish */
CREATE SCHEMA reporting;
GO
CREATE VIEW reporting.SalesByTitle AS
SELECT b.Title, SUM(l.Quantity) AS Copies, SUM(l.Quantity * l.UnitPrice) AS Revenue
FROM ordering.OrderLinesForReporting l
JOIN catalog.BooksForReporting b ON b.BookId = l.BookId
GROUP BY b.Title;
GO
CREATE USER reporting_reader WITHOUT LOGIN;
GRANT SELECT ON SCHEMA::reporting TO reporting_reader;

EXECUTE AS USER = 'reporting_reader';
SELECT Title, Copies, Revenue FROM reporting.SalesByTitle ORDER BY Title;
SELECT TOP 1 * FROM ordering.Orders;
REVERT;

Title Copies Revenue
----- ------ -------
Classic #1 2 20.00
Classic #2 1 10.00

(2 rows affected)
Msg 229, Level 14, State 5, Line 3
The SELECT permission was denied on the object 'Orders', database 'mm06', schema 'ordering'.

The report works and the reporting user cannot read module tables directly. Because the views live in schemas with the same owner (dbo), SQL Server's ownership chaining doesn't require separate permissions on the underlying tables. If the Catalog team splits the Title column, it has to update its own BooksForReporting view in the same migration; the reporting query doesn't change. The person who will notice the break is no longer the report owner but the module making the change.

Wanting a Single Transaction Across Modules

As long as we share a database, running two modules' DbContexts on the same DbConnection and the same transaction is technically possible; UseTransaction from the EF Core series post "Transactions in EF Core: SaveChanges, BeginTransaction and Savepoints" does that. But the need is usually a design signal. If two modules' data must be consistent at the same moment, atomically, either the two pieces of data are part of the same invariant and the boundary is drawn wrong, or the consistency can actually lag by a few seconds and an event is enough. Order and stock reservation are of the second kind: as we saw in post 05, they can be separated safely with an outbox and idempotent handlers. A single transaction across modules is a dependency that works today and becomes impossible the day you move a module into its own database.

Moving to a Database per Module

In this structure, moving from schema per module to database per module is a mechanical step: the module's connection string points to another database, its migrations are applied there, and its data is moved. The only thing that has to change in code is finding anything that still reaches that module's tables from another module. If schema permissions already prevent that, the answer is "nothing". Reporting views are the exception: combining views in different databases requires either a data warehouse or a separate reporting database fed by events. When this move makes sense is the subject of post 08.

Common Pitfalls

All entities in a single DbContext

The modules are separate in code, but BookstoreDbContext knows the entities of all of them. Any code using this context can reach any table, and EF Core's navigation properties quietly produce joins across modules. A context per module is the first step of the boundary on the database side.

Breaching isolation for reporting

"Let's give the reporting user SELECT on every schema." The day that decision is made, every module's table becomes reporting's contract and no module can change its columns freely. Offer reporting views published by modules, or a separate model fed by events.

Tying modules together with foreign keys

Adding a foreign key from ordering.OrderItems.BookId to catalog.Books.Id looks nice at database level, but it ties Catalog's ability to delete or move a book to Ordering and makes moving a module into a separate database impossible. References between modules are kept as ids without foreign keys; validation happens through a contract call.

Splitting the shared history table later

If you turn on MigrationsHistoryTable while the application is in production, EF Core sees the new table as empty and tries to apply every migration from the start. Copy the old records into the new tables before turning the setting on.

A Small Experiment

★☆☆ Try inserting a row into ordering.Orders as catalog_app. Which error code do you get? Then try writing to catalog.Sales.

★★☆ Write a migration that splits the Title column of Catalog's Books table into Title and Subtitle. In the same migration, update the BooksForReporting view so the reporting query doesn't change, and confirm that reporting.SalesByTitle returns the same result.

★★★ Move the Inventory module into a separate database (mm06_inventory): change only its connection string, apply its migration there and write a script that moves the data. Which other part of the application had to change? If the answer isn't "none", where was the boundary breached?

In this post we made each module's data its own and enforced the boundary in the database too. In the next post we set up the automated guards that keep these boundaries from eroding over time: module rules with ArchUnitNET, an MSBuild check, CODEOWNERS and integration tests that boot a single module on its own.