Dapper in .NET: A Practical Guide

Dapper is a micro-ORM that extends IDbConnection with methods that execute your SQL and map the results to C# objects. It doesn't generate SQL, doesn't track changes, doesn't lazy-load anything. The value is predictability: what you wrote is what runs, and the query plan you tested in your SQL client is the plan production gets. (And when that plan surprises you by ignoring an index, the cause is usually a non-sargable predicate.)

Every ORM discussion eventually produces the sentence "I just want to write the SQL myself." Dapper is for exactly that person.

Here are the patterns that cover 95% of real-world Dapper usage.

Setup

Dapper is a set of extension methods on IDbConnection, so it works with any ADO.NET provider:

dotnet add package Dapper
dotnet add package Npgsql
dotnet add package Npgsql.DependencyInjection

Register a connection factory so handlers can rent connections cheaply. With PostgreSQL, the modern approach is a shared NpgsqlDataSource, registered with the AddNpgsqlDataSource extension from Npgsql.DependencyInjection:

// Program.cs
builder.Services.AddNpgsqlDataSource(
    builder.Configuration.GetConnectionString("Database")!);

public class ProductRepository(NpgsqlDataSource dataSource)
{
    public async Task<Product?> GetByIdAsync(int id, CancellationToken ct)
    {
        await using NpgsqlConnection connection =
            await dataSource.OpenConnectionAsync(ct);

        return await connection.QuerySingleOrDefaultAsync<Product>(
            new CommandDefinition(
                "SELECT id, name, price, created_at FROM products WHERE id = @Id",
                new { Id = id },
                cancellationToken: ct));
    }
}

Note the CommandDefinition wrapper. Dapper's simple overloads have no CancellationToken parameter, so passing ct to the method and stopping there means the query itself can't be cancelled. Wrapping the SQL and parameters in a CommandDefinition is how the token reaches the actual database call.

The await using matters too: connections must go back to the pool, and forgetting disposal is the fast lane to connection pool exhaustion.

One mapping note for PostgreSQL: snake_case columns map to PascalCase properties if you enable it once at startup:

Dapper.DefaultTypeMap.MatchNamesWithUnderscores = true;

Queries: The Core Four

Dapper's query surface is small enough to memorize:

// Many rows
IEnumerable<Product> products = await connection.QueryAsync<Product>(
    "SELECT * FROM products WHERE price > @MinPrice",
    new { MinPrice = 100m });

// Exactly one row (throws if zero or more than one)
Product product = await connection.QuerySingleAsync<Product>(
    "SELECT * FROM products WHERE id = @Id", new { Id = 42 });

// Zero or one row
Product? maybe = await connection.QuerySingleOrDefaultAsync<Product>(
    "SELECT * FROM products WHERE sku = @Sku", new { Sku = "ABC-1" });

// A single scalar
int count = await connection.ExecuteScalarAsync<int>(
    "SELECT COUNT(*) FROM products");

And for writes, ExecuteAsync returns affected rows:

int rows = await connection.ExecuteAsync(
    """
    UPDATE products
    SET price = @Price
    WHERE id = @Id
    """,
    new { Id = 42, Price = 129.99m });

Parameters are always the anonymous-object form. Never interpolate values into the SQL string; parameterization is both your SQL injection defense and what lets the database reuse query plans.

A useful bonus: pass an IEnumerable as a parameter value and Dapper expands IN clauses for you:

var products = await connection.QueryAsync<Product>(
    "SELECT * FROM products WHERE id = ANY(@Ids)", // PostgreSQL
    new { Ids = new[] { 1, 5, 9 } });

(On SQL Server use WHERE id IN @Ids and Dapper rewrites it to individual parameters.)

Multi-Mapping: Joins Into Object Graphs

Dapper doesn't know about relationships, so joins map through a callback. The splitOn column tells Dapper where one entity's columns end and the next begins:

const string sql =
    """
    SELECT o.id, o.total, o.created_at,
           c.id, c.name, c.email
    FROM orders o
    JOIN customers c ON c.id = o.customer_id
    WHERE o.created_at >= @Since
    """;

IEnumerable<Order> orders = await connection.QueryAsync<Order, Customer, Order>(
    sql,
    (order, customer) =>
    {
        order.Customer = customer;
        return order;
    },
    new { Since = DateTime.UtcNow.AddDays(-7) },
    splitOn: "id");

For one-to-many (an order with its lines), the join repeats the parent per child row, so you deduplicate with a dictionary:

var lookup = new Dictionary<int, Order>();

await connection.QueryAsync<Order, OrderLine, Order>(
    """
    SELECT o.id, o.total, l.id, l.product_name, l.quantity
    FROM orders o
    JOIN order_lines l ON l.order_id = o.id
    """,
    (order, line) =>
    {
        if (!lookup.TryGetValue(order.Id, out Order? existing))
        {
            existing = order;
            existing.Lines = [];
            lookup.Add(existing.Id, existing);
        }

        existing.Lines.Add(line);
        return existing;
    },
    splitOn: "id");

List<Order> orders = lookup.Values.ToList();

This is the most mechanical part of Dapper, and I went deeper on the variants in mastering Dapper relationship mappings. When a read model needs three or more collections, QueryMultiple is usually cleaner than a mega-join.

QueryMultiple: Several Result Sets, One Round Trip

Dashboards and detail pages often need unrelated result sets. Send them as one batch:

const string sql =
    """
    SELECT * FROM orders WHERE customer_id = @CustomerId;
    SELECT * FROM addresses WHERE customer_id = @CustomerId;
    SELECT COUNT(*) FROM support_tickets WHERE customer_id = @CustomerId;
    """;

await using var multi = await connection.QueryMultipleAsync(
    sql, new { CustomerId = customerId });

List<Order> orders = (await multi.ReadAsync<Order>()).ToList();
List<Address> addresses = (await multi.ReadAsync<Address>()).ToList();
int openTickets = await multi.ReadSingleAsync<int>();

One round trip instead of three. On a 5ms-latency connection, that's 10ms saved per page load for free.

Transactions

Dapper rides on ADO.NET transactions; you pass the transaction to each call:

await using NpgsqlConnection connection = await dataSource.OpenConnectionAsync(ct);
await using NpgsqlTransaction transaction = await connection.BeginTransactionAsync(ct);

try
{
    await connection.ExecuteAsync(
        new CommandDefinition(
            "UPDATE accounts SET balance = balance - @Amount WHERE id = @From",
            new { Amount = 100m, From = fromId },
            transaction: transaction,
            cancellationToken: ct));

    await connection.ExecuteAsync(
        new CommandDefinition(
            "UPDATE accounts SET balance = balance + @Amount WHERE id = @To",
            new { Amount = 100m, To = toId },
            transaction: transaction,
            cancellationToken: ct));

    await transaction.CommitAsync(ct);
}
catch
{
    await transaction.RollbackAsync(ct);
    throw;
}

Forgetting to pass transaction to one of the calls is the classic bug, and the two big providers handle it differently. With Npgsql, a command on a connection that has an open transaction runs inside that transaction anyway, so the code works right up until someone refactors it onto a second connection and atomicity quietly disappears. With Microsoft.Data.SqlClient, the same mistake throws an InvalidOperationException, which is annoying but at least loud. Pass the transaction explicitly to every call and neither provider can surprise you. If you need cross-statement consistency guarantees beyond this, that's transaction isolation level territory.

Where Does Dapper Fit Next to EF Core?

I don't treat this as either/or, and most of my systems use both:

• EF Core for the write side: change tracking, transactions and unit-of-work semantics, migrations, and domain model persistence
• Dapper for the read side: reports, dashboards, list endpoints with heavy filtering, anything where I want to hand-tune the SQL and see exactly what runs

The reasoning: writes benefit from EF Core's tracking and consistency machinery; hot reads benefit from precise SQL and minimal materialization cost. When an EF Core LINQ query translates badly, rewriting that one query in Dapper (or EF Core raw SQL) is a scalpel, not a rewrite.

I put the detailed comparison and decision criteria in EF Core vs Dapper. The short version: choose Dapper where SQL control and predictability matter more than productivity features, which for most teams means the read-heavy 20% of the data access layer.

Two things Dapper deliberately won't do for you:

• No SQL generation: every schema change means finding the affected SQL strings. Integration tests against a real database (Testcontainers) are non-negotiable.
• No change tracking: updates are explicit statements. That's the predictability you signed up for.

Dapper also leaves schema evolution to another tool. The EF Core Migrations vs DbUp vs FluentMigrator comparison helps choose who should own that SQL.

Summary

Keep Dapper explicit. Dapper earns its place by refusing to be clever. You write SQL, it maps objects, and there's nothing in between to surprise you.

The working set of patterns is small:

• QueryAsync / QuerySingleOrDefaultAsync / ExecuteAsync / ExecuteScalarAsync with anonymous-object parameters, always
• Multi-mapping with splitOn for joins, dictionary deduplication for collections
• QueryMultiple to batch independent result sets into one round trip
• Explicit transactions passed to every participating call
• await using on every connection, because the pool doesn't forgive leaks

Pair it with EF Core rather than replacing EF Core, point it at your read-heavy paths, and you get hand-tuned SQL where it pays and productivity everywhere else.