Database access patterns for performance. Separate read/write models, avoid N+1 queries, use AsNoTracking, apply row limits, and never do application-side joins. Works with EF Core and Dapper.

Database Performance Patterns When to Use This Skill Use this skill when: Designing data access layers Optimizing slow database queries Choosing between EF Core and Dapper Avoiding common performance pitfalls Core Principles 1. Separate read and write models Don't use the same types for both 2. Think in batches Avoid N+1 queries 3. Only retrieve what you need No SELECT 4. Apply row limits Always have a configurable Take/Limit 5. Do joins in SQL Never in application code 6. AsNoTracking for reads EF Core change tracking is expensive Read/Write Model Separation (CQRS Pattern) Read and write models are fundamentally different they have different shapes, columns, and purposes. Don't create a single "User" entity and reuse it everywhere. Read models are denormalized, optimized for query efficiency, and return multiple projection types (UserProfile, UserSummary, UserDetailForAdmin) Write models are normalized, validation focused, and accept strongly typed commands (CreateUserCommand, UpdateUserCommand) Architecture Read Store Interface Write Store Interface Key structural differences illustrated: Read store returns multiple different DTOs (UserProfile, UserSummary, bool flag) Write store returns minimal data (just UserId on create) or void Read queries are stateless projections no tracking needed Write operations focus on command validation, not retrieving data afterwards Different databases/tables can back read vs write (eventual consistency pattern) Always Apply Row Limits Never return unbounded result sets. Every read method should have a configurable limit. Pattern: Limit Parameter EF Core with Pagination AsNoTracking for Read Queries EF Core's change tracking is expensive. Disable it for read only queries. Configure Default Behavior Then explicitly enable tracking when needed: Avoid N+1 Queries The N+1 problem: fetching a list, then querying for each item's related data. The Problem Solution 1: Include (EF Core) Solution 2: Batch Query (Dapper) Never Do Application Side Joins Joins must happen in SQL, not in C . Avoid Cartesian Explosions Multiple Include calls can cause Cartesian products. Solution: Split Queries Solution: Explicit Projection Constrain Column Sizes Define maximum lengths in your EF Core model to prevent oversized data. Don't Build Generic Repositories Generic repositories hide query complexity and make optimization difficult. Problems with generic repositories: Can't optimize specific queries No way to enforce limits Hide N+1 problems Make it easy to fetch too much data Encourage lazy thinking about data access Dapper for Read Heavy Workloads For complex read queries, Dapper with explicit SQL is often cleaner and faster. When to Use EF Core vs Dapper Scenario Recommendation Simple CRUD EF Core Complex read queries Dapper Writes with validation EF Core Bulk operations Dapper or raw SQL Reporting/analytics Dapper Domain heavy writes EF Core You can use both in the same project EF Core for writes, Dapper for reads. Quick Reference Anti Pattern Solution No row limit Add limit parameter to every read method SELECT Project only needed columns N+1 queries Use Include or batch queries Application joins Do joins in SQL Cartesian explosion Use AsSplitQuery or projection Tracking read only data Use AsNoTracking Generic repository Purpose built read/write stores Unbounded strings Configure MaxLength in model Resources EF Core Performance : https://learn.microsoft.com/en us/ef/core/performance/ Dapper : https://github.com/DapperLib/Dapper AsSplitQuery : https://learn.microsoft.com/en us/ef/core/querying/single split queries

database-performance