Dapper: High-Performance, Lightweight SQL for .NET

A practical guide to Dapper — the micro-ORM that maps query results to C# objects with minimal overhead while leaving you in full control of the actual SQL, covering core querying, parameterization, multi-mapping, transactions, dynamic SQL patterns, and how it compares to EF Core.

---

Table of Contents

1. Introduction
2. What Dapper Actually Is
3. Core Querying
4. Parameterization and SQL Injection Safety
5. Multi-Mapping Relationships
6. Multiple Result Sets
7. Executing Commands and Bulk Operations
8. Transactions
9. Dynamic SQL Patterns
10. Stored Procedures
11. Connection Management
12. Extending Dapper
13. Dapper vs. EF Core
14. Quick Reference Table
15. Conclusion

---

Introduction

Dapper is a micro-ORM — a lightweight library, not a full framework, that solves exactly one problem extremely well: mapping the results of a SQL query onto C# objects, with almost no overhead beyond what ADO.NET itself requires. It doesn't track changes, doesn't generate SQL from LINQ, doesn't manage migrations, and doesn't attempt to abstract away the database underneath you — you write the SQL, Dapper handles parameter binding and result mapping.

using var connection = new SqlConnection(connectionString);
var products = await connection.QueryAsync<Product>(
    "SELECT Id, Name, Price FROM Products WHERE CategoryId = @CategoryId",
    new { CategoryId = categoryId });

That's the entire pattern in one line: SQL text, a parameters object, and a strongly-typed result — no SqlCommand, no SqlDataReader loop, no manual property assignment. Dapper originated at Stack Overflow, built specifically because the team needed something faster than the full-featured ORMs of the time for their highest-traffic query paths, and it's remained popular for exactly that reason: when you already know the SQL you want to run and just need it executed efficiently and safely, Dapper gets out of the way.

---

1. What Dapper Actually Is

An extension library on IDbConnection

Dapper is implemented entirely as extension methods on ADO.NET's IDbConnection interface — there's no special connection type, no separate context object, no abstraction layer between you and the database provider you're already using (SqlConnection, NpgsqlConnection, SqliteConnection, etc.).

public static class SqlMapper
{
    public static Task<IEnumerable<T>> QueryAsync<T>(this IDbConnection cnn, string sql, object? param = null, ...);
    public static Task<int> ExecuteAsync(this IDbConnection cnn, string sql, object? param = null, ...);
}

This design choice is deliberate and significant: because Dapper works against the standard IDbConnection interface, it works with any ADO.NET provider without Dapper needing provider-specific code, and it composes naturally with existing ADO.NET knowledge rather than replacing it.

What Dapper does for you

• Parameter binding — maps an anonymous object's properties (or a DynamicParameters instance) to SQL parameters safely.
• Result mapping — maps each row of a SqlDataReader onto a C# object's properties by column name, using compiled IL generated at runtime (cached per query shape) rather than slow reflection on every row.
• Type handling — sensible conversions between SQL types and .NET types, plus extensibility for custom type handling.

What Dapper deliberately does not do

• No change tracking — you write the UPDATE statement yourself; there's no SaveChangesAsync().
• No LINQ-to-SQL translation — every query is SQL text you write directly.
• No migrations — schema management is entirely your responsibility (often paired with a dedicated migration tool like DbUp, Flyway, or even EF Core's migration system used purely for schema management alongside Dapper for data access).
• No lazy loading, no navigation property magic — relationships are expressed via explicit joins and multi-mapping (Section 4), not automatic traversal.

This isn't Dapper being incomplete — it's Dapper being intentionally minimal, trading the conveniences of a full ORM for predictability, transparency, and speed.

---

2. Core Querying

Query and QueryAsync

public class ProductRepository
{
    private readonly string _connectionString;
    public ProductRepository(string connectionString) => _connectionString = connectionString;

    public async Task<IEnumerable<Product>> GetByCategoryAsync(int categoryId)
    {
        using var connection = new SqlConnection(_connectionString);
        return await connection.QueryAsync<Product>(
            "SELECT Id, Name, Price, CategoryId FROM Products WHERE CategoryId = @CategoryId",
            new { CategoryId = categoryId });
    }
}

Single-row queries

var product = await connection.QuerySingleOrDefaultAsync<Product>(
    "SELECT Id, Name, Price FROM Products WHERE Id = @Id", new { Id = id });

Method	Behavior when zero/multiple rows match
QueryFirstAsync	Returns the first row; throws if zero rows
QueryFirstOrDefaultAsync	Returns the first row, or default if zero rows; ignores extras
QuerySingleAsync	Throws unless exactly one row is returned
QuerySingleOrDefaultAsync	Returns default if zero rows, throws if more than one

Choosing the right one communicates intent: QuerySingleOrDefaultAsync for "there should be at most one match, and it's a bug if there's more than one" (like looking up by a unique ID) versus QueryFirstOrDefaultAsync for "there might be several matches and I only want one" (like the most recent record in an ordered query).

Scalar values

var count = await connection.ExecuteScalarAsync<int>("SELECT COUNT(*) FROM Products WHERE CategoryId = @CategoryId", new { categoryId });

Mapping to anonymous types or dynamic

var results = await connection.QueryAsync("SELECT Name, Price FROM Products WHERE CategoryId = @CategoryId", new { categoryId });
foreach (var row in results)
{
    Console.WriteLine($"{row.Name}: {row.Price}"); // dynamic access, no class required
}

For quick, throwaway queries (a one-off report, an ad-hoc script) where defining a dedicated class feels like unnecessary ceremony, Dapper's non-generic Query/QueryAsync returns dynamic rows — convenient for exploration, though a concrete class is generally preferable for anything that's part of a maintained codebase, since dynamic loses compile-time checking entirely.

---

3. Parameterization and SQL Injection Safety

Always use parameters, never string concatenation

// ✅ Safe — Dapper parameterizes this correctly
var products = await connection.QueryAsync<Product>(
    "SELECT * FROM Products WHERE Name = @Name", new { Name = searchTerm });

// ❌ Vulnerable — never do this, regardless of the library
var products = await connection.QueryAsync<Product>(
    $"SELECT * FROM Products WHERE Name = '{searchTerm}'");

Because Dapper is a thin layer over raw SQL text, it doesn't protect you from SQL injection the way an ORM that generates all SQL for you inherently does — the protection comes entirely from consistently using parameterized queries (the anonymous object syntax above), never string interpolation or concatenation to build query text from user input. This is the single most important discipline when using Dapper (or any raw-SQL approach), and it's worth treating as a non-negotiable rule rather than a case-by-case judgment call.

DynamicParameters for more control

var parameters = new DynamicParameters();
parameters.Add("@CategoryId", categoryId);
parameters.Add("@MinPrice", minPrice, DbType.Decimal);
parameters.Add("@RowsAffected", dbType: DbType.Int32, direction: ParameterDirection.Output);

await connection.ExecuteAsync(
    "UPDATE Products SET Price = Price * 1.1 WHERE CategoryId = @CategoryId AND Price >= @MinPrice; SET @RowsAffected = @@ROWCOUNT;",
    parameters);

int rowsAffected = parameters.Get<int>("@RowsAffected");

DynamicParameters is useful when you need explicit control over a parameter's DbType, need output parameters (common with stored procedures, see Section 9), or are building a parameter set programmatically rather than from a fixed anonymous object shape.

---

4. Multi-Mapping Relationships

Because Dapper doesn't understand navigation properties or automatic joins, mapping a SQL join's flattened result set back into a nested object graph is done explicitly via multi-mapping.

public class Order
{
    public int Id { get; set; }
    public DateTime OrderDate { get; set; }
    public Customer Customer { get; set; } = null!;
}

public class Customer
{
    public int Id { get; set; }
    public string Name { get; set; } = "";
}

var sql = @"
    SELECT o.Id, o.OrderDate, c.Id, c.Name
    FROM Orders o
    JOIN Customers c ON c.Id = o.CustomerId
    WHERE o.Id = @OrderId";

var order = await connection.QueryAsync<Order, Customer, Order>(
    sql,
    (order, customer) => { order.Customer = customer; return order; },
    new { OrderId = orderId },
    splitOn: "Id" // tells Dapper where the Customer's columns begin in the flattened row
);

The splitOn parameter is the detail that trips people up most often — it tells Dapper which column name marks the boundary between the first object's columns and the second's in the flat row returned by the join. If both objects happen to have a column named Id (as above), that's exactly what you want to split on; if column names collide in a way that makes splitOn ambiguous, aliasing columns in the SQL itself (c.Id AS CustomerId) alongside an explicit splitOn: "CustomerId" resolves it cleanly.

One-to-many multi-mapping

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

var sql = @"
    SELECT o.Id, o.OrderDate, i.Id, i.ProductName, i.Quantity
    FROM Orders o
    JOIN OrderItems i ON i.OrderId = o.Id
    WHERE o.Id = @OrderId";

await connection.QueryAsync<Order, OrderItem, Order>(
    sql,
    (order, item) =>
    {
        if (!orderDictionary.TryGetValue(order.Id, out var existingOrder))
        {
            existingOrder = order;
            existingOrder.Items = [];
            orderDictionary.Add(existingOrder.Id, existingOrder);
        }
        existingOrder.Items.Add(item);
        return existingOrder;
    },
    new { OrderId = orderId },
    splitOn: "Id"
);

var result = orderDictionary.Values.Single();

This dictionary-accumulation pattern is the standard idiom for mapping a one-to-many join (one order, many order items) back into a single parent object with a populated collection — more manual than EF Core's automatic Include, but fully transparent about exactly what SQL runs and exactly how the mapping happens.

---

5. Multiple Result Sets

For a single round trip returning several distinct result sets (common when a single stored procedure or batched query needs to return, say, an order plus its line items as two separate SELECT statements), Dapper's QueryMultipleAsync handles it in one connection round trip:

var sql = @"
    SELECT * FROM Orders WHERE Id = @OrderId;
    SELECT * FROM OrderItems WHERE OrderId = @OrderId;";

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

var order = await multi.ReadSingleAsync<Order>();
var items = (await multi.ReadAsync<OrderItem>()).ToList();
order.Items = items;

This avoids two separate round trips to the database for logically related data, while still keeping the mapping fully explicit and readable — a good middle ground between a single complex multi-mapped join and two entirely separate queries.

---

6. Executing Commands and Bulk Operations

Insert, update, delete

await connection.ExecuteAsync(
    "INSERT INTO Products (Name, Price, CategoryId) VALUES (@Name, @Price, @CategoryId)",
    new { Name = "Wireless Mouse", Price = 29.99m, CategoryId = 3 });

await connection.ExecuteAsync(
    "UPDATE Products SET Price = @Price WHERE Id = @Id",
    new { Id = 42, Price = 24.99m });

int rowsDeleted = await connection.ExecuteAsync(
    "DELETE FROM Products WHERE CategoryId = @CategoryId", new { CategoryId = categoryId });

ExecuteAsync returns the number of rows affected, which is often useful for confirming an update/delete actually matched something, without needing a separate existence check beforehand.

Getting a generated ID back after insert

var newId = await connection.ExecuteScalarAsync<int>(
    "INSERT INTO Products (Name, Price) VALUES (@Name, @Price); SELECT CAST(SCOPE_IDENTITY() AS INT);",
    new { Name = "Wireless Mouse", Price = 29.99m });

Since there's no automatic entity tracking to populate an Id property for you, retrieving a database-generated identity value requires explicitly including that in the SQL itself — SCOPE_IDENTITY() for SQL Server, RETURNING id for PostgreSQL, and so on, each database having its own idiom.

Bulk insert via a single parameterized call

var products = GetProductsToInsert(); // IEnumerable<Product>
await connection.ExecuteAsync(
    "INSERT INTO Products (Name, Price, CategoryId) VALUES (@Name, @Price, @CategoryId)",
    products);

Passing an IEnumerable<T> to ExecuteAsync runs the statement once per item — convenient and still far better than manually looping and building individual SqlCommand objects, but for genuinely large bulk-insert scenarios (thousands of rows or more), a dedicated bulk-copy mechanism (SqlBulkCopy for SQL Server, PostgreSQL's COPY command) will significantly outperform this per-row execution pattern, since it avoids the per-statement round-trip overhead entirely.

---

7. Transactions

using var connection = new SqlConnection(connectionString);
await connection.OpenAsync();
using var transaction = connection.BeginTransaction();

try
{
    await connection.ExecuteAsync(
        "UPDATE Accounts SET Balance = Balance - @Amount WHERE Id = @FromId",
        new { Amount = 100, FromId = fromAccountId }, transaction);

    await connection.ExecuteAsync(
        "UPDATE Accounts SET Balance = Balance + @Amount WHERE Id = @ToId",
        new { Amount = 100, ToId = toAccountId }, transaction);

    transaction.Commit();
}
catch
{
    transaction.Rollback();
    throw;
}

Because Dapper is built directly on ADO.NET, transactions work exactly the way they always have in ADO.NET — a DbTransaction object, passed explicitly as an argument to each ExecuteAsync/QueryAsync call that should participate in it. There's no separate Dapper-specific transaction abstraction to learn; if you already know ADO.NET transactions, you already know Dapper transactions.

---

8. Dynamic SQL Patterns

Because Dapper doesn't build SQL for you, conditionally varying a query's shape (optional filters, dynamic sorting) is handled by conditionally building the SQL string itself, carefully, while still keeping all values parameterized.

Conditional filters

public async Task<IEnumerable<Product>> SearchAsync(string? category, decimal? minPrice, decimal? maxPrice)
{
    var conditions = new List<string>();
    var parameters = new DynamicParameters();

    if (category is not null)
    {
        conditions.Add("Category = @Category");
        parameters.Add("@Category", category);
    }
    if (minPrice is not null)
    {
        conditions.Add("Price >= @MinPrice");
        parameters.Add("@MinPrice", minPrice);
    }
    if (maxPrice is not null)
    {
        conditions.Add("Price <= @MaxPrice");
        parameters.Add("@MaxPrice", maxPrice);
    }

    var whereClause = conditions.Count > 0 ? "WHERE " + string.Join(" AND ", conditions) : "";
    var sql = $"SELECT * FROM Products {whereClause}";

    return await connection.QueryAsync<Product>(sql, parameters);
}

The critical discipline here: the conditionally-built parts are column/keyword names and structure, never raw values — every actual value still flows through a parameter (@Category, @MinPrice), never directly interpolated into the SQL string. This is what keeps dynamic SQL construction safe from injection even though the query text itself varies at runtime.

IN clauses with a variable number of values

var products = await connection.QueryAsync<Product>(
    "SELECT * FROM Products WHERE CategoryId IN @CategoryIds",
    new { CategoryIds = categoryIds }); // categoryIds: IEnumerable<int>

Dapper has built-in support for expanding a collection parameter into the correct number of IN (...) placeholders automatically — you don't need to hand-build a comma-separated list yourself, which is both more convenient and avoids a common, easy-to-get-wrong manual string-building mistake.

---

9. Stored Procedures

var results = await connection.QueryAsync<Product>(
    "GetProductsByCategory",
    new { CategoryId = categoryId },
    commandType: CommandType.StoredProcedure);

var parameters = new DynamicParameters();
parameters.Add("@CustomerId", customerId);
parameters.Add("@TotalOrders", dbType: DbType.Int32, direction: ParameterDirection.Output);

await connection.ExecuteAsync(
    "GetCustomerOrderSummary",
    parameters,
    commandType: CommandType.StoredProcedure);

int totalOrders = parameters.Get<int>("@TotalOrders");

Calling stored procedures is a natural fit for Dapper's model — you're already writing/managing the actual data-access logic explicitly, and a stored procedure is just another form of "SQL text to execute with parameters," output parameters included via DynamicParameters as shown above.

---

10. Connection Management

Dapper doesn't manage connection lifetime for you

// Each repository method typically opens and disposes its own connection
public async Task<Product?> GetByIdAsync(int id)
{
    using var connection = new SqlConnection(_connectionString);
    return await connection.QuerySingleOrDefaultAsync<Product>(
        "SELECT * FROM Products WHERE Id = @Id", new { Id = id });
}

Unlike EF Core's DbContext, which manages an underlying connection's lifecycle as part of its own scoped lifetime, Dapper operates directly on whatever IDbConnection you hand it — opening and disposing the connection is your responsibility, typically via a using statement scoped to each logical unit of work (as above), relying on ADO.NET's built-in connection pooling to make repeatedly opening/closing connections cheap in practice (the underlying physical connection is usually reused from the pool, not actually torn down and recreated each time).

A typical repository pattern

public class ProductRepository
{
    private readonly IDbConnectionFactory _connectionFactory;
    public ProductRepository(IDbConnectionFactory connectionFactory) => _connectionFactory = connectionFactory;

    public async Task<Product?> GetByIdAsync(int id)
    {
        using var connection = _connectionFactory.CreateConnection();
        return await connection.QuerySingleOrDefaultAsync<Product>(
            "SELECT * FROM Products WHERE Id = @Id", new { Id = id });
    }
}

public interface IDbConnectionFactory
{
    IDbConnection CreateConnection();
}

public class SqlConnectionFactory : IDbConnectionFactory
{
    private readonly string _connectionString;
    public SqlConnectionFactory(string connectionString) => _connectionString = connectionString;
    public IDbConnection CreateConnection() => new SqlConnection(_connectionString);
}

Wrapping connection creation behind a small factory interface (rather than instantiating SqlConnection directly throughout the codebase) makes it easier to swap providers or inject a test double, without adding meaningful overhead or complexity — a common, lightweight convention in Dapper-based codebases.

---

11. Extending Dapper

Custom type handlers

public class JsonTypeHandler<T> : SqlMapper.TypeHandler<T>
{
    public override void SetValue(IDbDataParameter parameter, T value) =>
        parameter.Value = JsonSerializer.Serialize(value);

    public override T Parse(object value) =>
        JsonSerializer.Deserialize<T>((string)value)!;
}

SqlMapper.AddTypeHandler(new JsonTypeHandler<ProductMetadata>());

Custom type handlers let Dapper correctly map types it doesn't understand natively — a common example being a column storing serialized JSON that should be automatically deserialized into a strongly-typed C# object on read, and serialized back on write.

Dapper.Contrib and other extensions

[Table("Products")]
public class Product
{
    [Key]
    public int Id { get; set; }
    public string Name { get; set; } = "";
    public decimal Price { get; set; }
}

await connection.InsertAsync(product);
await connection.UpdateAsync(product);
await connection.DeleteAsync(product);
var product = await connection.GetAsync<Product>(42);

Dapper.Contrib is an official companion library adding basic CRUD convenience methods (Insert, Update, Delete, Get) generated from simple attribute-based conventions — useful for straightforward single-table CRUD where writing the SQL by hand feels like unnecessary repetition, while still keeping the rest of your data access as plain Dapper for anything more involved. It's optional, and many Dapper codebases skip it entirely in favor of writing every statement explicitly, valuing the transparency over the convenience.

---

12. Dapper vs. EF Core

Aspect	Dapper	EF Core
SQL control	You write every query explicitly	LINQ translated automatically; less direct control
Performance overhead	Minimal — closest to raw ADO.NET	Higher — change tracking, query translation, more abstraction
Change tracking	None — you write UPDATE statements yourself	Automatic — modify properties, call SaveChangesAsync()
Migrations	None built in — pair with a separate tool, or manage schema manually	Built-in, integrated migration system
Relationship mapping	Manual, explicit multi-mapping	Automatic via navigation properties and Include
Learning curve	Low if you already know SQL; you own more of the plumbing	Higher — more concepts (change tracking, loading strategies, LINQ translation quirks) to learn well
Best for	Performance-critical paths, complex reporting queries, teams that want full SQL control	The majority of typical CRUD-heavy application data access
Risk profile	SQL injection risk if parameterization discipline lapses; more manual code to get wrong	N+1 queries and unintended full-entity loads if loading strategies aren't used carefully

The common, pragmatic combination

As covered in this series' EF Core guide, a large share of production .NET codebases don't choose one exclusively — they use EF Core for the bulk of application data access (where its productivity and maintainability benefits are worth the overhead) and Dapper for a specific, deliberately identified set of high-volume or complex-reporting queries where raw SQL control and minimal overhead genuinely matter more than the conveniences EF Core provides. Both libraries can coexist against the same database and even the same underlying SqlConnection without conflict, since Dapper is just extension methods on the same ADO.NET types EF Core itself ultimately uses underneath.

---

Quick Reference Table

Concept	Purpose
QueryAsync<T>	Maps multiple rows to a strongly-typed collection
QuerySingleOrDefaultAsync<T>	Enforces "at most one match" semantics for lookups
ExecuteAsync	Runs INSERT/UPDATE/DELETE, returns affected row count
ExecuteScalarAsync<T>	Returns a single scalar value (count, generated ID, etc.)
DynamicParameters	Explicit parameter control, output parameters
Multi-mapping (splitOn)	Maps a flattened join result back into a nested object graph
QueryMultipleAsync	Reads several result sets from one round trip
Custom type handlers	Teaches Dapper to map types it doesn't understand natively
Dapper.Contrib	Optional attribute-based basic CRUD convenience layer
IN @Collection	Automatic expansion of a collection parameter into IN (...)

---

Conclusion

Dapper's entire value proposition is honesty about the tradeoff a full ORM makes: EF Core buys productivity and convenience by abstracting SQL away, at some cost in overhead and "magic" you need to understand to avoid its pitfalls; Dapper keeps you writing SQL directly, in exchange for speed, transparency, and the confidence that comes from knowing exactly what query is running, every time. Neither approach is more "correct" — they're suited to different priorities, and the most pragmatic production codebases often use both, applying each where its specific strengths matter most.

What Dapper demands from you that a full ORM partially handles automatically is discipline: consistent parameterization to avoid SQL injection, deliberate multi-mapping for relationships, and your own schema/migration strategy. In exchange, you get code that does exactly what it looks like it does — no hidden N+1 queries, no surprising client-evaluation fallbacks, no generated SQL you didn't anticipate — which is precisely why it remains the tool of choice whenever a query's performance and predictability matter more than the convenience of not writing SQL at all.

---

Found this useful? Feel free to star the repo, open an issue with corrections, or share the splitOn mistake that taught you to read the multi-mapping docs carefully.