dotnet-data-access-strategy

Imported from wshaddix/dotnet-skills (skills/dotnet-data-access-strategy/SKILL.md). Install upstream with npx skills add wshaddix/dotnet-skills --skill dotnet-data-access-strategy. Copyright stays with the author.

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

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

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)

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

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

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:

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

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

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

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

// Dapper.AOT generates the mapping code at compile time
// Usage is the same as standard Dapper -- the source generator intercepts calls

[DapperAot]  // Attribute enables AOT generation for this class
public sealed class OrderRepository(NpgsqlDataSource dataSource)
{
    public async Task<OrderDto?> GetByIdAsync(int id, CancellationToken ct)
    {
        await using var connection = await dataSource.OpenConnectionAsync(ct);
        return await connection.QuerySingleOrDefaultAsync<OrderDto>(
            "SELECT id, customer_id, status FROM orders WHERE id = @Id",
            new { Id = id });
    }
}

Raw ADO.NET

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

AOT Decision Guide

Hybrid Approaches

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

EF Core for Commands, Dapper for Queries

// Command: use EF Core for change tracking and validation
public sealed class CreateOrderHandler(WriteDbContext db)
{
    public async Task<int> HandleAsync(CreateOrderCommand command, CancellationToken ct)
    {
        var order = new Order(command.CustomerId);
        // ... business logic ...
        db.Orders.Add(order);
        await db.SaveChangesAsync(ct);
        return order.Id;
    }
}

// Query: use Dapper for complex read-only queries
public sealed class OrderReportHandler(NpgsqlDataSource dataSource)
{
    public async Task<IReadOnlyList<OrderReportRow>> HandleAsync(
        OrderReportQuery query,
        CancellationToken ct)
    {
        await using var connection = await dataSource.OpenConnectionAsync(ct);
        var rows = await connection.QueryAsync<OrderReportRow>(
            """
            SELECT
                date_trunc('day', o.created_at) AS day,
                COUNT(*) AS order_count,
                SUM(i.quantity * i.unit_price) AS revenue
            FROM orders o
            JOIN order_items i ON i.order_id = o.id
            WHERE o.created_at >= @StartDate AND o.created_at < @EndDate
            GROUP BY date_trunc('day', o.created_at)
            ORDER BY day
            """,
            new { query.StartDate, query.EndDate });
        return rows.AsList();
    }
}

Sharing the Database Connection

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

public async Task ProcessWithBothAsync(int orderId, CancellationToken ct)
{
    var connection = db.Database.GetDbConnection();
    await db.Database.OpenConnectionAsync(ct);

    await using var transaction = await db.Database.BeginTransactionAsync(ct);

    // EF Core operation
    var order = await db.Orders.FindAsync([orderId], ct);
    order!.Status = OrderStatus.Processing;
    await db.SaveChangesAsync(ct);

    // Dapper operation on the same connection and transaction
    await connection.ExecuteAsync(
        """
        INSERT INTO audit_log (entity_type, entity_id, action, timestamp)
        VALUES (@Type, @Id, @Action, @Timestamp)
        """,
        new { Type = "Order", Id = orderId, Action = "StatusChange",
              Timestamp = DateTimeOffset.UtcNow },
        transaction: transaction.GetDbTransaction());

    await transaction.CommitAsync(ct);
}

NpgsqlDataSource Registration

When using Dapper with PostgreSQL, register NpgsqlDataSource as a singleton in DI (it manages connection pooling internally):

builder.Services.AddNpgsqlDataSource(
    builder.Configuration.GetConnectionString("DefaultConnection")!);

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

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

Migration Paths

From Raw ADO.NET to Dapper

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

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

From Dapper to EF Core

1. Add EF Core packages and create a DbContext with entity configurations.
2. Generate initial migration from existing database: dotnet ef dbcontext scaffold.
3. Gradually replace Dapper queries with EF Core in new features.
4. Keep Dapper for complex reporting queries -- hybrid is fine.

From EF Core to Dapper/ADO.NET (Performance-Critical Paths)

1. Identify hot paths via profiling (OpenTelemetry traces, database query stats).
2. Replace specific queries with Dapper, sharing the same connection.
3. Keep EF Core for CRUD operations that benefit from change tracking.

Package Reference

Key Principles

• Choose based on your actual needs -- not on performance benchmarks. Network latency to the database dwarfs ORM overhead for most applications.
• EF Core is the default choice for .NET applications -- it provides productivity, safety, and migrations. Optimize with Dapper when profiling identifies specific bottlenecks.
• Hybrid is the pragmatic answer -- use EF Core for commands and Dapper for complex queries. They share connections and transactions.
• AOT compatibility matters if you need it -- if publishing AOT is a hard requirement today, use Dapper.AOT or raw ADO.NET. EF Core AOT support is improving but incomplete.
• Do not prematurely optimize -- start with EF Core, use AsNoTracking() and Select() projections, and measure before introducing Dapper.
• Migrations are a real productivity feature -- if you choose Dapper, plan your migration strategy separately (FluentMigrator, DbUp, or manual scripts).

Agent Gotchas

1. Do not recommend Dapper purely for performance without first checking whether EF Core with AsNoTracking() and Select() projections meets the performance requirement. The difference is often negligible when EF Core is used correctly.
2. Do not use standard Dapper in AOT-published applications -- it uses Reflection.Emit which is not AOT-compatible. Use Dapper.AOT with the [DapperAot] attribute for AOT scenarios.
3. Do not forget to list required NuGet packages -- both EF Core providers (e.g., Npgsql.EntityFrameworkCore.PostgreSQL) and Dapper packages must be explicitly referenced. Agents that generate code without package references produce non-compiling projects.
4. Do not create new NpgsqlConnection instances manually in DI-registered services -- use NpgsqlDataSource (registered via AddNpgsqlDataSource()) which manages connection pooling. Creating connections manually bypasses pool management.
5. Do not mix EF Core and Dapper on separate connections within the same logical transaction -- use DbContext.Database.GetDbConnection() to share the connection and transaction.GetDbTransaction() to share the transaction.
6. Do not assume EF Core LINQ translates all C# expressions to SQL -- unsupported expressions silently evaluate client-side in older versions or throw in newer versions. Check the generated SQL with ToQueryString() during development.

References

• EF Core performance best practices
• EF Core compiled models
• Dapper documentation
• Dapper.AOT
• NpgsqlDataSource and dependency injection
• ADO.NET overview
• Native AOT deployment