API Demo: bad design vs. good design

A deliberately junior-quality ASP.NET Core Web API against a realistic e-commerce schema. It answers one question with real k6 numbers instead of a hand-wave: what actually happens under load when an API is built without ever load-testing it?

• Sync-over-async — `.Result` instead of `await`, `.ToList()` instead of `.ToListAsync()` in the hot paths.
• N+1 queries — Lazy-loaded navigation properties everywhere, no `.Include()` calls at all.
• Over-fetching — Every endpoint serializes full EF entity graphs directly — no DTOs, no projection.
• No pagination — `GET /api/orders` and `GET /api/products` return the entire table, every single call.
• Index-hostile queries — `?year=2024` compares `OrderDate.Year` in a way that defeats the existing index; `?search=` filters after `.ToList()` has already pulled the whole table into memory.
• DbContext as a Singleton — EF Core's DbContext is not thread-safe. This is the single biggest reason the API falls over under concurrent load.
• Connection pooling disabled — `Pooling=False` in the connection string — every lazy-load query opens a brand-new, unpooled connection.
• No caching — Not even for near-static reference data like categories and products.
• No rate limiting — Nothing throttles clients or applies backpressure as load increases.
• Chatty surface — Order detail forces a database round trip per related entity — order, then customer, then items, then a product lookup per item.

Verified: Verified against a dev-scale database (2k customers / 10k orders) on a single unloaded connection: GET /api/orders took ~230 seconds to stream ~6.7MB before the SQL client itself threw a Connection Timeout Expired error mid-response. Pooling=False plus a Singleton DbContext means every one of the thousands of lazy-load queries this endpoint triggers opens a new connection, and eventually the database stops keeping up. That failure is real and reproducible, not staged for effect.

The same conceptual schema, built the way a senior developer would insist on: layered (Domain -> Application -> Infrastructure -> Api, dependencies only ever pointing inward), test-first, and optimized specifically against the anti-patterns the bad-api repo demonstrates.

• Pagination everywhere — OFFSET/FETCH paging in the Dapper read repositories — never loads more rows than a page needs.
• Flat DTO projections — No entity graphs on the read path. Dapper projects straight into DTOs, so there's no N+1 to trigger in the first place.
• Order detail as one query — Header and line items come back from a single multi-result query (QueryMultipleAsync), not N+1 per line item.
• Index-friendly queries — OrderDate is indexed, and the queries are written to avoid wrapping it in a function that would defeat that index.
• Pooled DbContext — AddDbContextPool avoids both per-request allocation churn and the Singleton misuse the bad-api repo demonstrates.
• Caching via decorator — An in-memory cache wraps the order read repository (Open/Closed Principle) without either class knowing about the other's concern, plus 10-second server-side output caching.
• Rate limiting — A per-IP token-bucket limiter returns 429 instead of piling up unbounded load.
• Response compression — Brotli/Gzip enabled for both HTTP and HTTPS.

Verified: Every layer is covered by tests written before the code that makes them pass: unit tests for the query handlers against mocked repositories, integration tests running the Dapper repositories against a real SQL Server container (Testcontainers), and architecture tests that mechanically enforce the dependency-direction rules above — Domain cannot depend on Infrastructure or EF Core, Application cannot depend on Infrastructure.