EF Core vs Dapper: one API, two ways to talk to SQL Server
“Should we use EF Core or Dapper?” comes up on almost every .NET team, and it’s usually argued in the abstract. I wanted something concrete, so I used Claude, an AI assistant, to build a small repo that answers it with code: one API, one database, implemented twice.
- Entity Framework Core is a full ORM. You describe your model in C#, write queries in LINQ, and EF Core generates the SQL, tracks changes and manages transactions for you.
- Dapper is a micro-ORM. You write the SQL yourself, and Dapper does one job very well: it binds parameters and maps result rows onto C# objects.
The API manages customers and their orders on .NET 10 and SQL Server. Every endpoint exists twice, at /ef/customers and /dapper/customers, and both return identical JSON.
The shape of the solution
The trick that makes a fair comparison possible is a single interface. The endpoints only ever talk to ICustomerStore, and there are two implementations of it:
public interface ICustomerStore
{
Task<IReadOnlyList<CustomerSummary>> ListAsync(CancellationToken ct = default);
Task<CustomerDetails?> GetAsync(int id, CancellationToken ct = default);
Task<int> CreateAsync(CreateCustomerRequest request, CancellationToken ct = default);
Task<int?> AddOrderAsync(int customerId, CreateOrderRequest request, CancellationToken ct = default);
Task<bool> UpdateEmailAsync(int id, string email, CancellationToken ct = default);
Task<bool> DeleteAsync(int id, CancellationToken ct = default);
Task<IReadOnlyList<TopCustomer>> TopCustomersAsync(int count, CancellationToken ct = default);
}
Because the endpoints are written against the interface, they’re mapped once as a generic group and registered for each store:
app.MapCustomers<EfCustomerStore>("ef");
app.MapCustomers<DapperCustomerStore>("dapper");
One detail matters more than it looks: the schema comes from the EF Core model. AppDbContext defines the tables, the unique index on email and the cascading foreign key, and Dapper reads and writes those same tables. Neither side gets a schema tailored to it.
Reading data
A list with a computed column
The customer list includes each customer’s order count. In EF Core, you project straight into the response record and EF Core translates Orders.Count() into SQL:
await db.Customers
.OrderBy(c => c.Name).ThenBy(c => c.Id)
.Select(c => new CustomerSummary(c.Id, c.Name, c.Email, c.Orders.Count()))
.ToListAsync(ct);
Projecting with Select is the single most useful EF Core habit. EF Core fetches only the columns it needs and doesn’t track the results, so you avoid the classic mistake of loading whole entities just to show three fields.
With Dapper you write the query, and Dapper maps each row onto the same record by matching column names to constructor parameters:
const string sql = """
SELECT c.Id, c.Name, c.Email,
(SELECT COUNT(*) FROM Orders o WHERE o.CustomerId = c.Id) AS OrderCount
FROM Customers c
ORDER BY c.Name, c.Id;
""";
await using var conn = connections.Create();
return (await conn.QueryAsync<CustomerSummary>(new CommandDefinition(sql, cancellationToken: ct))).AsList();
A customer with their orders
Loading a parent with its children shows the biggest difference in style. EF Core handles the nested collection inside a single LINQ projection:
await db.Customers
.Where(c => c.Id == id)
.Select(c => new CustomerDetails(c.Id, c.Name, c.Email, c.CreatedUtc,
c.Orders.OrderBy(o => o.PlacedUtc)
.Select(o => new OrderDto(o.Id, o.Total, o.PlacedUtc))
.ToList()))
.SingleOrDefaultAsync(ct);
Dapper has no idea what a “customer with orders” is. Instead, the store sends two SELECT statements in one batch and read the result sets back in turn with QueryMultiple, which is still a single round trip:
const string sql = """
SELECT Id, Name, Email, CreatedUtc FROM Customers WHERE Id = @Id;
SELECT Id, Total, PlacedUtc FROM Orders WHERE CustomerId = @Id ORDER BY PlacedUtc, Id;
""";
using var results = await conn.QueryMultipleAsync(new CommandDefinition(sql, new { Id = id }, cancellationToken: ct));
var customer = await results.ReadSingleOrDefaultAsync<CustomerRow>();
if (customer is null) return null;
var orders = (await results.ReadAsync<OrderDto>()).AsList();
Note new { Id = id }. Dapper always takes parameters as an object, and they’re sent as real SQL parameters, so there’s no string concatenation and no SQL injection risk.
Storing data, and where transactions live
Creating a customer can include their first orders, and either everything is saved or nothing is. This is where the two libraries differ most in what they do for you.
SaveChanges in a transaction automatically and wires up the foreign keys. With Dapper you own every step, which is more code but also leaves nothing hidden.The EF Core version is just an object graph and one call:
var customer = new Customer
{
Name = request.Name,
Email = request.Email,
CreatedUtc = now,
Orders = (request.InitialOrders ?? []).Select(t => new Order { Total = t, PlacedUtc = now }).ToList()
};
db.Customers.Add(customer);
await db.SaveChangesAsync(ct); // one transaction, ids and foreign keys filled in
return customer.Id;
The Dapper version spells everything out. OUTPUT INSERTED.Id returns the new key, and passing a list of parameter objects to ExecuteAsync runs the insert once per order, all on the same transaction:
await using var conn = connections.Create();
await conn.OpenAsync(ct);
await using var tx = await conn.BeginTransactionAsync(ct);
var id = await conn.ExecuteScalarAsync<int>(new CommandDefinition(
insertCustomer, new { request.Name, request.Email, CreatedUtc = now }, tx, cancellationToken: ct));
var orders = (request.InitialOrders ?? []).Select(t => new { CustomerId = id, Total = t, PlacedUtc = now }).ToList();
if (orders.Count > 0)
await conn.ExecuteAsync(new CommandDefinition(insertOrder, orders, tx, cancellationToken: ct));
await tx.CommitAsync(ct);
This is personal for me. In an older project of mine, each repository opened its own connection, so a customer, their address and their contact details were saved in three separate transactions. A failure halfway left partial data behind. The fix is exactly what’s shown here: one connection, one transaction, one commit. There’s a test that proves it for both stores.
Writing without loading first
For updates and deletes, older EF Core code would load an entity, change it, and save it: two round trips. Since EF Core 7, ExecuteUpdate and ExecuteDelete send a single statement, which brings EF Core level with the SQL you’d write by hand:
// EF Core
await db.Customers.Where(c => c.Id == id)
.ExecuteUpdateAsync(s => s.SetProperty(c => c.Email, email), ct);
// Dapper
await conn.ExecuteAsync(new CommandDefinition(
"UPDATE Customers SET Email = @Email WHERE Id = @Id;", new { Id = id, Email = email }, cancellationToken: ct));
Dapper can also do clever things in plain SQL that would take two steps in EF Core. Adding an order only if the customer exists is one statement:
INSERT INTO Orders (CustomerId, Total, PlacedUtc)
OUTPUT INSERTED.Id
SELECT @CustomerId, @Total, @PlacedUtc
WHERE EXISTS (SELECT 1 FROM Customers WHERE Id = @CustomerId);
If the customer is missing, nothing is inserted, OUTPUT returns no row, and Dapper hands back null, which the endpoint turns into a 404.
Handling errors the same way
Email addresses are unique. When a duplicate goes in, SQL Server raises error 2601 or 2627, but the two libraries surface it differently: EF Core wraps it in a DbUpdateException, while Dapper lets the SqlException through. Both stores translate it into the same domain exception, so the endpoint can return 409 Conflict without knowing which library is behind it:
catch (DbUpdateException ex) when (SqlErrors.IsUniqueViolation(ex)) // EF Core
catch (Exception ex) when (SqlErrors.IsUniqueViolation(ex)) // Dapper
{
throw new DuplicateEmailException(request.Email, ex);
}
One test suite, run against both
Comparing two implementations means nothing unless they behave the same, so every test is a [Theory] that runs once with "ef" and once with "dapper". The tests use Testcontainers to start a real SQL Server in Docker, so they’re testing actual SQL, constraints and transactions, not an in-memory fake that behaves differently:
[Theory, MemberData(nameof(Stores))] // "ef", "dapper"
public async Task Duplicate_email_throws_and_leaves_nothing_behind(string kind)
{
var store = fixture.CreateStore(kind);
await store.CreateAsync(new CreateCustomerRequest("First", "same@example.com"));
await Assert.ThrowsAsync<DuplicateEmailException>(() =>
store.CreateAsync(new CreateCustomerRequest("Second", "same@example.com", [10m, 20m])));
var only = Assert.Single(await fixture.CreateStore(kind).ListAsync());
Assert.Equal(0, only.OrderCount); // the transaction rolled back: no orphaned orders
}
A final test writes data through one store and reads it through the other, checking the results are identical. That makes 21 tests in all (ten behaviours, each run against both stores, plus the cross-check), and GitHub Actions runs them on every push.
So which should you use?
Having both side by side, the answer is “it depends”, but I can be specific about what it depends on:
| EF Core | Dapper | |
|---|---|---|
| You write | C# and LINQ | SQL |
| Schema and migrations | Built in | Bring your own (SQL project, DbUp, etc.) |
| Transactions | Automatic per SaveChanges |
Explicit |
| Related data | Navigations and projections | Multiple result sets or multi-mapping |
| Generated SQL | Usually good; check it in logs | Exactly what you wrote |
| Overhead | Higher, but small for most apps | Close to raw ADO.NET |
| Best for | Domain-heavy CRUD, rapid development | Reporting, complex or tuned queries, existing stored procedures |
They also mix well. A common pattern is EF Core for the bulk of an application’s reads and writes, with Dapper for the handful of reporting queries where you want full control of the SQL, sharing the same connection string and database. The aggregate “top customers” report in this repo is a good example: it’s tidy in both, but the Dapper version reads like the SQL you’d hand to a DBA.
Whichever you choose, two habits pay off: turn on EF Core’s SQL logging (Microsoft.EntityFrameworkCore.Database.Command) so you always know what it’s sending, and test against a real database engine.
The full code, with setup instructions and an .http file of requests to try against both stores, is on GitHub.