Ef.Dapper 1.0.0

Ef.Dapper

Entity Framework's developer experience, Dapper's speed.

A lightweight ORM giving you DbContext/DbSet-shaped APIs, lambda queries, joins, grouping, paging and CRUD — all translated to plain parameterised SQL and executed by Dapper. Drop to raw SQL whenever you like; it shares the same context, connection and transaction.

dotnet add package Ef.Dapper

---

Why use Ef.Dapper?

Dapper is fast and predictable, but you hand-write SQL for every insert, update, filter and page. Each of those strings is a place for a typo the compiler cannot catch, and a rename can silently break a query.

EF Core writes that SQL for you, but you take a change tracker, proxies, an identity map and a query pipeline you pay for on every call — and since EF Core 5 it no longer runs on .NET Framework.

Ef.Dapper puts EF's shape on Dapper's engine.

	Dapper	EF Core	Ef.Dapper
CRUD without hand-written SQL	—	✔	✔
Lambda queries: filter, join, group, page	—	✔	✔
DbContext / DbSet API	—	✔	✔
Raw SQL on the same connection and transaction	✔	limited	✔
No change tracker, no hidden writes	✔	—	✔
Runs on .NET Framework 4.6.1+	✔	—	✔
Migrations, lazy loading, navigation properties	—	✔	—
Dependencies	none	several	Dapper

Reach for Ef.Dapper when

• You like Dapper's model — explicit SQL, no magic — but you are tired of writing the CRUD half.
• You want lambda filters, joins, grouping and paging without a change tracker deciding when to write.
• You need .NET Framework support, which EF Core dropped after 3.1.
• Per-query latency matters: 6.5× faster than EF Core on a key lookup, 9× or better on a 100-row insert.
• You want to see the SQL. ToSql() prints exactly what will run, and every value is parameterised.

Stay with Dapper when

• Your queries are complex SQL that a lambda would only obscure.
• You want no abstraction whatsoever between you and the statement you ship.

Stay with EF Core when

• You want migrations, change tracking, lazy loading or navigation properties.
• You load large object graphs: EF's compiled materialisers beat Ef.Dapper and raw Dapper past roughly a thousand rows.

---

At a glance

Dependencies	Dapper, plus Microsoft.Extensions.DependencyInjection.Abstractions for the optional DI helper
Targets	netstandard2.0 and netstandard2.1 — .NET Framework 4.6.1+, .NET Core 2.0+, .NET 5+, Mono, Xamarin
Providers	SQL Server · PostgreSQL · MySQL · SQLite · Oracle (experimental) — or implement ISqlDialect
Tests	291, run against a real database
License	MIT

// Lambda
var admins = await db.Users
    .Where(u => u.IsActive && u.Age >= 18)
    .OrderByDescending(u => u.CreatedAt)
    .Take(20)
    .ToListAsync();

// Raw SQL — same context, same connection, same transaction
var report = await db.QueryAsync<CountryCount>(
    "SELECT Country, COUNT(*) AS Customers FROM Users WHERE IsActive = @active GROUP BY Country",
    new { active = true });

What you get, in one line each:

• No change tracker, no proxies, no identity map. Statements are generated once and cached; property access uses compiled delegates.
• Everything is parameterised. ToSql() shows exactly what will run, so nothing is a mystery.
• Lambda or raw SQL, chosen per query, through one context.
• Maps by attribute, convention or fluent configuration, with a dialect per provider.
• Streams large result sets with AsAsyncEnumerable() instead of buffering.
• Compiled queries translate a hot query once and then vary only by its arguments.

Measured against EF Core

Milliseconds, in-memory SQLite, EF Core with AsNoTracking(). Full tables and method in Performance.

	Ef.Dapper vs EF Core	Reproducibility
Get by key	6.5× faster	stable
Paged query	1.6× faster	stable
Insert one	4–7× faster	varies between runs
Insert 100 rows	9–12× faster	varies between runs
Read 1000 rows	EF Core 2–16% faster	direction stable, margin varies

Turning a lambda into SQL costs about 0.004 ms. Ef.Dapper wins wherever per-query overhead dominates; EF Core wins on bulk materialisation, where its compiled materialisers beat Ef.Dapper and raw Dapper.

These are ratios, not absolutes, because absolute timings moved 20–30% between runs on the same machine — for every contender, raw Dapper included. Write benchmarks vary most, which is why those rows give a range rather than a single figure.

Bring your own ADO.NET provider (Microsoft.Data.SqlClient, Npgsql, MySqlConnector, Microsoft.Data.Sqlite, Oracle.ManagedDataAccess.Core, …). DateOnly and TimeOnly are mapped whenever the host runtime has them, even though the netstandard build cannot name those types.

Getting started

using Ef.Dapper;

[Table("Users")]
public class User
{
    [Key]                                // database-generated primary key
    public int Id { get; set; }

    public string Name { get; set; } = "";

    [Column("email_address")]            // column name differs from the property
    public string? Email { get; set; }

    public int Age { get; set; }
    public bool IsActive { get; set; }
    public DateTime CreatedAt { get; set; }

    [NotMapped]                          // never read or written
    public string Display => $"{Name} <{Email}>";
}

public class AppDbContext : DapperContext
{
    public AppDbContext(DapperOptions options) : base(options) { }

    public DapperSet<User> Users => Set<User>();
}

var options = new DapperOptions()
    .UseSqlServer(() => new SqlConnection(connectionString));

using var db = new AppDbContext(options);

The connection is created lazily and opened on first use.

Two runnable programs are in samples/: a tour of every feature that prints the SQL it generates, and a repository / unit-of-work layout. Both use in-memory SQLite, so dotnet run is all they need.

Dependency injection

builder.Services.AddDapper<AppDbContext>(options => options
    .UsePostgreSql(() => new NpgsqlConnection(connectionString))
    .UseNamingConvention(NamingConventions.SnakeCase)
    .WithCommandTimeout(30)
    .UseRetry(maxAttempts: 3)
    .LogTo(logger.LogDebug));

Registered as Scoped by default; pass a ServiceLifetime to change it.

CRUD

var user = await db.Users.InsertAsync(new User { Name = "Ada", Age = 36 });
// user.Id is now populated from the database

var loaded  = await db.Users.FindAsync(user.Id);          // by primary key
var all     = await db.Users.ToListAsync();
var updated = await db.Users.UpdateAsync(user);           // bool
var deleted = await db.Users.DeleteAsync(user);           // bool

await db.Users.DeleteByKeyAsync(42);
await db.Users.DeleteWhereAsync(u => !u.IsActive);
await db.Users.DeleteAllAsync();

Composite keys work the same way:

var tag = await db.UserTags.FindAsync(new object[] { userId, "beta" });

Bulk operations

// Inserts one row at a time so generated keys come back on each entity
await db.Users.InsertRangeAsync(users);

// Much faster, but the generated keys are not read back. The batching strategy is chosen by
// the dialect: networked providers use multi-row INSERT to save round trips, while an
// in-process one (SQLite) reuses a single prepared statement, which measures far quicker.
await db.Users.InsertRangeAsync(users, populateKeys: false);

await db.Users.UpdateRangeAsync(users);

// One UPDATE statement for every matching row
await db.Users.ExecuteUpdateAsync(u => u.LastSeen < cutoff, new { IsActive = false });

Every write is available synchronously too (Insert, Update, Delete, Find, …).

Lambda queries

Start from a set (or db.Query<User>()), compose, then finish with a terminal operation. Queries are immutable — each operator returns a new query, and nothing runs until you ask for results.

var query = db.Users
    .Where(u => u.IsActive)
    .WhereIf(minimumAge.HasValue, u => u.Age >= minimumAge!.Value)
    .OrderBy(u => u.Name)
    .ThenByDescending(u => u.CreatedAt)
    .Skip(20)
    .Take(10);

var rows = await query.ToListAsync();

Operators

Composition	Terminal (async + sync)
Where, WhereIf	ToListAsync, ToArrayAsync
OrderBy, OrderByDescending	FirstOrDefaultAsync, FirstAsync
ThenBy, ThenByDescending	SingleOrDefaultAsync, SingleAsync
Skip, Take	CountAsync, LongCountAsync, AnyAsync
Distinct	SumAsync, MinAsync, MaxAsync, AverageAsync
Select	ToPagedListAsync, AsAsyncEnumerable
Join, LeftJoin	DeleteAsync, ExecuteUpdateAsync
GroupBy, Having	ToSql, GetParameters

FirstOrDefault, Single, Count and Any also accept a predicate directly:

var ada = await db.Users.FirstOrDefaultAsync(u => u.Name == "Ada");
var n   = await db.Users.CountAsync(u => u.IsActive);

Streaming large result sets

Every terminal so far buffers into a List<T>. When a result set is too large for that, AsAsyncEnumerable() streams rows straight off the reader:

await foreach (var user in db.Users.Where(u => u.IsActive).OrderBy(u => u.Id).AsAsyncEnumerable(ct))
{
    await ProcessAsync(user);
}

It is available on sets, queries, projections, joins and grouped projections, and on raw SQL through db.QueryUnbufferedAsync<T>(sql, param). Nothing runs until the first MoveNextAsync, and breaking out of the loop disposes the reader immediately — only the rows you consume are read.

Two things to keep in mind:

• The connection is busy for the duration. Do not run another query on the same context while iterating; finish or break out of the loop first.
• Cancellation works either through the parameter or .WithCancellation(token).

Projections

Select narrows the SELECT list to only the columns you asked for. Anonymous types, DTOs with settable properties, and positional records all work.

var names = await db.Users.AsQuery().Select(u => u.Name).ToListAsync();          // List<string>

var cards = await db.Users
    .Where(u => u.IsActive)
    .Select(u => new { u.Id, u.Name })
    .OrderBy(u => u.Name)               // still ordered by the source entity's columns
    .ToListAsync();

public record UserCard(int Id, string Name, int Age);
var records = await db.Users.Select(u => new UserCard(u.Id, u.Name, u.Age)).ToListAsync();

Joins

Join and LeftJoin bring in a second (and third) table. Every lambda after the join receives all the joined entities, in the order the tables were added.

var lines = await db.Orders
    .Join<Customer>((o, c) => o.CustomerId == c.Id)
    .Where((o, c) => c.Country == "US" && o.Total > 0)
    .OrderByDescending((o, c) => o.Total)
    .Select((o, c) => new { o.Reference, Customer = c.Name, o.Total })
    .ToListAsync();

SELECT t0.[Reference] AS [Reference], t1.[Name] AS [Customer], t0.[Total] AS [Total]
FROM   [dbo].[Orders] AS [t0]
INNER JOIN [dbo].[Customers] AS [t1] ON (t0.[CustomerId] = t1.[Id])
WHERE  ((t1.[Country] = @p0) AND (t0.[Total] > @p1))
ORDER BY t0.[Total] DESC

Without a Select, you get both entities per row as a tuple:

foreach (var (order, customer) in await db.Orders.Join<Customer>((o, c) => o.CustomerId == c.Id).ToListAsync())
{
    Console.WriteLine($"{order.Reference} — {customer!.Name}");
}

A LeftJoin hands back null for an unmatched right-hand entity:

var rows = await db.Customers
    .LeftJoin<Order>((c, o) => o.CustomerId == c.Id)
    .OrderBy((c, o) => c.Name)
    .ToListAsync();

foreach (var (customer, order) in rows)
    Console.WriteLine($"{customer.Name}: {order?.Reference ?? "(no orders)"}");

Three tables work the same way, and joins may be mixed:

var rows = await db.Users
    .Join<Order>((u, o) => o.UserId == u.Id)
    .LeftJoin<UserTag>((u, o, t) => t.UserId == u.Id)
    .Where((u, o, t) => u.IsActive)
    .Select((u, o, t) => new { u.Name, o.Reference, t.Tag })
    .ToListAsync();

Joined queries support Where, WhereIf, OrderBy/ThenBy, Skip, Take, Distinct, Select, ToList, FirstOrDefault, Count, Any, Sum/Min/Max/Average, ToPagedList, ToSql.

Two details worth knowing:

• Each entity is read from its own slice of the result set, by column position rather than Dapper's splitOn. Two tables that both have an Id column need no special handling.
• Filter and order before Select. Once projected, the source entities are no longer in scope, so the projected query only offers Skip, Take, Distinct and the terminals.

Single-table queries generate unaliased SQL exactly as before; aliases (t0, t1, …) appear only once a join is present — including in predicates that were added before the join, because translation happens when the query is built, not when the operator is called.

Grouping

GroupBy produces a query whose Having, OrderBy and Select lambdas receive a group rather than a row — g.Key for the grouping key, g.Count() / g.Sum(...) / g.Min / g.Max / g.Average for aggregates.

var perCustomer = await db.Orders
    .Where(o => o.PlacedAt >= since)
    .GroupBy(o => o.CustomerId)
    .Having(g => g.Count() > 1)
    .OrderByDescending(g => g.Sum(o => o.Total))
    .Select(g => new { CustomerId = g.Key, Orders = g.Count(), Total = g.Sum(o => o.Total) })
    .ToListAsync();

SELECT   [CustomerId] AS [CustomerId], COUNT(*) AS [Orders], SUM([Total]) AS [Total]
FROM     [dbo].[Orders]
WHERE    ([PlacedAt] >= @p0)
GROUP BY [CustomerId]
HAVING   (COUNT(*) > @p1)
ORDER BY SUM([Total]) DESC

Group by several columns with an anonymous key, then read the members off g.Key:

var breakdown = await db.Users
    .GroupBy(u => new { u.Country, Year = u.CreatedAt.Year })
    .Select(g => new { g.Key.Country, g.Key.Year, People = g.Count() })
    .ToListAsync();

The key can be any translatable expression (u.CreatedAt.Year becomes DATEPART/EXTRACT/strftime), and g.Count(predicate) becomes a conditional count:

.Select(g => new { g.Key, Total = g.Count(), Active = g.Count(u => u.IsActive) })
// COUNT(*) ... COUNT(CASE WHEN [IsActive] = 1 THEN 1 END)

Grouping composes with joins — aggregate selectors then take all the joined entities:

var spenders = await db.Orders
    .Join<Customer>((o, c) => o.CustomerId == c.Id)
    .GroupBy((o, c) => c.Country)
    .Select(g => new { Country = g.Key, Revenue = g.Sum((o, c) => o.Total) })
    .ToListAsync();

CountAsync() on a grouped query counts groups, not rows (it wraps the query in a derived table), and ToPagedListAsync reports the group total. ToKeyListAsync() returns just the keys. Paging a grouped query with no explicit ordering falls back to ordering by the key, so pages stay stable.

IDapperGrouping<TKey, T> deliberately exposes only what SQL can express, so an untranslatable operation is a compile error rather than a runtime one. Everything after GroupBy operates on groups: use Where before it to filter rows, Having after it to filter groups.

What translates

C#	SQL
u.Age > 18 && (u.IsActive \|\| u.Name == "root")	(Age > @p0 AND (IsActive = 1 OR Name = @p1))
u.Email == null, u.ManagerId.HasValue	IS NULL / IS NOT NULL
u.IsActive, !u.IsActive	IsActive = 1, NOT (IsActive = 1)
ids.Contains(u.Id)	Id IN @p0 (Dapper expands the list)
u.Name.Contains/StartsWith/EndsWith	LIKE with wildcards escaped
...StartsWith(x, StringComparison.OrdinalIgnoreCase)	UPPER(Name) LIKE @p0
string.IsNullOrEmpty(u.Email)	(Email IS NULL OR Email = '')
u.Name.ToUpper/ToLower/Trim/Substring/Replace/IndexOf	provider's scalar functions
u.Name.Length	LEN(Name) / LENGTH(Name)
u.CreatedAt.Year/Month/Day/Hour/…	DATEPART / EXTRACT / strftime
u.Age + 1, u.Total * 1.2m, a ?? b	arithmetic, COALESCE
Math.Abs/Round/Floor/Ceiling/Pow/Sqrt	scalar functions
enums, captured locals, captured null	parameters (a captured null becomes IS NULL)

Anything else throws a DapperException naming the expression, so you find out at the call site rather than getting silently wrong SQL. When that happens, use raw SQL.

Seeing the SQL

var sql = db.Users.Where(u => u.Age > 18).Take(5).ToSql();
// SELECT [Id], [Name], [email_address] AS [Email], [Age] ...
// FROM [dbo].[Users] WHERE ([Age] > @p0)
// ORDER BY (SELECT NULL) OFFSET 0 ROWS FETCH NEXT @p1 ROWS ONLY

var parameters = db.Users.Where(u => u.Age > 18).GetParameters();

Or log everything: options.LogTo(Console.WriteLine).

Compiled queries

A normal lambda query walks its expression tree on every call. For a hot path, compile it once and keep the result in a static field — the tree is translated the first time it runs, and after that each call only binds its arguments.

static readonly CompiledQuery<User, int> OlderThan =
    CompiledQuery.Compile<User, int>((q, minAge) =>
        q.Where(u => u.Age > minAge).OrderBy(u => u.Name));

var adults = await OlderThan.ToListAsync(db, 18);
var elders = await OlderThan.ToListAsync(db, 65);   // same statement, new value

Every shape the plan can take is compiled separately, on first use:

await OlderThan.ToListAsync(db, 18);
await OlderThan.FirstOrDefaultAsync(db, 18);
await OlderThan.CountAsync(db, 18);
await OlderThan.AnyAsync(db, 18);
OlderThan.ToList(db, 18);                            // synchronous
OlderThan.ToSql(db);                                 // ... WHERE ([Age] > @arg0) ...

Up to two arguments, in the order you declare them:

static readonly CompiledQuery<User, int, bool> Cohort =
    CompiledQuery.Compile<User, int, bool>((q, minAge, active) =>
        q.Where(u => u.Age > minAge && u.IsActive == active));

await Cohort.ToListAsync(db, 18, true);

Only the arguments vary. Anything else in the lambda — a captured local, a DateTime.UtcNow, a literal — is read once and baked into the statement, the same rule EF Core's compiled queries follow. If a value changes between calls, it has to be an argument.

// Wrong: `today` is frozen at first execution.
var today = DateTime.UtcNow.Date;
CompiledQuery.Compile<User, int>((q, minAge) => q.Where(u => u.Age > minAge && u.CreatedAt < today));

// Right: pass it in.
CompiledQuery.Compile<User, int, DateTime>((q, minAge, before) =>
    q.Where(u => u.Age > minAge && u.CreatedAt < before));

A collection argument works as an IN list, because Dapper expands it per command rather than at translation time. Extension methods cannot see the implicit conversion, so write .Value there:

static readonly CompiledQuery<User, int[]> ByIds =
    CompiledQuery.Compile<User, int[]>((q, ids) => q.Where(u => ids.Value.Contains(u.Id)));

await ByIds.ToListAsync(db, new[] { 1, 2, 3 });      // Id IN @arg0

The compiled query is thread-safe and holds no connection, so a static field shared across contexts is the intended use. It runs on whichever context you hand it, joining that context's transaction. Plans are cached per dialect, naming convention, model and schema, so one instance serves several providers.

It saves a fixed ~0.0045 ms and 6.1 KB per call, which is a third of a single-row lookup and nothing at all on a large result set — see the measurements.

Arguments that can be null are compared the way C# compares them. A captured null becomes IS NULL, but an argument has no value when the statement is built, and SQL's = never matches null. So an equality against a nullable argument is widened — but only when the column can hold null too:

public string  Name  { get; set; }    // NOT NULL
public string? Email { get; set; }    // nullable

CompiledQuery.Compile<User, string?>((q, email) => q.Where(u => u.Email == email));
// (("Email" IS NULL AND @arg0 IS NULL) OR ("Email" IS NOT NULL AND @arg0 IS NOT NULL AND "Email" = @arg0))

CompiledQuery.Compile<User, string>((q, name) => q.Where(u => u.Name == name));
// ("Name" = @arg0)

Passing null for the email finds the rows that have none, as the equivalent lambda would. Against a NOT NULL column the two rules already agree — no row holds null, so neither matches a null argument — and the plain comparison stays index-friendly.

Nullability comes from the property: the CLR type for a value type, the nullable-reference annotation for a reference type. A project with nullable reference types switched off tells Ef.Dapper nothing, so every string column counts as nullable; say so explicitly where it matters:

modelBuilder.Entity<User>()
    .Property(u => u.Name).IsRequired()       // never null: keep the plain '='
    .Property(u => u.Email).IsOptional();     // holds null: widen the comparison

Pages are arguments too. Skip and Take have overloads taking a QueryParameter<int>, so a paged endpoint compiles to one statement no matter how deep the caller scrolls:

static readonly CompiledQuery<User, int, int> Page =
    CompiledQuery.Compile<User, int, int>((q, skip, take) =>
        q.OrderBy(u => u.Name).Skip(skip).Take(take));

await Page.ToListAsync(db, skip: 40, take: 20);

A negative value cannot be rejected when the query is compiled, unlike Skip(int)/Take(int) — it reaches the database instead. Shapes that impose their own limit (FirstOrDefaultAsync) override the argument rather than fighting it.

One thing a compiled query still cannot carry: a projection, join or GroupBy. Compile hands you the entity query, so those stay as ordinary lambdas.

Pagination

PagedResult<User> page = await db.Users
    .Where(u => u.IsActive)
    .OrderBy(u => u.Name)
    .ToPagedListAsync(page: 2, pageSize: 20);

page.Items;            // IReadOnlyList<User>
page.TotalCount;       // total rows ignoring paging
page.TotalPages;
page.HasPreviousPage;
page.HasNextPage;
page.Map(u => new UserDto(u));   // reshape items, keep the metadata

One COUNT plus one windowed SELECT. An empty result short-circuits the second query.

The page and size are parameters, not literals. Walking a list page by page therefore runs one statement with different values rather than a new statement per offset, so the server keeps a single cached plan instead of one per page. This applies to the lambda API and to raw SQL alike; raw paging adds @dapperef_skip and @dapperef_take alongside whatever parameters you passed.

Limits Ef.Dapper imposes on its own behalf stay literal — the single row behind FirstOrDefault and the existence probe behind Any emit LIMIT 1, not a parameter. They never vary, so a parameter could not help the plan cache, and a literal leaves the optimiser an exact row goal.

Raw SQL can be paged too:

var page = await db.QueryPagedAsync<User>(
    "SELECT * FROM Users WHERE Country = @country ORDER BY Name",
    page: 1, pageSize: 25,
    parameters: new { country = "US" });

Raw SQL

Everything on the context participates in the ambient transaction and uses the configured timeout.

await db.QueryAsync<User>("SELECT * FROM Users WHERE Age > @min", new { min = 18 });
await db.QueryFirstOrDefaultAsync<User>("SELECT TOP 1 * FROM Users ORDER BY Id DESC");
await db.QuerySingleOrDefaultAsync<int>("SELECT Age FROM Users WHERE Id = @id", new { id });
await db.ExecuteAsync("UPDATE Users SET IsActive = 0 WHERE Id = @id", new { id });
await db.ExecuteScalarAsync<long>("SELECT COUNT(*) FROM Users");

// Multi-mapping
await db.QueryAsync<Order, User, Order>(sql, (o, u) => { o.User = u; return o; }, splitOn: "Id");

// Raw SQL that returns entities, mapped through the entity's column mapping
await db.Users.FromSqlAsync("SELECT * FROM Users WHERE Name LIKE @pattern", new { pattern = "A%" });

Several result sets in one round trip

QueryMultipleAsync returns Dapper's GridReader. Read the sets back in the same order the statements appear, then dispose it before running anything else on the context:

using var grid = await db.QueryMultipleAsync(
    """
    SELECT Id, Name, email_address AS Email FROM Users ORDER BY Name;
    SELECT Id, UserId, Reference, Total FROM Orders ORDER BY Reference;
    """);

var users  = (await grid.ReadAsync<User>()).ToList();
var orders = (await grid.ReadAsync<Order>()).ToList();

It uses the context's connection and ambient transaction, so it sees uncommitted work in the same unit of work. ReadSingleAsync<T>() and ReadFirstAsync<T>() are there for scalar sets.

Two things to watch. The grid holds the connection open until disposed, so no other query can run on that context in the meantime — the same rule as AsAsyncEnumerable(). And because this is raw SQL, Ef.Dapper's column mapping does not apply: alias renamed columns yourself, which is why email_address AS Email appears above.

Parameters are always ADO.NET parameters — Ef.Dapper never concatenates values into SQL.

Transactions

await using var transaction = await db.BeginTransactionAsync();
await db.Users.InsertAsync(user);
await db.ExecuteAsync("INSERT INTO Audit (Action) VALUES (@action)", new { action = "created" });
await transaction.CommitAsync();
// disposing without committing rolls back

The whole path is asynchronous: BeginTransactionAsync uses DbConnection.BeginTransactionAsync, and CommitAsync/RollbackAsync use DbTransaction.CommitAsync/RollbackAsync, so no thread blocks on the database. Providers that only expose the synchronous ADO.NET API fall back to it automatically. Synchronous Commit(), Rollback() and Dispose() remain available.

Or let the helper handle it:

var id = await db.UseTransactionAsync(async () =>
{
    var user = await db.Users.InsertAsync(new User { Name = "Ada" });
    await db.Orders.InsertAsync(new Order { UserId = user.Id });
    return user.Id;
});   // commits on success, rolls back on exception

Nested scopes join the outermost transaction. Only the outermost commits; a rollback anywhere prevents the outer commit.

Async disposal

DapperContext and DapperTransaction implement both IDisposable and IAsyncDisposable, so await using disposes the transaction and the connection without blocking:

await using var db = new AppDbContext(options);

Disposing a context that still has an open transaction rolls it back first, asynchronously when the provider supports it. On netstandard2.0 the interface comes from Microsoft.Bcl.AsyncInterfaces — which Dapper already brings in — so the API is identical on both targets; only the underlying calls differ.

Mapping

Resolved once per entity type and cached.

Attributes

Attribute	Effect
[Table("name", Schema = "dbo")]	table and schema
[Key]	primary key, value generated by the database
[ExplicitKey]	primary key, value assigned by your code (Guids, natural keys, composite keys)
[Column("name")]	column name
[NotMapped]	excluded entirely
[Computed]	read on SELECT, never written
[Immutable]	written on INSERT, never on UPDATE
[Sequence("NAME")]	the sequence generated keys come from, on providers that use one (Oracle)

System.ComponentModel.DataAnnotations attributes ([Table], [Column], [Key], [NotMapped], [DatabaseGenerated]) are recognised too, read reflectively — no package reference added.

Conventions

Without attributes: the table name is the pluralised type name, columns match property names, and a property called Id (or <Type>Id) becomes the key — generated when it is an integral type.

options.UseNamingConvention(NamingConventions.SnakeCase);   // Categories -> categories, UserId -> user_id

Built in: Exact, Pluralized (default), SnakeCase, SnakeCaseSingular, UpperSnakeCase. Implement INamingConvention for anything else.

Only properties of mappable types are considered (primitives, string, decimal, DateTime, DateTimeOffset, TimeSpan, Guid, enums, byte[], DateOnly/TimeOnly, and Nullable<> of those), so navigation properties and collections are ignored automatically.

Fluent mapping

protected override void OnModelCreating(DapperModel model)
{
    model.Entity<Category>()
        .ToTable("categories", "catalog")
        .HasKey(c => c.Key)
        .Property(c => c.Key).HasColumnName("id")
        .Property(c => c.Caption).HasColumnName("title")
        .Property(c => c.RowVersion).IsComputed()
        .Property(c => c.Caption).IsRequired();      // the column never holds null
}

Also available on options (options.ConfigureModel(...)) and as reusable IEntityConfiguration<T> classes.

IsRequired() / IsOptional() state whether a column holds null. Ef.Dapper otherwise reads that from the property — the CLR type for a value type, the nullable-reference annotation for a reference type — and assumes nullable when a project has those annotations switched off. It affects how a compiled query's nullable argument is compared; see compiled queries.

Dialects

SqlDialect.SqlServer (default), PostgreSql, MySql, Sqlite, Oracle. The dialect controls identifier quoting, the parameter prefix, paging (OFFSET/FETCH vs LIMIT/OFFSET), how a generated key is obtained, boolean literals, scalar function names and the batch parameter budget.

How far each one is verified. SQLite is exercised end to end by the test suite against a real database. SQL Server, PostgreSQL and MySQL are covered by assertions on the generated SQL, which catches translation errors but not provider behaviour. Oracle is experimental — see below.

options.UseSqlServer(() => new SqlConnection(cs));
options.UsePostgreSql(() => new NpgsqlConnection(cs));
options.UseMySql(() => new MySqlConnection(cs));
options.UseSqlite(() => new SqliteConnection(cs));
options.UseOracle(() => new OracleConnection(cs));

// Anything else
options.UseConnectionFactory(() => new OtherConnection(cs)).UseDialect(new MyDialect());

Implement ISqlDialect (or derive from SqlDialectBase and override the handful of members that differ) to add a provider.

ApplyPaging receives the offset and row limit as rendered SQL fragments — normally parameter references such as @p2 — rather than as numbers, which is what keeps a paged query to one plan. Append them as given; do not parse them back into integers.

Generated keys

Most providers read the key back after the INSERT — SCOPE_IDENTITY(), RETURNING, LAST_INSERT_ID(), last_insert_rowid(). Oracle draws it from a sequence before the row is written, which the dialect declares with FetchesIdentityBeforeInsert. Ef.Dapper then fetches the value, assigns it to the entity, and includes the key column in the INSERT — so InsertAsync still comes back with a populated key and calling code does not change.

The sequence name comes from [Sequence("NAME")], from modelBuilder.Entity<T>().UseSequence("NAME"), or from the dialect's default of {TABLE}_SEQ.

Oracle notes

Experimental. The Oracle dialect has never been run against a live Oracle instance. Its generated SQL is unit-tested and the sequence-key mechanism is verified end to end against a stand-in provider, but ODP.NET's type coercion is untested. One known risk: Oracle has no boolean type, so binding a bool parameter may throw and need a Dapper type handler. Treat it as a starting point and run your own integration tests before relying on it.

• Bind variables use :, which Dapper understands.
• Identifiers are quoted, and quoting makes Oracle case-sensitive. Since unquoted Oracle names fold to upper case, pair UseOracle with NamingConventions.UpperSnakeCase unless your tables are genuinely mixed case.
• Date parts render as TO_NUMBER(TO_CHAR(x, 'YYYY')) rather than EXTRACT, because EXTRACT rejects time fields on DATE columns. DayOfWeek throws instead of guessing: Oracle's D mask is numbered by NLS_TERRITORY, so it cannot be mapped onto System.DayOfWeek reliably.
• Oracle has no multi-row VALUES syntax, so InsertRangeAsync(..., populateKeys: false) runs one statement per row instead of batching.
• A compiled query with a nullable argument names that bind variable more than once, which needs BindByName = true on the ODP.NET command. Non-nullable arguments name it once. This is untested against a live instance, like everything else here.

Targeting several providers with one context

The dialect lives on the options, so the same context class runs against any provider — swap the registration, not the code. Entity maps are cached per (type, naming convention, model, default schema), so two providers in one process never share a map.

Keep the schema out of your entities. [Table("Users", Schema = "dbo")] compiles that schema into the type and applies it to every provider, so PostgreSQL would look for "dbo"."Users". Set it per registration instead — an explicit schema still wins, and leaving both unset is portable:

services.AddDapper<ShopContext>(o => o
    .UseSqlServer(() => new SqlConnection(cs))
    .UseDefaultSchema("sales"));

services.AddDapper<ShopContext>(o => o
    .UsePostgreSql(() => new NpgsqlConnection(cs))
    .UseNamingConvention(NamingConventions.SnakeCase));   // no schema: use the search_path

When differences outgrow options, branch inside OnModelCreating; the context exposes Dialect, and the model is built once per options instance.

Multi-tenant caveat. AddDapper runs its configure delegate once, at registration, so resolve anything per-request inside the connection factory — that runs on every open. Different providers per tenant need one options object per provider, kept as singletons so map caching survives. Note the map cache is keyed partly by default schema, so schema-per-tenant retains a map per tenant per entity: prefer one schema plus a tenant column when tenant counts are large.

Without a context

If you already have an IDbConnection, use it directly:

DapperDefaults.Dialect = SqlDialect.PostgreSql;   // once at start-up

using var connection = new NpgsqlConnection(cs);

var user   = await connection.InsertAsync(new User { Name = "Ada" });
var loaded = await connection.GetAsync<User>(user.Id);
var active = await connection.From<User>().Where(u => u.IsActive).ToListAsync();
var set    = connection.Table<User>();               // the full CRUD surface

await connection.DeleteWhereAsync<User>(u => !u.IsActive);

Each extension also accepts an explicit dialect and transaction.

Threading

A context is not thread-safe. It owns one connection and tracks the ambient transaction in ordinary fields, so two operations running on the same instance at once corrupt that state and break the ADO.NET contract — exactly the rule Entity Framework's DbContext has. Use one context per unit of work; the default Scoped registration already gives each request its own.

The trap worth naming, because it looks harmless:

// WRONG - both run on one connection at the same time
var (users, orders) = await Task.WhenAll(db.Users.CountAsync(), db.Orders.CountAsync());

// Right - await in turn, or give each branch its own context
var users  = await db.Users.CountAsync();
var orders = await db.Orders.CountAsync();

Connection lifetime

By default a context opens its connection on first use and holds it until disposal. That is right for a short unit of work, but in a web request that queries, calls an HTTP API, then queries again, it pins a pooled connection for the whole request — and pool exhaustion shows up as timeouts long before query time becomes the problem.

services.AddDapper<ShopContext>(o => o
    .UseSqlServer(() => new SqlConnection(cs))
    .CloseConnectionsWhenIdle());          // return it to the pool between operations

With this on, the connection goes back to the pool as each operation finishes and is taken again for the next one — a few microseconds per operation. It is held regardless while a transaction is open, while a streamed enumeration is running, and while a GridReader from QueryMultipleAsync is alive; completing the transaction or finishing the enumeration releases it.

Leave it off for SQLite Mode=Memory, where closing the last connection discards the database.

Everything outside a context is safe to share. Entity maps, generated statements, compiled property accessors and row binders live in static concurrent caches, so contexts on different threads reuse the same resolved metadata without locking.

Two things to set once at start-up rather than mid-flight: DapperDefaults (process-wide static state, used by the connection extensions) and any ConfigureModel / OnModelCreating mapping. The model is built once per options instance under a lock; reconfiguring it after queries have run is a race.

Retrying transient failures

Networked databases drop connections: a failover, a throttled pool, a deadlock victim. Retrying is off by default, because repeating a statement is only safe when you know what it did.

options.UseRetry(maxAttempts: 3, baseDelay: TimeSpan.FromMilliseconds(200));

That covers opening a connection, which is the one step that can be retried without thinking: if the open fails, nothing ran. It is also where most transient failures land. Backoff doubles each attempt with jitter, so a fleet coming back from an outage does not stampede.

Nothing else retries on its own. Once a statement is in flight, a lost connection leaves its outcome unknown — the server may have committed before the network died — and a silent retry would write twice. So the unit that gets repeated is one you define:

await db.ExecuteWithRetryAsync(async ct =>
{
    using var transaction = await db.BeginTransactionAsync(cancellationToken: ct);

    await db.Orders.InsertAsync(order, ct);
    await db.Inventory.ExecuteUpdateAsync(i => i.Sku == order.Sku, new { Reserved = true }, ct);

    transaction.Commit();
}, cancellationToken);

Begin the transaction inside the block, so a failed attempt rolls back and the next one starts clean. Calling it while a transaction is already open throws, because retrying a fragment of a transaction that has already died cannot work. Reads are always safe to wrap; for writes, make the operation idempotent or able to recognise its own earlier work.

What counts as transient is read off the exception without Ef.Dapper referencing any provider: SqlState on PostgreSQL, SqliteErrorCode on SQLite, Number on SQL Server and MySQL — connection failures, failovers, resource limits, deadlocks and serialisation failures. A constraint violation or a bad column name is never retried. Override the judgement, or the whole mechanism, if you need to:

options.UseRetry(isTransient: ex => ex is MyProviderException { IsRecoverable: true });
options.UseRetryPolicy(new MyPolicy());     // an IRetryPolicy of your own, e.g. wrapping Polly

Caching

Ef.Dapper caches the work it would otherwise repeat: entity maps, generated CRUD statements, column lists, row binders and compiled-query plans. They live for the life of the process and are keyed partly by naming convention, model and default schema.

For a normal application that is a few dozen entries that never change. A schema-per-tenant design is the case that would otherwise grow without limit — the same entity cached once per tenant — so each cache holds a bounded number of entries and drops its least recently used when it overflows:

DapperCache.Capacity = 4096;   // entries per cache, default 1024. Set once at start-up.

Eviction costs only rebuild time, never correctness — an evicted entry is regenerated on next use. Size it so your working set fits: roughly entities × schemas for the map and statement caches. A cache that evicts on every call costs more than it saves.

Nothing needs clearing by hand, but the caches can be released outright when a whole set of schemas is retired: EntityMapFactory.ClearCache(), SqlGenerator.ClearCache(), RowMaterializer.ClearCache(), and ClearPlans() on an individual compiled query.

Performance

BenchmarkDotNet, .NET 8, in-memory SQLite, 1000 users / 500 orders. All times in milliseconds, lower is better. Raw Dapper is included as a floor: it shows what Ef.Dapper costs on top of the thing it wraps. EF Core uses AsNoTracking(), because Ef.Dapper has no change tracker — tracked EF is measured separately below.

Reads

Case	Dapper	Ef.Dapper	EF Core
Get by key	0.0098	0.0107	0.0705
Filter + order, 50	0.134	0.150	0.221
Paged (count + page)	0.154	0.188	0.299
Group by	0.169	0.176	0.226
Join, 500 rows	0.275	0.289	0.316
Project to DTO, 1000	0.378	0.418	0.362
Whole table, 1000	1.320	1.394	1.168

Writes

Case	Dapper	Ef.Dapper	EF Core
Insert one	0.081	0.073	0.511
Update one	0.050	0.103	0.332
Insert 100, keys read back	1.289	3.460	—
Insert 100, no keys	1.289	1.209	11.144

Change tracking, 1000 rows

	Time
Ef.Dapper	2.744
EF Core AsNoTracking()	2.225
EF Core tracked (default)	6.489

Ef.Dapper overhead per query

	Time
Compose query object	0.0016
Compose + translate + parameters	0.0042

Compiled queries vs the same query as a lambda

Each pair runs a byte-identical statement — the benchmark asserts that before measuring — so the only difference is that the lambda re-translates its expression tree on every call.

Case	lambda	compiled	Ratio	Allocated, lambda → compiled
SQL only, no database	0.0045	0.00004	0.009	6,112 B → 40 B
Single row by key	0.0169	0.0115	0.68	6,576 B → 2,776 B
50 rows, filtered + ordered	0.160	0.145	0.91	28,696 B → 23,080 B
Filtered count	0.0490	0.0505	1.03	4,936 B → 1,912 B
~750 rows	1.656	1.586	0.96	305,387 B → 301,161 B

Translation costs about 0.0045 ms and 6.1 KB per call, and compiling removes essentially all of it — 40 bytes instead of 6,112. That saving is fixed, so what it is worth depends on how much database work sits beside it: a third off a single-row lookup, 9% off a 50-row page, and nothing measurable once the result set is large.

The allocation column is the reliable one. Across two runs of this suite the allocation ratios repeated exactly (0.007, 0.42, 0.80, 0.39, 0.99) while the timings moved: the count case measured 0.90 in one run and 1.03 in the next, and the 750-row case 1.04 then 0.96. Both of those sit inside their own error bars — the count's ratio standard deviation is 0.13 — and the compiled count allocated an identical 1,912 B in both runs, so it was doing identical work either way. Where the fixed saving is small next to the query, expect the clock to say nothing conclusive.

Compile the queries on your hot, small-result paths. On anything that returns hundreds of rows, the lambda is already as fast and reads better.

What the numbers say

Ef.Dapper wins where per-query overhead dominates; EF Core wins on bulk materialisation at 1000 rows. Tracked EF — the default an application gets — costs about 2.9× the same query with AsNoTracking(). Translating a lambda costs ~0.0042 ms, which is why Ef.Dapper sits just above raw Dapper.

Read these as an upper bound. In-memory SQLite has no network, so ORM overhead is a far larger share of total time than in production; against a server where one round trip is ~1 ms, most of these differences disappear into it.

Absolute times are machine-specific. Re-running this suite on the same machine moved every figure by 20–30%, raw Dapper included, so compare the ratios rather than the milliseconds. The read ratios, the per-query overhead and "EF Core wins on 1000 rows" reproduced closely; the write ratios did not — Insert one measured 7.0× in one run and 3.9× in another, with BenchmarkDotNet reporting a ratio standard deviation above 1.0. Treat the write multipliers as order-of-magnitude, not precise.

Two footnotes: "Filter + order" is the median, not the outlier-inflated mean; and "Update one" loads the row first in Ef.Dapper and EF Core but not in the raw-Dapper baseline, so only those two compare directly. Method and reproduction steps: benchmarks/Ef.Dapper.Benchmarks.

Notes and limits

• No change tracking. UpdateAsync writes every non-key, non-computed column. Use ExecuteUpdateAsync for partial updates.
• Joins are explicit. Join/LeftJoin cover up to three tables with an explicit ON condition. There is no GroupJoin, no SelectMany, and no automatic relationship discovery; for anything more elaborate, use raw SQL with Dapper multi-mapping.
• Bulk ExecuteUpdate/Delete operate on a single table and reject joined queries, because aliased UPDATE/DELETE syntax differs too much between providers to generate safely.
• GroupBy covers the SQL shape, not LINQ's. There is no grouping into nested row sets: a grouped query must be projected to key and aggregate values, which is what GROUP BY actually returns. Bulk ExecuteUpdate/Delete reject grouped queries.
• SQLite and decimal: Microsoft.Data.Sqlite stores decimal as TEXT, and SQLite ranks TEXT above REAL, so comparing a decimal column or aggregate against a decimal parameter silently matches nothing. Compare as double (g.Sum(o => (double)o.Total) > 100d) or store money as INTEGER cents. Providers with a real decimal type are unaffected.
• Oracle support is experimental and unverified against a live instance; see Oracle notes.
• No lazy loading or navigation properties. Related collections are ignored by the mapper.
• SingleOrDefault fetches two rows and throws if both come back.
• Sum/Min/Max on an empty set return default (0 for int), because SQL returns NULL rather than throwing. Project a nullable type — SumAsync(u => (int?)u.Age) — to tell the two apart. AverageAsync always returns double?.
• ExecuteUpdate/Delete reject Skip, Take, OrderBy and Distinct instead of quietly ignoring them.
• Compiled queries cover entity queries only. CompiledQuery.Compile hands you the entity query, so a projection, join or GroupBy stays an ordinary lambda; there is no streaming shape, and ToSql renders the list shape. Up to two arguments.
• Caches evict rather than grow without limit. See Caching.
• Nothing retries by default. Opening a connection retries once a policy is configured; a statement already in flight never does. See Retrying transient failures.
• Not compatible with Native AOT, and not trim-safe. Ef.Dapper maps entities by reflection and compiles property accessors and row binders as expression trees, and Dapper generates its materialisers as IL at runtime — System.Reflection.Emit is unavailable under Native AOT, in every build Dapper ships. Trimming can also remove entity members nothing statically references. For an AOT or aggressively trimmed application, use Dapper.AOT, whose source generator exists for exactly this reason.

License

MIT.