Choosing a Data Access Approach

Pick EF Core, Dapper or raw Npgsql per workload, and run them together on one connection and one transaction when a use case needs both.

Syntax

using var tx = await db.Database.BeginTransactionAsync(ct);
await db.Database.GetDbConnection().ExecuteAsync(new CommandDefinition(
sql, args, tx.GetDbTransaction(), cancellationToken: ct));
await db.SaveChangesAsync(ct);
await tx.CommitAsync(ct);

No single library is right for every query in a service. Decide per workload: what does this code need from the database, and what will the team have to maintain?

Per workload

Workload	Choose	Why
Commands on an aggregate (place order, change address)	EF Core	Change tracking, relationships, concurrency tokens, one `SaveChanges` transaction
Set-based updates and deletes	EF Core `ExecuteUpdate`/`ExecuteDelete`	No loading, still typed
Read models, reports, search pages	Dapper or EF Core `SqlQuery`	Hand-written SQL mapped to DTOs
Bulk load, streaming export, LISTEN/NOTIFY	Npgsql directly	COPY and protocol features no abstraction exposes
Schema	EF Core migrations, or a SQL migration tool	Exactly one owner

The defensible default: EF Core owns the schema and the write side; queries EF translates badly, or that a DBA tuned, go through Dapper against the same database. A service that is all reporting can skip EF Core. Before adding Dapper for a handful of queries, check db.Database.SqlQuery<T>($"..."): it maps raw SQL to unmapped DTO types with interpolated values sent as parameters, which covers many read cases without a second library. What teams regret is a hand-rolled Dapper repository for plain CRUD, re-implementing what EF Core gives for free, or LINQ contortions to make EF Core emit what is three lines of SQL.

One connection, one transaction

When EF Core and Dapper must commit together, use EF Core's connection. db.Database.GetDbConnection() returns the DbConnection the context uses; after BeginTransactionAsync, tx.GetDbTransaction() is the ADO.NET transaction for Dapper's transaction argument, and SaveChangesAsync joins the open transaction instead of starting its own. The reverse works too: open an NpgsqlConnection, begin a transaction, and give both to EF Core with UseNpgsql(connection) and db.Database.UseTransactionAsync(tx). EF Core closes only connections it opened itself.

Pass the transaction to Dapper even on PostgreSQL. Npgsql runs every command inside the connection's current transaction anyway, but SqlClient throws ExecuteReader requires the command to have a transaction when it is missing, so the habit keeps code portable and explicit.

What goes wrong

Retries: with EnableRetryOnFailure, the shared transaction must run inside CreateExecutionStrategy().ExecuteAsync, as the example does; the Transactions lesson covers the rules.
Stale tracking: rows changed through Dapper or ExecuteUpdate are not refreshed in entities the context already tracks. Query after the write, or clear the tracker.
Two pools: a separate connection string for Dapper creates a second pool and budget. Build one NpgsqlDataSource and pass it to EF Core with UseNpgsql(dataSource), so type mappings are shared too.
Two writers: one table written from two styles drifts. Keep each table's writes in one place.

Example

using Dapper;
using Microsoft.AspNetCore.Http.HttpResults;
using Microsoft.EntityFrameworkCore;
using Microsoft.EntityFrameworkCore.Storage;
using Npgsql;

var builder = WebApplication.CreateBuilder(args);

// One data source shared by EF Core and Dapper: one pool and the same type mappings.
var dataSource = new NpgsqlDataSourceBuilder(builder.Configuration.GetConnectionString("Shop")
    ?? throw new InvalidOperationException("ConnectionStrings:Shop is not configured.")).Build();
builder.Services.AddSingleton(dataSource);
builder.Services.AddDbContext<ShopDb>(options =>
    options.UseNpgsql(dataSource, npgsql => npgsql.EnableRetryOnFailure()));

var app = builder.Build();

app.MapPost("/orders", async Task<Results<Created<OrderCreated>, Conflict<string>>> (
    PlaceOrder request, ShopDb db, TimeProvider clock, CancellationToken ct) =>
{
    var strategy = db.Database.CreateExecutionStrategy();
    var order = await strategy.ExecuteAsync(async () =>
    {
        db.ChangeTracker.Clear();
        // A rerun after a commit whose acknowledgement was lost finds the order and stops here.
        var existing = await db.Orders.AsNoTracking()
            .FirstOrDefaultAsync(o => o.IdempotencyKey == request.IdempotencyKey, ct);
        if (existing is not null) return existing;

        await using var tx = await db.Database.BeginTransactionAsync(ct);
        var connection = db.Database.GetDbConnection();

        // Dapper runs the statement once per line and returns the summed row count.
        // Sorted by SKU so concurrent orders lock inventory rows in the same order.
        var reserved = await connection.ExecuteAsync(new CommandDefinition(
            "UPDATE inventory SET on_hand = on_hand - @Quantity WHERE sku = @Sku AND on_hand >= @Quantity",
            request.Lines.OrderBy(l => l.Sku, StringComparer.Ordinal), tx.GetDbTransaction(), cancellationToken: ct));
        if (reserved != request.Lines.Count)
        {
            await tx.RollbackAsync(ct);
            return null;
        }

        var entity = new Order
        {
            CustomerId = request.CustomerId,
            IdempotencyKey = request.IdempotencyKey,
            Status = "Pending",
            PlacedAt = clock.GetUtcNow().UtcDateTime,
            Lines = request.Lines.Select(l => new OrderLine { Sku = l.Sku, Quantity = l.Quantity }).ToList(),
        };
        db.Orders.Add(entity);
        await db.SaveChangesAsync(ct); // joins the open transaction
        await tx.CommitAsync(ct);
        return entity;
    });

    return order is null
        ? TypedResults.Conflict("One or more products are out of stock.")
        : TypedResults.Created($"/orders/{order.Id}", new OrderCreated(order.Id));
});

app.Run();

public sealed record PlaceOrderLine(string Sku, int Quantity);
public sealed record PlaceOrder(Guid IdempotencyKey, long CustomerId, List<PlaceOrderLine> Lines);
public sealed record OrderCreated(long Id);

public sealed class Order
{
    public long Id { get; set; }
    public long CustomerId { get; set; }
    public Guid IdempotencyKey { get; set; }
    public string Status { get; set; } = "";
    public DateTime PlacedAt { get; set; }
    public List<OrderLine> Lines { get; set; } = [];
}

public sealed class OrderLine
{
    public long Id { get; set; }
    public string Sku { get; set; } = "";
    public int Quantity { get; set; }
}

public sealed class ShopDb(DbContextOptions<ShopDb> options) : DbContext(options)
{
    public DbSet<Order> Orders => Set<Order>();

    // The unique index also rejects two concurrent requests that carry the same key.
    protected override void OnModelCreating(ModelBuilder model) =>
        model.Entity<Order>().HasIndex(o => o.IdempotencyKey).IsUnique();
}

When to use it

Place an order with EF Core while decrementing stock with a conditional Dapper UPDATE, committing both in one transaction
Serve a monthly sales report with window functions through Dapper on the DbContext's connection, without a second pool
Stream a large export with raw Npgsql while the rest of the service stays on EF Core

More examples

A Dapper report on EF Core's connection

The report needs a window function over an aggregate, which is natural SQL and awkward LINQ. Using <code>GetDbConnection()</code> means no second connection string and no second pool: Dapper opens the context's connection for the call and closes it again because it was closed when handed over. The positional record works because the column order and types (<code>date</code>, <code>integer</code>, <code>numeric</code>) match it exactly.

using Dapper;
using Microsoft.EntityFrameworkCore;

// Column-to-member matching assumes DefaultTypeMap.MatchNamesWithUnderscores = true at start-up.
public sealed record DailySales(DateTime Day, int Orders, decimal Revenue, decimal RunningTotal);

public sealed class SalesReport(ShopDb db)
{
    public async Task<IReadOnlyList<DailySales>> ForMonthAsync(DateOnly month, CancellationToken ct)
    {
        const string sql = """
            SELECT placed_at::date AS day,
                   count(*)::int AS orders,
                   sum(total_amount) AS revenue,
                   sum(sum(total_amount)) OVER (ORDER BY placed_at::date) AS running_total
            FROM orders
            WHERE placed_at >= @from AND placed_at < @to AND status <> 'Cancelled'
            GROUP BY placed_at::date
            ORDER BY day
            """;
        var from = month.ToDateTime(TimeOnly.MinValue, DateTimeKind.Utc);
        var rows = await db.Database.GetDbConnection().QueryAsync<DailySales>(
            new CommandDefinition(sql, new { from, to = from.AddMonths(1) }, cancellationToken: ct));
        return rows.AsList();
    }
}

public sealed class ShopDb(DbContextOptions<ShopDb> options) : DbContext(options);

Pitfall: a tracked entity goes stale after a set-based write

The change tracker knows nothing about SQL it did not generate, so the loaded <code>Customer</code> keeps its old <code>Tier</code>. <code>ReloadAsync</code> re-reads the row inside the same transaction. The same applies to <code>ExecuteUpdateAsync</code>. In a write path, prefer one style per statement: load, change and save with EF Core, or update with SQL and query afterwards.

using Dapper;
using Microsoft.EntityFrameworkCore;
using Microsoft.EntityFrameworkCore.Storage;

public sealed class Customer
{
    public long Id { get; set; }
    public string Tier { get; set; } = "Standard";
    public decimal LifetimeSpend { get; set; }
}

public sealed class ShopDb(DbContextOptions<ShopDb> options) : DbContext(options)
{
    public DbSet<Customer> Customers => Set<Customer>();
}

public sealed class LoyaltyService(ShopDb db)
{
    public async Task<string> RecalculateAsync(long customerId, CancellationToken ct)
    {
        var customer = await db.Customers.SingleAsync(c => c.Id == customerId, ct);

        await using var tx = await db.Database.BeginTransactionAsync(ct);
        await db.Database.GetDbConnection().ExecuteAsync(new CommandDefinition("""
            UPDATE customers SET tier = CASE WHEN lifetime_spend >= 5000 THEN 'Gold' ELSE 'Standard' END
            WHERE id = @customerId
            """, new { customerId }, tx.GetDbTransaction(), cancellationToken: ct));

        // customer.Tier still holds the value read before the UPDATE. Saving the entity now
        // would not overwrite it (no property changed), but returning it would be wrong.
        await db.Entry(customer).ReloadAsync(ct);
        await tx.CommitAsync(ct);
        return customer.Tier;
    }
}

Handing your own connection and transaction to EF Core

Here raw SQL leads and EF Core joins. <code>UseNpgsql(connection)</code> makes the context use an already open connection, which it will not close or dispose, and <code>UseTransactionAsync</code> enlists it in the transaction you began, so the archive, the delete and the audit row commit or roll back together. Building options per call skips the DI context cache, so keep this for rare paths.

using Dapper;
using Microsoft.EntityFrameworkCore;
using Npgsql;

public sealed class AuditEntry
{
    public long Id { get; set; }
    public string Action { get; set; } = "";
    public long OrderId { get; set; }
}

public sealed class AuditDb(DbContextOptions<AuditDb> options) : DbContext(options)
{
    public DbSet<AuditEntry> Entries => Set<AuditEntry>();
}

public sealed class OrderArchiver(NpgsqlDataSource dataSource)
{
    public async Task ArchiveAsync(long orderId, CancellationToken ct)
    {
        await using var connection = await dataSource.OpenConnectionAsync(ct);
        await using var tx = await connection.BeginTransactionAsync(ct);

        await connection.ExecuteAsync(new CommandDefinition(
            "INSERT INTO orders_archive SELECT * FROM orders WHERE id = @orderId; DELETE FROM orders WHERE id = @orderId;",
            new { orderId }, tx, cancellationToken: ct));

        var options = new DbContextOptionsBuilder<AuditDb>().UseNpgsql(connection).Options;
        await using var audit = new AuditDb(options);
        await audit.Database.UseTransactionAsync(tx, ct);
        audit.Entries.Add(new AuditEntry { Action = "archived", OrderId = orderId });
        await audit.SaveChangesAsync(ct);

        await tx.CommitAsync(ct);
    }
}

Discussion

Be the first to comment on this lesson.