336 - Database · dotnetmadeeasy.com

247, 273, and 277 taught the repository pattern, query optimization, and connection pooling as separate tools. OrderFlow needs all three, on the same four tables, at the same time.

334 gave IOrderRepository a home in OrderFlow.Domain and a concrete implementation in OrderFlow.Infrastructure, but it deliberately left the interior of that repository — the actual schema, the actual queries, the actual connection behavior under load — for this lesson. That gap gets closed now: 247 already settled that OrderFlow should use per-aggregate repositories (IOrderRepository, IProductRepository, ICustomerRepository) instead of one generic IRepository<T>, 273 gave you the tools to keep those repositories' queries fast, and 277 gave you the knobs that keep the connections behind them healthy under real concurrent load.

This lesson doesn't re-teach any of those three — it applies them together to OrderFlow's actual four tables and its two real access patterns: the fast, narrow checkout write, and the wider, read-heavy order-detail and catalog-listing queries.

What Is It?

OrderFlow's schema is four tables, mapped from the entities 333 and 334 already fixed:

Table	Key columns	Relationship
Customers	Id, Email, Name	one Customer → many Orders
Products	Id, Name, Price, StockQuantity	one Product → many OrderItems
Orders	Id, CustomerId (FK), Status, PlacedAtUtc	one Order → many OrderItems
OrderItems	Id, OrderId (FK), ProductId (FK), Quantity, UnitPriceAtPurchase	the line-item join between an Order and a Product

247 already made the repository shape decision for OrderFlow: three narrow, entity-specific interfaces — IOrderRepository, IProductRepository, ICustomerRepository — each free to expose exactly the query its own entity needs, rather than one generic IRepository<T> straining to cover all four tables' very different access patterns.

Why Does It Exist?

OrderFlow has two data-access patterns that pull in opposite directions, and pretending one generic approach serves both is exactly the trap 247 warned about. Placing an order needs to be narrow and fast — insert one Order row and a handful of OrderItem rows, tracked, so EF Core's change tracker can save them together. Rendering an order's detail page needs to be wide and read-only — the order, its items, and each item's product name, none of which will ever be saved back. A single generic repository method can't be honestly tuned for both at once; that's precisely why IOrderRepository gets a distinct method for each shape of access, instead of one GetAll() that every caller bends to fit.

Big Picture — Two Access Patterns, Two Different Priorities

The Write Path — Placing an Order

Tracked entities — EF Core needs to detect and save the new Order and its OrderItems
Narrow — no unrelated data pulled along for the ride
Runs inside one DbContext lifetime scoped to the request
Sensitive to connection pool exhaustion under checkout traffic spikes

The Read Path — Order Detail, Catalog Listing

AsNoTracking() — nothing here is ever saved back
Wide, but shaped — .Include() or a projection, never an accidental N+1
The catalog listing, specifically, is read far more often than it's written — 337 picks this up directly
The order-detail read touches three related tables at once, a real AsSplitQuery() candidate

How It Works — EfOrderRepository's Real Methods

Simple Example — OrderFlowDbContext and EfOrderRepository

// ═══ OrderFlow.Infrastructure ═══
public class OrderFlowDbContext(DbContextOptions<OrderFlowDbContext> options) : DbContext(options)
{
    public DbSet<Customer> Customers => Set<Customer>();
    public DbSet<Product> Products => Set<Product>();
    public DbSet<Order> Orders => Set<Order>();
    public DbSet<OrderItem> OrderItems => Set<OrderItem>();
}

// IOrderRepository lives in OrderFlow.Domain (334) — narrow and entity-specific, per 247
public interface IOrderRepository
{
    Task AddAsync(Order order, CancellationToken ct);
    Task<Order?> GetByIdWithItemsAsync(Guid id, CancellationToken ct);
    Task<List<Order>> GetByCustomerIdAsync(Guid customerId, CancellationToken ct);
}

public class EfOrderRepository(OrderFlowDbContext db) : IOrderRepository
{
    // WRITE PATH — tracked, narrow, exactly what checkout needs to persist
    public async Task AddAsync(Order order, CancellationToken ct) =>
        await db.Orders.AddAsync(order, ct);

    // READ PATH — untracked, split to avoid a cartesian product across Items and their Products
    public Task<Order?> GetByIdWithItemsAsync(Guid id, CancellationToken ct) =>
        db.Orders
          .AsNoTracking()
          .Include(o => o.Items).ThenInclude(i => i.Product)
          .AsSplitQuery()
          .FirstOrDefaultAsync(o => o.Id == id, ct);

    public Task<List<Order>> GetByCustomerIdAsync(Guid customerId, CancellationToken ct) =>
        db.Orders.AsNoTracking().Where(o => o.CustomerId == customerId).ToListAsync(ct);
}

Meaning: Two methods on the same repository, two different EF Core postures — AddAsync stays tracked because it's about to be saved; the read methods go untracked and split because they're read-only and touch related tables. This is 247's "let each method be exactly as specific as the real query it serves" principle, applied literally.

Real-World Example — Sizing the Pool for OrderFlow's Actual Load

Say OrderFlow expects 500 concurrent HTTP requests at peak — a number that, per 277, badly overstates the connection pool size actually needed. Most of those requests are reading the product catalog (about to be served from cache, per 337) or checking an order's status; only a fraction are actively mid-flight on a database call at any given instant. Measuring the real number — concurrent, in-flight database operations, not concurrent requests — is what 277 insists on:

// appsettings.json connection string — sized against MEASURED concurrent DB operations, not raw request count
"Server=...;Database=OrderFlowDb;Min Pool Size=10;Max Pool Size=150;Connection Timeout=15;"

// Program.cs — AddDbContextPool considered, but deliberately NOT adopted yet:
// OrderFlowDbContext has no custom mutable state beyond its DbSets,
// so it would be low-risk — but 277 is explicit that it's a measured optimization,
// not a default. AddDbContext is the honest starting point.
builder.Services.AddDbContext<OrderFlowDbContext>(options =>
    options.UseSqlServer(builder.Configuration.GetConnectionString("OrderFlowDb")));

This is exactly 277's rule of thumb in action: trust the pool, size it against real measured concurrent database operations, and treat AddDbContextPool as something to adopt later, once construction overhead is actually measured as meaningful — not a reflexive first choice.

Analogy

A Mail Room With Two Different Counters

Picture a mail room with a drop-off counter and a pickup counter. Drop-off is fast and narrow — hand over exactly one package, get a receipt, done. Pickup is wider — someone might ask for everything addressed to them, sorted, with related paperwork attached. Nobody would staff both counters identically, or force every visitor through the same single window regardless of which they need. EfOrderRepository's AddAsync is the drop-off counter; GetByIdWithItemsAsync is the pickup counter — same mail room, two purpose-built counters.

Under the Hood — Why the Order Detail Read Specifically Needs AsSplitQuery()

An Order with 3 OrderItems, each referencing a Product, fetched with .Include(o => o.Items).ThenInclude(i => i.Product) as one single, non-split query produces one SQL join across three tables — and because a join multiplies rows, the Order's own columns get repeated once per item row in the result set. 273 named this precisely: it's not the N+1 problem (that's solved by using .Include() at all, rather than looping and re-querying), it's the separate cartesian-product cost of a single wide join. AsSplitQuery() issues one query for the order, a second for its items, and a third for their products — no duplicated columns, no join-multiplication — at the cost of no longer being a single atomic round-trip, a trade-off worth making here because the read is read-only and small in scope.

Common Confusion

1. "One IRepository<T> would have been simpler for OrderFlow's four tables" — simpler to write, harder to keep honest

A generic repository would compile fine for all four entities at first. But Orders' real query needs (split, related-product includes, customer-scoped filtering) and Products' real query needs (337's cache-aside pattern, no .Include() at all) are different enough that a shared generic interface would eventually either grow entity-specific methods bolted on, or leak IQueryable<T> back out to callers — exactly the failure mode 247 identified. Three narrow interfaces avoid that from the start.

2. "AsSplitQuery() fixes N+1" — it solves a different, related problem

N+1 is what happens with no eager loading at all — a loop that queries once per row. GetByIdWithItemsAsync already eager-loads with .Include(), so N+1 was never the risk here; the risk was the join-multiplication cost of doing that eager load as one single query, which is what AsSplitQuery() specifically addresses, per 273.

Common Mistakes

Mistake 1 — Using AsNoTracking() on the checkout write path "for consistency"

Applying AsNoTracking() to the new Order before calling SaveChangesAsync, out of habit from the read methods — EF Core then has nothing to detect as needing an insert. Only the read paths are untracked; anything about to be saved needs to stay tracked, exactly as AddAsync does above.

Mistake 2 — Sizing Max Pool Size off peak concurrent HTTP requests

Setting Max Pool Size to 500 because that's OrderFlow's peak concurrent request estimate — massively overshooting what's actually needed, and risking overwhelming the database server itself. Measure real concurrent, in-flight database operations (typically far lower than concurrent requests, since most requests aren't touching the database at every instant) and size against that, per 277.

Mistake 3 — Letting IOrderRepository grow a generic Find(predicate) method "just in case"

Adding Task<List<Order>> Find(Expression<Func<Order, bool>> predicate) to cover future, not-yet-needed queries — this quietly reopens exactly the IQueryable-leaking trap 247 warned about, just with extra steps. Add a new, precisely-named method (GetCancelledOrdersOlderThanAsync, say) when a real, specific need shows up — not a generic escape hatch in advance.

When Should I Use It?

Mental Model

Four tables = Customers, Products, Orders, OrderItems.
Three repositories = IOrderRepository, IProductRepository, ICustomerRepository — narrow, entity-specific, per 247.
Write path = tracked, narrow — exactly what SaveChangesAsync needs.
Read path = AsNoTracking(), split where more than one collection is included, per 273.
Connection pool = sized against measured concurrent database operations, per 277 — not raw request count.

Remember: these repositories live in OrderFlow.Infrastructure, implementing interfaces OrderFlow.Domain owns — the layering 334 already set up.

Key Takeaway

OrderFlow's schema is four tables — Customers, Products, Orders, OrderItems — accessed through three narrow, entity-specific repositories per 247's guidance, not one generic IRepository<T>.
The write path (placing an order) stays tracked and narrow; the read path (order detail, order history) goes AsNoTracking(), with AsSplitQuery() avoiding a cartesian-product join across an order's items and their products, per 273.
The connection pool is sized against measured concurrent database operations, not peak concurrent HTTP requests — per 277, the two numbers are usually very different, and the second badly overshoots.
AddDbContextPool is deliberately not adopted yet — it's a measured optimization for later, not a default starting choice.
This schema and these repositories are what 337's caching sits in front of for the product catalog, and what 338's background pipeline reads from and writes to once a checkout moves past the synchronous request.

Check Your Understanding

You've seen OrderFlow's schema and its two data-access patterns. Let's confirm the reasoning behind each design choice.

1. Why does EfOrderRepository.AddAsync leave the new Order tracked, while GetByIdWithItemsAsync uses AsNoTracking()?

A) It's an inconsistency that should be fixed by using the same setting everywhere
B) AddAsync is about to be saved via SaveChangesAsync, so EF Core's change tracker needs to know about it; GetByIdWithItemsAsync is read-only, so tracking overhead is pure waste
C) AsNoTracking() only works on Guid primary keys, which Order doesn't use for reads
D) Tracking is determined by which controller calls the method, not by the repository itself

Show answer

Correct: B

Why B is correct: This is exactly the write-path/read-path distinction the lesson draws — tracking is required for data about to be saved, and pure overhead for data that will only ever be read.

Why A is incorrect: This is a deliberate, correct distinction, not an inconsistency to be "fixed" — using AsNoTracking() on the write path would actually break saving the new order.

Why C is incorrect: AsNoTracking() works regardless of primary key type — this isn't a real constraint.

Why D is incorrect: Tracking behavior is set inside the repository method itself, via the query's own configuration — it doesn't depend on which caller happens to invoke it.

Reinforcement: Track what you're about to save; skip tracking for anything read-only.

2. A teammate proposes sizing OrderFlow's Max Pool Size to match the 500 peak concurrent HTTP requests the API expects. What does this lesson say about that approach?

A) It's correct — Max Pool Size should always equal peak concurrent requests exactly
B) It likely badly overshoots real need — per 277, pool size should reflect measured concurrent in-flight database operations, which is normally far lower than concurrent HTTP requests, since most requests aren't actively querying the database at every instant
C) It's irrelevant, since OrderFlow uses AddDbContextPool instead of a connection pool
D) Max Pool Size has no measurable effect on application behavior either way

Show answer

Correct: B

Why B is correct: This is 277's core guidance, applied directly — pool size belongs against measured concurrent database operations, not raw request counts, which this lesson's Real-World Example walks through explicitly for OrderFlow's own traffic shape.

Why A is incorrect: This is precisely the mistake the lesson warns against — it conflates two different, usually very different numbers.

Why C is incorrect: The lesson explicitly does NOT adopt AddDbContextPool for OrderFlow yet, and even if it did, that pools DbContext instances, not the underlying ADO.NET connection pool — the two are separate, layered mechanisms per 277.

Why D is incorrect: An undersized pool causes real wait-timeout exceptions under load; an oversized one can genuinely overwhelm the database server — this has real, measurable consequences either way.

Reinforcement: Concurrent requests and concurrent in-flight database operations are different numbers — size the pool against the second one.

3. Why does this lesson prefer three narrow repositories (IOrderRepository, IProductRepository, ICustomerRepository) over one generic IRepository<T> for OrderFlow?

A) Because generic interfaces are not supported for Entity Framework Core entities
B) Because Orders and Products have genuinely different real query needs — split, related-product includes for one; a simple cache-aside read for the other — and a shared generic interface would eventually either grow bolted-on methods or leak IQueryable<T>, per 247
C) Because OrderFlow only has four tables, which is too few to justify a generic interface
D) Because Clean Architecture (334) forbids using generic interfaces anywhere in the Domain layer

Show answer

Correct: B

Why B is correct: Common Confusion #1 states this directly — the different real access patterns each entity needs are exactly what makes a shared generic interface eventually strain and leak, the specific failure mode 247 already identified.

Why A is incorrect: Generic repositories over EF Core entities work perfectly well technically — lesson 247 itself demonstrates one; the issue here is a design trade-off, not a technical limitation.

Why C is incorrect: Table count isn't the deciding factor — query shape diversity is; four tables with genuinely different access patterns is exactly the scenario that favors narrow interfaces.

Why D is incorrect: The Dependency Rule from 334 is about which direction references point, not about generic vs. non-generic interface design — this isn't a Clean Architecture constraint at all.

Reinforcement: The deciding factor is whether entities genuinely need different query shapes — when they do, narrow interfaces avoid the generic-repository strain 247 described.

OrderFlow's data access is real now — tracked where it needs to be, split where it needs to be, pooled against real load. Next: 337 puts a cache in front of the product catalog, the read this lesson's schema already flagged as the busiest one in the system.