dotnet-data-access-strategy

name	dotnet-data-access-strategy
description	Chooses a data access approach. EF Core vs Dapper vs ADO.NET decision matrix, tradeoffs.
allowed-tools	["Read","Grep","Glob","Bash","Write","Edit"]

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.

Scope

• EF Core vs Dapper vs ADO.NET decision matrix
• Performance and feature tradeoffs across data access approaches
• AOT/trimming compatibility comparison
• Hybrid approach patterns and migration paths

Out of scope

• Tactical EF Core usage (DbContext lifecycle, migrations, interceptors) -- see [skill:dotnet-efcore-patterns]
• Strategic EF Core architecture (read/write split, aggregate boundaries) -- see [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]

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()

// 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

```text

### 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 });

```text

### 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  no source generators are acceptable
- You are working  stored procedures  database-specific features

```csharp


   connection =  NpgsqlConnection(connectionString);
 connection.OpenAsync(ct);

   command = connection.CreateCommand();
command.CommandText = ;
command.Parameters.AddWithValue(categoryId);

   reader =  command.ExecuteReaderAsync(ct);
 products =  List<ProductDto>();

 ( reader.ReadAsync(ct))
{
    products.Add( ProductDto
    {
        Id = reader.GetInt32(),
        Name = reader.GetString(),
        Price = reader.GetDecimal()
    });
}

```text

---



;  

```csharp

**Bottom line:** EF Core Native AOT support    version-dependent. As of .NET , compiled models improve
startup  trim-friendliness, but query translation  change tracking still rely  runtime code generation. Check
the
[](https:
 your target version before committing to EF Core  an AOT deployment. Use compiled models to improve startup time
 possible, but plan  Dapper.AOT  ADO.NET fallbacks  AOT-critical paths.



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

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

```xml

<PackageReference Include= Version= />
<PackageReference Include= Version= />

```xml

```csharp




[]  

{
      Task<OrderDto?> GetByIdAsync( id, CancellationToken ct)
    {
           connection =  dataSource.OpenConnectionAsync(ct);
          connection.QuerySingleOrDefaultAsync<OrderDto>(
            ,
             { Id = id });
    }
}

```text



Full AOT  trimming support. No reflection, no code generation -- all mapping  .



| Requirement                             | Recommendation                                   |
| --------------------------------------- | ------------------------------------------------ |
| Must publish AOT today                  | Dapper.AOT  raw ADO.NET                        |
| Prefer ORM, AOT              | EF Core                                          |
| Prefer ORM, AOT planned  future      | EF Core now, evaluate AOT support  it improves |
| Building a library consumed  AOT apps | Raw ADO.NET  Dapper.AOT                        |

---



Most production applications benefit   multiple data access technologies. EF Core  Dapper can coexist  the
same project, sharing the same database connection.



```csharp



{
    
    {
         order =  Order(command.CustomerId);
        
        db.Orders.Add(order);
         db.SaveChangesAsync(ct);
         order.Id;
    }
}



{
      Task<IReadOnlyList<OrderReportRow>> HandleAsync(
        OrderReportQuery query,
        CancellationToken ct)
    {
           connection =  dataSource.OpenConnectionAsync(ct);
         rows =  connection.QueryAsync<OrderReportRow>(
            ,
             { query.StartDate, query.EndDate });
         rows.AsList();
    }
}

```text



Use `DbContext.Database.GetDbConnection()` to  the underlying `DbConnection`  Dapper queries within an EF Core
transaction:

```
{
     connection = db.Database.GetDbConnection();
     db.Database.OpenConnectionAsync(ct);

       transaction =  db.Database.BeginTransactionAsync(ct);

    
     order =  db.Orders.FindAsync([orderId], ct);
    order!.Status = OrderStatus.Processing;
     db.SaveChangesAsync(ct);

    
     connection.ExecuteAsync(
        ,
         { Type = , Id = orderId, Action = ,
              Timestamp = DateTimeOffset.UtcNow },
        transaction: transaction.GetDbTransaction());

     transaction.CommitAsync(ct);
}

```text



When  Dapper  PostgreSQL, register `NpgsqlDataSource` ;

```csharp

The `Npgsql.DependencyInjection` package provides `AddNpgsqlDataSource()`. This also integrates  EF Core --
`UseNpgsql()` can accept the registered data source:

```csharp

builder.Services.AddDbContext<AppDbContext>((sp, options) =>
    options.UseNpgsql(sp.GetRequiredService<NpgsqlDataSource>()));

```csharp

---





Dapper wraps `IDbConnection` extension methods around existing ADO.NET code. Migration  incremental:

 Replace `DbDataReader` loops  `QueryAsync<T>()` calls.
 Replace `command.Parameters.AddWithValue()`  anonymous objects.
 No schema changes, no  dependencies beyond the `Dapper` NuGet package.



 Add EF Core packages  create a `DbContext`  entity configurations.
 Generate initial migration  existing database: `dotnet ef dbcontext scaffold`.
 Gradually replace Dapper queries  EF Core   features.
 Keep Dapper  complex reporting queries -- hybrid  fine.