Dapper is a free, open-source micro-ORM for .NET. It adds extension methods such as Query and Execute to any ADO.NET connection, runs the SQL you write, and maps the results to your C# objects. It was built by Stack Overflow, which still runs it in production, and the source lives in the DapperLib/Dapper repository on GitHub.
What Dapper leaves out is as important as what it does. It does not generate SQL, track changes or manage migrations. In return it is very fast, very small and quick to learn: if you can write SQL, you can be productive with Dapper in ten minutes.
Install it with dotnet add package Dapper. As of October 2026 the current version is 2.1.89 (September 23, 2026), and it works with SQL Server, PostgreSQL, MySQL, SQLite, Oracle and any other ADO.NET provider. This is a complete query:
using var connection = new SqlConnection(connectionString);
var products = await connection.QueryAsync<Product>(
"SELECT Id, Name, Price FROM dbo.Products WHERE Price > @minPrice",
new { minPrice = 10m });
What Dapper does under the hood
Plain ADO.NET makes you create a command, add parameters one by one, open a reader and copy each column into a property. Dapper does all of that in one call:
Your SQL + parameter object
↓
Dapper builds a DbCommand and parameters
↓
Provider (SqlClient, Npgsql, MySqlConnector...) runs the query
↓
Dapper maps each row to T using generated, cached code
↓
IEnumerable<T> returned to you
The first time Dapper sees a query, it generates IL code for reading that result shape and caches it. Later calls reuse the cached code, which is why its speed is close to hand-written ADO.NET. Columns map to properties by name, case-insensitively, and it works with classes, records, constructors and dynamic.
Installing Dapper and wiring it into ASP.NET Core
Add Dapper plus the ADO.NET provider for your database:
dotnet add package Dapper
dotnet add package Microsoft.Data.SqlClient # SQL Server
# dotnet add package Npgsql # PostgreSQL
# dotnet add package MySqlConnector # MySQL / MariaDB
# dotnet add package Microsoft.Data.Sqlite # SQLite
The package targets .NET Framework 4.6.1, .NET Standard 2.0, .NET 8 and .NET 10, so it fits old and new projects alike.
In ASP.NET Core, don't register a single shared connection. ADO.NET connections are not thread-safe, so register a factory and create a connection per operation. Connection pooling makes that cheap.
public interface IDbConnectionFactory
{
DbConnection Create();
}
public sealed class SqlConnectionFactory(string connectionString) : IDbConnectionFactory
{
public DbConnection Create() => new SqlConnection(connectionString);
}
// Program.cs
builder.Services.AddSingleton<IDbConnectionFactory>(
new SqlConnectionFactory(builder.Configuration.GetConnectionString("Default")!));
The examples below use these models:
public sealed class Customer
{
public int Id { get; set; }
public string Name { get; set; } = "";
public string Email { get; set; } = "";
}
public sealed class Order
{
public int Id { get; set; }
public DateTime OrderDate { get; set; }
public int CustomerId { get; set; }
public Customer? Customer { get; set; }
public List<OrderLine> Lines { get; set; } = [];
}
public sealed class OrderLine
{
public int Id { get; set; }
public string Sku { get; set; } = "";
public int Quantity { get; set; }
}
Reading data
using var connection = factory.Create();
IEnumerable<Customer> customers = await connection.QueryAsync<Customer>(
"SELECT Id, Name, Email FROM dbo.Customers ORDER BY Name");
Dapper opens a closed connection for you and closes it again afterwards. You only need to open it yourself when you run several commands in a row or use a transaction.
For a single row, pick the method by what "zero rows" and "more than one row" should mean:
Method | No rows | One row | Many rows |
|---|
QueryFirst
| Throws | Returns it | Returns the first |
QueryFirstOrDefault
| Returns default (null) | Returns it | Returns the first |
QuerySingle
| Throws | Returns it | Throws |
QuerySingleOrDefault
| Returns default (null) | Returns it | Throws |
Customer? customer = await connection.QuerySingleOrDefaultAsync<Customer>(
"SELECT Id, Name, Email FROM dbo.Customers WHERE Id = @id",
new { id });
int count = await connection.ExecuteScalarAsync<int>("SELECT COUNT(*) FROM dbo.Customers");
My recommendation is QuerySingleOrDefault for every primary-key lookup. If it ever throws because two rows came back, it has found a real data bug for you, whereas QueryFirst would have quietly hidden it.
Writing data with Execute
Execute runs a command and returns the number of affected rows, which is often all you need to decide between success and "not found":
int rows = await connection.ExecuteAsync(
"UPDATE dbo.Customers SET Email = @Email WHERE Id = @Id",
new { Id = 42, Email = "[email protected]" });
if (rows == 0)
{
// Not found - return 404 from your API
}
To insert a row and get its new ID back, select it in the same batch:
const string sql = """
INSERT INTO dbo.Customers (Name, Email)
VALUES (@Name, @Email);
SELECT CAST(SCOPE_IDENTITY() AS int);
""";
int newId = await connection.QuerySingleAsync<int>(sql, new { Name = "Asha", Email = "[email protected]" });
OUTPUT INSERTED.Id works too, with one catch: SQL Server rejects a plain OUTPUT clause on a table that has triggers. In that case use SCOPE_IDENTITY() or OUTPUT ... INTO a table variable. On PostgreSQL, use RETURNING id.
Parameters
Always pass values as parameters, never by concatenating strings. Dapper accepts an anonymous object, or any existing object whose property names match the SQL:
// Anonymous object: property names become @parameter names
await connection.ExecuteAsync("DELETE FROM dbo.Orders WHERE Id = @id", new { id = 7 });
// An existing object works too: Dapper reads only the properties the SQL uses
await connection.ExecuteAsync(
"UPDATE dbo.Customers SET Name = @Name, Email = @Email WHERE Id = @Id", customer);
IN lists
Pass a collection and Dapper expands it into separate parameters. Note that there are no parentheses after IN in your SQL:
int[] ids = [1, 2, 3];
var selected = await connection.QueryAsync<Customer>(
"SELECT Id, Name, Email FROM dbo.Customers WHERE Id IN @ids",
new { ids });
// Sent as: WHERE Id IN (@ids1, @ids2, @ids3)
For very large lists, in the thousands, use a table-valued parameter instead. SQL Server allows at most 2,100 parameters per command.
Dynamic filters
When the filter depends on user input, build the SQL text conditionally but keep every value in DynamicParameters:
var sql = new StringBuilder("SELECT Id, Name, Email FROM dbo.Customers WHERE 1 = 1");
var p = new DynamicParameters();
if (!string.IsNullOrWhiteSpace(name))
{
sql.Append(" AND Name LIKE @name");
p.Add("name", name + "%");
}
if (createdAfter is not null)
{
sql.Append(" AND CreatedAt >= @createdAfter");
p.Add("createdAfter", createdAfter, DbType.DateTime2);
}
var results = await connection.QueryAsync<Customer>(sql.ToString(), p);
The SQL text changes, but no value ever becomes part of it. That is the safe way to build dynamic queries.
varchar columns
.NET strings are sent as nvarchar by default. If the column is varchar, SQL Server may convert the column on every row and skip your index. Tell Dapper the type explicitly:
var p = new { Code = new DbString { Value = "AB-100", IsAnsi = true, Length = 20 } };
var item = await connection.QuerySingleOrDefaultAsync<Product>(
"SELECT Id, Name, Price FROM dbo.Products WHERE Code = @Code", p);
Mapping joins with multi-mapping
Multi-mapping splits each row of a join into several objects. By default Dapper splits at every column named Id; pass splitOn when your key has a different name. For a one-to-one relationship, such as an order and its customer, the mapping function just attaches one object to the other:
const string sql = """
SELECT o.Id, o.OrderDate, o.CustomerId,
c.Id, c.Name, c.Email
FROM dbo.Orders o
JOIN dbo.Customers c ON c.Id = o.CustomerId
""";
var orders = await connection.QueryAsync<Order, Customer, Order>(
sql,
(order, customer) => { order.Customer = customer; return order; },
splitOn: "Id");
One-to-many takes a little more work, because a join repeats the parent row once per child:
const string sql = """
SELECT o.Id, o.OrderDate, o.CustomerId,
l.Id, l.Sku, l.Quantity
FROM dbo.Orders o
LEFT JOIN dbo.OrderLines l ON l.OrderId = o.Id
WHERE o.CustomerId = @customerId
""";
var lookup = new Dictionary<int, Order>();
await connection.QueryAsync<Order, OrderLine, Order>(
sql,
(order, line) =>
{
if (!lookup.TryGetValue(order.Id, out var existing))
{
existing = order;
lookup.Add(order.Id, existing);
}
if (line is not null)
existing.Lines.Add(line);
return existing;
},
new { customerId },
splitOn: "Id");
List<Order> result = [.. lookup.Values];
The dictionary collapses the duplicate order objects the join produces. With a LEFT JOIN, line is null for an order with no lines. If you get half-filled objects, the usual cause is a wrong splitOn: check that the split column appears in the SELECT exactly where the next object starts.
Several result sets in one round trip
A detail page often needs a record plus a few related lists. QueryMultiple fetches them all in a single round trip instead of three:
const string sql = """
SELECT Id, Name, Email FROM dbo.Customers WHERE Id = @id;
SELECT Id, OrderDate, CustomerId FROM dbo.Orders WHERE CustomerId = @id;
SELECT COUNT(*) FROM dbo.Reviews WHERE CustomerId = @id;
""";
using var multi = await connection.QueryMultipleAsync(sql, new { id });
var customer = await multi.ReadSingleOrDefaultAsync<Customer>();
var orders = (await multi.ReadAsync<Order>()).ToList();
var reviewCount = await multi.ReadSingleAsync<int>();
Read the result sets in the same order the SQL returns them.
Transactions
Open the connection, begin a transaction, and pass it to every command:
await using var connection = factory.Create();
await connection.OpenAsync();
await using var tx = await connection.BeginTransactionAsync();
try
{
var orderId = await connection.QuerySingleAsync<int>(
"""
INSERT INTO dbo.Orders (CustomerId, OrderDate) VALUES (@customerId, SYSUTCDATETIME());
SELECT CAST(SCOPE_IDENTITY() AS int);
""",
new { customerId }, tx);
await connection.ExecuteAsync(
"INSERT INTO dbo.OrderLines (OrderId, Sku, Quantity) VALUES (@orderId, @Sku, @Quantity)",
lines.Select(l => new { orderId, l.Sku, l.Quantity }), tx);
await tx.CommitAsync();
}
catch
{
await tx.RollbackAsync();
throw;
}
Forget to pass tx to one command on a connection with an open transaction and SqlClient throws, saying the command requires a transaction. That error is a useful safety net. TransactionScope also works, but with async code you must create it as new TransactionScope(TransactionScopeAsyncFlowOption.Enabled), or the transaction won't flow across await.
Stored procedures
var orders = await connection.QueryAsync<Order>(
"dbo.GetOrdersByCustomer",
new { CustomerId = 42 },
commandType: CommandType.StoredProcedure);
Output parameters and return values go through DynamicParameters:
var p = new DynamicParameters();
p.Add("@CustomerId", 42);
p.Add("@TotalOrders", dbType: DbType.Int32, direction: ParameterDirection.Output);
p.Add("@ReturnCode", dbType: DbType.Int32, direction: ParameterDirection.ReturnValue);
await connection.ExecuteAsync("dbo.CountOrders", p, commandType: CommandType.StoredProcedure);
int total = p.Get<int>("@TotalOrders");
int code = p.Get<int>("@ReturnCode");
If your data layer is built around procedures, Build an ASP.NET Core Web API with Dapper and SQL Server Stored Procedures shows a complete API on that pattern.
Async, cancellation and streaming
Every main method has an async version: QueryAsync, QueryFirstOrDefaultAsync, QuerySingleAsync, ExecuteAsync, ExecuteScalarAsync and QueryMultipleAsync. Use them in web apps; calling Query instead of QueryAsync in ASP.NET Core blocks a thread while it waits on the database. To pass a cancellation token or a timeout, wrap the command in a CommandDefinition:
app.MapGet("/customers", async (IDbConnectionFactory factory, CancellationToken ct) =>
{
using var connection = factory.Create();
var command = new CommandDefinition(
"SELECT Id, Name, Email FROM dbo.Customers ORDER BY Name",
cancellationToken: ct,
commandTimeout: 15);
return await connection.QueryAsync<Customer>(command);
});
By default Dapper buffers the whole result into a list, which releases the connection quickly. For a huge export, stream the rows instead with QueryUnbufferedAsync, which returns an IAsyncEnumerable<T>:
await using var connection = new SqlConnection(connectionString);
await connection.OpenAsync(ct);
await foreach (var row in connection.QueryUnbufferedAsync<Order>(
"SELECT Id, OrderDate, CustomerId FROM dbo.Orders"))
{
await writer.WriteLineAsync($"{row.Id},{row.OrderDate:O},{row.CustomerId}");
}
An unbuffered query holds the connection and reader open for the whole loop, so keep the work inside it short, and don't run another command on the same connection while you iterate. That needs MARS on SQL Server or, better, a second connection.
Bulk inserts: where most people get caught
Passing a list to Execute looks like a bulk insert. It isn't:
// Runs the INSERT once per item: 10,000 items = 10,000 round trips
await connection.ExecuteAsync(
"INSERT INTO dbo.Stock (Sku, Quantity) VALUES (@Sku, @Quantity)", items, tx);
That is fine for tens or hundreds of rows inside a transaction. For thousands or more, pick a real bulk mechanism:
Option | Best for | Notes |
|---|
Table-valued parameter (TVP) | SQL Server, up to tens of thousands of rows, MERGE/upsert logic | Built into Dapper via AsTableValuedParameter |
SqlBulkCopy
| SQL Server, very large loads | Fastest; not Dapper itself, but works alongside it |
JSON parameter + OPENJSON | SQL Server, flexible shapes | One parameter, parsed on the server |
PostgreSQL COPY or unnest arrays | PostgreSQL | Npgsql supports binary COPY |
Dapper Plus | Teams wanting a ready-made bulk API | Commercial library from ZZZ Projects, a Dapper sponsor |
On SQL Server I would start with a TVP, because it also lets you upsert with MERGE. Define the table type once:
CREATE TYPE dbo.StockItemType AS TABLE (Sku varchar(20) NOT NULL, Quantity int NOT NULL);
var table = new DataTable();
table.Columns.Add("Sku", typeof(string));
table.Columns.Add("Quantity", typeof(int));
foreach (var item in items)
table.Rows.Add(item.Sku, item.Quantity);
await connection.ExecuteAsync(
"""
MERGE dbo.Stock AS t
USING @items AS s ON t.Sku = s.Sku
WHEN MATCHED THEN UPDATE SET t.Quantity = s.Quantity
WHEN NOT MATCHED THEN INSERT (Sku, Quantity) VALUES (s.Sku, s.Quantity);
""",
new { items = table.AsTableValuedParameter("dbo.StockItemType") });
When it is a straight load of a very large set, the same DataTable goes to SqlBulkCopy:
await using var connection = new SqlConnection(connectionString);
await connection.OpenAsync();
using var bulk = new SqlBulkCopy(connection)
{
DestinationTableName = "dbo.Stock",
BatchSize = 5_000
};
bulk.ColumnMappings.Add("Sku", "Sku");
bulk.ColumnMappings.Add("Quantity", "Quantity");
await bulk.WriteToServerAsync(table);
Dapper or EF Core?
Aspect | Dapper | EF Core |
|---|
Who writes SQL | You | EF Core generates it from LINQ (raw SQL also possible) |
Change tracking | None | Yes, with SaveChanges unit of work |
Migrations | None (use DbUp, Flyway, SQL projects) | Built in |
Relationships | Manual multi-mapping | Navigation properties, Include |
Overhead per query | Very low, close to raw ADO.NET | Low with AsNoTracking and compiled queries; higher with tracking |
Database portability | Your SQL is provider-specific | Same LINQ across providers |
Learning curve | Small if you know SQL | Larger: tracking, loading strategies, migrations |
Native AOT | Supported through Dapper.AOT | Needs compiled models and precompiled queries; check current EF Core limits |
Speed is the reason people usually give for Dapper, and for simple queries it does have less overhead, especially against tracked EF Core queries. But the gap is small once EF Core uses AsNoTracking or compiled queries, and the database is usually the bigger cost anyway. Benchmark your own queries before choosing on speed alone.
The better reasons are about the kind of code you write. Dapper suits read-heavy APIs, reporting, complex hand-tuned SQL, existing stored procedures, legacy schemas, and hot paths where every allocation counts. EF Core suits rich domain models with many writes, teams that prefer LINQ, and apps where productivity and migrations matter more than the last microsecond.
You also don't have to pick one. A pattern I like is EF Core for commands (writes) and Dapper for queries (reads), and you can run Dapper on EF Core's own connection with dbContext.Database.GetDbConnection(). For throughput numbers in Web APIs, see High-Throughput Data Access: Choosing Between EF Core and Dapper for Web APIs.
Keeping Dapper fast
- Select only the columns you need.
SELECT * wastes I/O and breaks when columns are added. - Keep SQL text stable. Dapper caches per SQL string, so building SQL with interpolated values creates a new cache entry, and a new SQL Server plan, for every value.
- Give parameters the right types, especially
DbString with IsAnsi = true for varchar columns. - Batch round trips with
QueryMultiple and TVPs instead of loops of single queries. - Stay buffered unless the result is genuinely huge, because buffered queries release the connection sooner.
- Map snake_case columns with
DefaultTypeMap.MatchNamesWithUnderscores = true; instead of aliasing every column. - Profile the database, not just .NET. In most slow Dapper apps, the problem is a missing index or a bad plan, not Dapper.
Dapper.AOT for Native AOT
Classic Dapper generates its mapping code at runtime with reflection and IL emit. That doesn't work under Native AOT and is invisible to the trimmer. Dapper.AOT moves the work to build time with a source generator and C# interceptors, and your Dapper calls stay exactly the same.
The current Dapper.AOT release is 1.1.0, published on September 11, 2026. Building it needs the .NET 8 SDK or later, but your project can still target older frameworks. Enabling it takes three steps: add the package, turn it on for the module, and allow its interceptors namespace in the project file.
dotnet add package Dapper.AOT
// In any C# file: enable Dapper.AOT for the whole project
[module: DapperAot]
<PropertyGroup>
<InterceptorsNamespaces>$(InterceptorsNamespaces);Dapper.AOT</InterceptorsNamespaces>
<!-- Only if you build with the .NET 8 SDK: -->
<InterceptorsPreviewNamespaces>$(InterceptorsPreviewNamespaces);Dapper.AOT</InterceptorsPreviewNamespaces>
<!-- Optional: publish as Native AOT -->
<PublishAot>true</PublishAot>
</PropertyGroup>
Dapper.AOT also ships SQL analyzers. Rule DAP219, for example, warns about SELECT * when it knows you use SQL Server. If you want only the analyzers, without changing runtime behaviour, install Dapper.Advisor instead. One limit to be aware of: your ADO.NET provider must support Native AOT as well, and Dapper.AOT can't promise that part.
Mistakes that cause production bugs
The most serious one is SQL injection through string interpolation. Dapper does not parameterize interpolated strings for you:
// WRONG: user input becomes part of the SQL text
var bad = await connection.QueryAsync<Customer>(
$"SELECT Id, Name, Email FROM dbo.Customers WHERE Name = '{name}'");
// RIGHT: user input is a parameter
var good = await connection.QueryAsync<Customer>(
"SELECT Id, Name, Email FROM dbo.Customers WHERE Name = @name", new { name });
Only values passed through an anonymous object or DynamicParameters are sent as real database parameters. Column and table names can't be parameters, so if you need a dynamic one, such as a sort column, validate it against a fixed allow-list.
Connection handling causes the next batch of problems. Sharing one connection across requests or threads fails because connections are not thread-safe. Forgetting using or await using drains the pool under load. Leaving the transaction parameter off one of several commands inside a transaction makes SqlClient throw.
The last class of bug is the quiet kind. A column name that doesn't match a property silently leaves that property at its default value, so alias the column (AS Name) or enable MatchNamesWithUnderscores. And QueryFirst used where QuerySingle was meant will hide duplicate rows indefinitely.
If you are starting a new data layer, learn QueryAsync, QuerySingleOrDefaultAsync, ExecuteAsync, multi-mapping, QueryMultipleAsync, transactions and DynamicParameters first. That set covers nearly every real-world case, and everything else here is something you add when a specific problem calls for it.
dapperdapper c#dapper tutorialdapper vs ef coredapper multi mappingdapper querymultipledapper stored proceduredapper transactiondapper bulk insertdapper.aot
Arun PottiPosted Nov 22, 2026, 12:37 PM
Good Article.