dotnet-data-access-strategy

What this skill does

# dotnet-data-access-strategy

Decision framework for choosing between Entity Framework Core, Dapper, and raw ADO.NET in .NET applications. Covers performance tradeoffs, feature comparisons, AOT/trimming compatibility, hybrid approaches, and migration paths. Use this skill to make an informed technology decision before writing data access code.

**Out of scope:** Tactical EF Core usage (DbContext lifecycle, migrations, interceptors) is covered in [skill:dotnet-efcore-patterns]. Strategic EF Core architecture (read/write split, aggregate boundaries, repository policy) is covered in [skill:dotnet-efcore-architecture]. DI container mechanics -- see [skill:dotnet-csharp-dependency-injection]. Async patterns -- see [skill:dotnet-csharp-async-patterns]. Testing data access layers -- see [skill:dotnet-integration-testing] for database fixture and Testcontainers patterns. CI/CD pipelines -- see [skill:dotnet-gha-patterns] and [skill:dotnet-ado-patterns].

Cross-references: [skill:dotnet-efcore-patterns] for tactical EF Core usage, [skill:dotnet-efcore-architecture] for strategic EF Core patterns, [skill:dotnet-csharp-dependency-injection] for service registration, [skill:dotnet-csharp-async-patterns] for async query patterns.

---

## Decision Matrix

| Factor | EF Core | Dapper | Raw ADO.NET |
|--------|---------|--------|-------------|
| **Learning curve** | Moderate (LINQ, migrations, config) | Low (SQL + mapping) | Low-moderate (SQL + manual mapping) |
| **Productivity** | High (change tracking, migrations, scaffolding) | Moderate (write SQL, auto-map) | Low (everything manual) |
| **Query performance** | Good with projections; overhead from tracking | Near-ADO.NET performance | Fastest possible |
| **Startup time** | Higher (model building, compilation) | Minimal | Minimal |
| **Memory allocation** | Higher (change tracker, proxy objects) | Low (direct mapping) | Lowest |
| **AOT/trimming** | Limited (reflection-heavy, improving) | Good with source generators | Full support |
| **Change tracking** | Built-in | None | None |
| **Migrations** | Built-in | None (use FluentMigrator, DbUp, etc.) | None |
| **LINQ support** | Full (translated to SQL) | None (raw SQL) | None (raw SQL) |
| **Batch operations** | `ExecuteUpdate`/`ExecuteDelete` (EF Core 7+) | Manual batching | Manual batching |
| **Complex mappings** | Excellent (owned types, TPH/TPT/TPC) | Simple POCO mapping | Manual |

---

## When to Choose Each

### Choose EF Core When

- Building CRUD applications with standard domain models
- You need change tracking and automatic dirty detection
- You want schema migrations managed in code
- Your team prefers LINQ over raw SQL
- You are building with .NET Aspire (EF Core has first-class Aspire integration)
- Query performance is acceptable with projections and `AsNoTracking()`

```csharp
// EF Core: expressive, type-safe, with change tracking
var order = await db.Orders
    .Include(o => o.Items)
    .FirstOrDefaultAsync(o => o.Id == orderId, ct);

order!.Status = OrderStatus.Shipped;
await db.SaveChangesAsync(ct); // Automatic dirty detection
```

### Choose Dapper When

- Performance is critical and you need control over SQL
- You are writing complex queries (reporting, analytics, multi-join)
- You need thin data access with minimal abstraction
- Your team is comfortable writing and maintaining SQL
- You need AOT compatibility today (with Dapper.AOT source generator)

```csharp
// Dapper: direct SQL, minimal overhead
await using var connection = new NpgsqlConnection(connectionString);

var orders = await connection.QueryAsync<OrderDto>(
    """
    SELECT o.id, o.customer_id, o.status, o.created_at,
           COUNT(i.id) AS item_count,
           SUM(i.quantity * i.unit_price) AS total
    FROM orders o
    LEFT JOIN order_items i ON i.order_id = o.id
    WHERE o.customer_id = @CustomerId
    GROUP BY o.id, o.customer_id, o.status, o.created_at
    ORDER BY o.created_at DESC
    LIMIT @PageSize
    """,
    new { CustomerId = customerId, PageSize = pageSize });
```

### Choose Raw ADO.NET When

- Maximum performance is non-negotiable (sub-millisecond data access)
- You need full control over connection, command, and reader lifecycle
- You are building a library or framework (no app-level dependencies)
- AOT compatibility is required and no source generators are acceptable
- You are working with stored procedures or database-specific features

```csharp
// Raw ADO.NET: full control, zero abstraction overhead
await using var connection = new NpgsqlConnection(connectionString);
await connection.OpenAsync(ct);

await using var command = connection.CreateCommand();
command.CommandText = "SELECT id, name, price FROM products WHERE category_id = $1";
command.Parameters.AddWithValue(categoryId);

await using var reader = await command.ExecuteReaderAsync(ct);
var products = new List<ProductDto>();

while (await reader.ReadAsync(ct))
{
    products.Add(new ProductDto
    {
        Id = reader.GetInt32(0),
        Name = reader.GetString(1),
        Price = reader.GetDecimal(2)
    });
}
```

---

## Performance Comparison

Approximate overhead per query (relative to raw ADO.NET baseline):

| Operation | ADO.NET | Dapper | EF Core (NoTracking) | EF Core (Tracking) |
|-----------|---------|--------|----------------------|-------------------|
| Simple SELECT by PK | 1x | ~1.05x | ~1.3x | ~1.5x |
| SELECT 100 rows | 1x | ~1.1x | ~1.4x | ~2x |
| INSERT single row | 1x | ~1.1x | ~1.5x | ~2x |
| Complex JOIN query | 1x | ~1.05x | ~1.3-2x (depends on LINQ translation) | ~1.5-2.5x |

**Notes:**
- These are rough relative comparisons -- actual numbers depend on query complexity, database, network latency, and hardware.
- Network latency to the database typically dwarfs ORM overhead. A 1ms query with 5ms network latency is 6ms regardless of ORM.
- EF Core with `Select()` projections and `AsNoTracking()` approaches Dapper performance for most queries.
- Measure your actual workload before choosing based on performance alone.

---

## AOT and Trimming Compatibility

### EF Core

EF Core relies heavily on reflection for model building, change tracking, and query translation. AOT compatibility is improving but not complete:

| Feature | AOT Status (.NET 9+) |
|---------|---------------------|
| Model building | Partial -- requires compiled model (`dotnet ef dbcontext optimize`) |
| Query translation | Not AOT-safe (expression tree compilation) |
| Change tracking | Not AOT-safe (proxy generation, snapshot creation) |
| Migrations | Design-time only -- not needed at runtime |

**Compiled models** pre-generate the model configuration at build time, reducing startup cost and improving trim-friendliness:

```bash
dotnet ef dbcontext optimize \
    --project src/MyApp.Infrastructure \
    --startup-project src/MyApp.Api \
    --output-dir CompiledModels
```

```csharp
options.UseNpgsql(connectionString)
       .UseModel(AppDbContextModel.Instance);  // Pre-compiled model
```

**Bottom line:** EF Core Native AOT support is partial and version-dependent. As of .NET 9, compiled models improve startup and trim-friendliness, but query translation and change tracking still rely on runtime code generation. Check the [current limitations](https://learn.microsoft.com/en-us/ef/core/performance/advanced-performance-topics#compiled-models) for your target version before committing to EF Core in an AOT deployment. Use compiled models to improve startup time where possible, but plan for Dapper.AOT or ADO.NET fallbacks on AOT-critical paths.

### Dapper

Dapper traditionally uses runtime reflection and emit for POCO mapping. The `Dapper.AOT` source generator provides a trim- and AOT-compatible alternative:

| Package | AOT Status |
|---------|-----------|
| `Dapper` (standard) | Not AOT-safe (uses Reflection.Emit) |
| `Dapper.AOT` | AOT-safe (source-generated mappers) |

```xml
<PackageReference Include="Dapper" Version="2.*" />
<PackageReference Include="Dapper.AOT" Version="1.*" />
```

```