From dotnet-skills
Choosing a data access approach. EF Core vs Dapper vs ADO.NET decision matrix, performance tradeoffs.
npx claudepluginhub wshaddix/dotnet-skillsThis skill uses the workspace's default tool permissions.
Searches, retrieves, and installs Agent Skills from prompts.chat registry using MCP tools like search_skills and get_skill. Activates for finding skills, browsing catalogs, or extending Claude.
Searches prompts.chat for AI prompt templates by keyword or category, retrieves by ID with variable handling, and improves prompts via AI. Use for discovering or enhancing prompts.
Compares coding agents like Claude Code and Aider on custom YAML-defined codebase tasks using git worktrees, measuring pass rate, cost, time, and consistency.
Decision framework for choosing between Entity Framework Core, Dapper, and raw ADO.NET in .NET applications. Covers performance tradeoffs, feature comparisons, AOT/trimming compatibility, hybrid approaches, and migration paths. Use this skill to make an informed technology decision before writing data access code.
Out of scope: Tactical EF Core usage (DbContext lifecycle, migrations, interceptors) is covered in [skill:dotnet-efcore-patterns]. Strategic EF Core architecture (read/write split, aggregate boundaries, repository policy) is covered in [skill:dotnet-efcore-architecture]. DI container mechanics -- see [skill:dotnet-csharp-dependency-injection]. Async patterns -- see [skill:dotnet-csharp-async-patterns]. Testing data access layers -- see [skill:dotnet-integration-testing] for database fixture and Testcontainers patterns. CI/CD pipelines -- see [skill:dotnet-gha-patterns] and [skill:dotnet-ado-patterns].
Cross-references: [skill:dotnet-efcore-patterns] for tactical EF Core usage, [skill:dotnet-efcore-architecture] for strategic EF Core patterns, [skill:dotnet-csharp-dependency-injection] for service registration, [skill:dotnet-csharp-async-patterns] for async query patterns.
| Factor | EF Core | Dapper | Raw ADO.NET |
|---|---|---|---|
| Learning curve | Moderate (LINQ, migrations, config) | Low (SQL + mapping) | Low-moderate (SQL + manual mapping) |
| Productivity | High (change tracking, migrations, scaffolding) | Moderate (write SQL, auto-map) | Low (everything manual) |
| Query performance | Good with projections; overhead from tracking | Near-ADO.NET performance | Fastest possible |
| Startup time | Higher (model building, compilation) | Minimal | Minimal |
| Memory allocation | Higher (change tracker, proxy objects) | Low (direct mapping) | Lowest |
| AOT/trimming | Limited (reflection-heavy, improving) | Good with source generators | Full support |
| Change tracking | Built-in | None | None |
| Migrations | Built-in | None (use FluentMigrator, DbUp, etc.) | None |
| LINQ support | Full (translated to SQL) | None (raw SQL) | None (raw SQL) |
| Batch operations | ExecuteUpdate/ExecuteDelete (EF Core 7+) | Manual batching | Manual batching |
| Complex mappings | Excellent (owned types, TPH/TPT/TPC) | Simple POCO mapping | Manual |
AsNoTracking()// EF Core: expressive, type-safe, with change tracking
var order = await db.Orders
.Include(o => o.Items)
.FirstOrDefaultAsync(o => o.Id == orderId, ct);
order!.Status = OrderStatus.Shipped;
await db.SaveChangesAsync(ct); // Automatic dirty detection
// Dapper: direct SQL, minimal overhead
await using var connection = new NpgsqlConnection(connectionString);
var orders = await connection.QueryAsync<OrderDto>(
"""
SELECT o.id, o.customer_id, o.status, o.created_at,
COUNT(i.id) AS item_count,
SUM(i.quantity * i.unit_price) AS total
FROM orders o
LEFT JOIN order_items i ON i.order_id = o.id
WHERE o.customer_id = @CustomerId
GROUP BY o.id, o.customer_id, o.status, o.created_at
ORDER BY o.created_at DESC
LIMIT @PageSize
""",
new { CustomerId = customerId, PageSize = pageSize });
// Raw ADO.NET: full control, zero abstraction overhead
await using var connection = new NpgsqlConnection(connectionString);
await connection.OpenAsync(ct);
await using var command = connection.CreateCommand();
command.CommandText = "SELECT id, name, price FROM products WHERE category_id = $1";
command.Parameters.AddWithValue(categoryId);
await using var reader = await command.ExecuteReaderAsync(ct);
var products = new List<ProductDto>();
while (await reader.ReadAsync(ct))
{
products.Add(new ProductDto
{
Id = reader.GetInt32(0),
Name = reader.GetString(1),
Price = reader.GetDecimal(2)
});
}
Approximate overhead per query (relative to raw ADO.NET baseline):
| Operation | ADO.NET | Dapper | EF Core (NoTracking) | EF Core (Tracking) |
|---|---|---|---|---|
| Simple SELECT by PK | 1x | ~1.05x | ~1.3x | ~1.5x |
| SELECT 100 rows | 1x | ~1.1x | ~1.4x | ~2x |
| INSERT single row | 1x | ~1.1x | ~1.5x | ~2x |
| Complex JOIN query | 1x | ~1.05x | ~1.3-2x (depends on LINQ translation) | ~1.5-2.5x |
Notes:
Select() projections and AsNoTracking() approaches Dapper performance for most queries.EF Core relies heavily on reflection for model building, change tracking, and query translation. AOT compatibility is improving but not complete:
| Feature | AOT Status (.NET 9+) |
|---|---|
| Model building | Partial -- requires compiled model (dotnet ef dbcontext optimize) |
| Query translation | Not AOT-safe (expression tree compilation) |
| Change tracking | Not AOT-safe (proxy generation, snapshot creation) |
| Migrations | Design-time only -- not needed at runtime |
Compiled models pre-generate the model configuration at build time, reducing startup cost and improving trim-friendliness:
dotnet ef dbcontext optimize \
--project src/MyApp.Infrastructure \
--startup-project src/MyApp.Api \
--output-dir CompiledModels
options.UseNpgsql(connectionString)
.UseModel(AppDbContextModel.Instance); // Pre-compiled model
Bottom line: EF Core Native AOT support is partial and version-dependent. As of .NET 9, compiled models improve startup and trim-friendliness, but query translation and change tracking still rely on runtime code generation. Check the current limitations for your target version before committing to EF Core in an AOT deployment. Use compiled models to improve startup time where possible, but plan for Dapper.AOT or ADO.NET fallbacks on AOT-critical paths.
Dapper traditionally uses runtime reflection and emit for POCO mapping. The Dapper.AOT source generator provides a trim- and AOT-compatible alternative:
| Package | AOT Status |
|---|---|
Dapper (standard) | Not AOT-safe (uses Reflection.Emit) |
Dapper.AOT | AOT-safe (source-generated mappers) |
<PackageReference Include="Dapper" Version="2.*" />
<PackageReference Include="Dapper.AOT" Version="1.*" />
// Dapper.AOT generates the mapping code at compile time
// Usage is the same as standard Dapper -- the source generator intercepts calls
[DapperAot] // Attribute enables AOT generation for this class
public sealed class OrderRepository(NpgsqlDataSource dataSource)
{
public async Task<OrderDto?> GetByIdAsync(int id, CancellationToken ct)
{
await using var connection = await dataSource.OpenConnectionAsync(ct);
return await connection.QuerySingleOrDefaultAsync<OrderDto>(
"SELECT id, customer_id, status FROM orders WHERE id = @Id",
new { Id = id });
}
}
Full AOT and trimming support. No reflection, no code generation -- all mapping is explicit.
| Requirement | Recommendation |
|---|---|
| Must publish AOT today | Dapper.AOT or raw ADO.NET |
| Prefer ORM, AOT not required | EF Core |
| Prefer ORM, AOT planned for future | EF Core now, evaluate AOT support as it improves |
| Building a library consumed by AOT apps | Raw ADO.NET or Dapper.AOT |
Most production applications benefit from using multiple data access technologies. EF Core and Dapper can coexist in the same project, sharing the same database connection.
// Command: use EF Core for change tracking and validation
public sealed class CreateOrderHandler(WriteDbContext db)
{
public async Task<int> HandleAsync(CreateOrderCommand command, CancellationToken ct)
{
var order = new Order(command.CustomerId);
// ... business logic ...
db.Orders.Add(order);
await db.SaveChangesAsync(ct);
return order.Id;
}
}
// Query: use Dapper for complex read-only queries
public sealed class OrderReportHandler(NpgsqlDataSource dataSource)
{
public async Task<IReadOnlyList<OrderReportRow>> HandleAsync(
OrderReportQuery query,
CancellationToken ct)
{
await using var connection = await dataSource.OpenConnectionAsync(ct);
var rows = await connection.QueryAsync<OrderReportRow>(
"""
SELECT
date_trunc('day', o.created_at) AS day,
COUNT(*) AS order_count,
SUM(i.quantity * i.unit_price) AS revenue
FROM orders o
JOIN order_items i ON i.order_id = o.id
WHERE o.created_at >= @StartDate AND o.created_at < @EndDate
GROUP BY date_trunc('day', o.created_at)
ORDER BY day
""",
new { query.StartDate, query.EndDate });
return rows.AsList();
}
}
Use DbContext.Database.GetDbConnection() to get the underlying DbConnection for Dapper queries within an EF Core transaction:
public async Task ProcessWithBothAsync(int orderId, CancellationToken ct)
{
var connection = db.Database.GetDbConnection();
await db.Database.OpenConnectionAsync(ct);
await using var transaction = await db.Database.BeginTransactionAsync(ct);
// EF Core operation
var order = await db.Orders.FindAsync([orderId], ct);
order!.Status = OrderStatus.Processing;
await db.SaveChangesAsync(ct);
// Dapper operation on the same connection and transaction
await connection.ExecuteAsync(
"""
INSERT INTO audit_log (entity_type, entity_id, action, timestamp)
VALUES (@Type, @Id, @Action, @Timestamp)
""",
new { Type = "Order", Id = orderId, Action = "StatusChange",
Timestamp = DateTimeOffset.UtcNow },
transaction: transaction.GetDbTransaction());
await transaction.CommitAsync(ct);
}
When using Dapper with PostgreSQL, register NpgsqlDataSource as a singleton in DI (it manages connection pooling internally):
builder.Services.AddNpgsqlDataSource(
builder.Configuration.GetConnectionString("DefaultConnection")!);
The Npgsql.DependencyInjection package provides AddNpgsqlDataSource(). This also integrates with EF Core -- UseNpgsql() can accept the registered data source:
builder.Services.AddDbContext<AppDbContext>((sp, options) =>
options.UseNpgsql(sp.GetRequiredService<NpgsqlDataSource>()));
Dapper wraps IDbConnection extension methods around existing ADO.NET code. Migration is incremental:
DbDataReader loops with QueryAsync<T>() calls.command.Parameters.AddWithValue() with anonymous objects.Dapper NuGet package.DbContext with entity configurations.dotnet ef dbcontext scaffold.| Package | Purpose | NuGet |
|---|---|---|
Microsoft.EntityFrameworkCore | Core EF framework | Microsoft.EntityFrameworkCore |
Microsoft.EntityFrameworkCore.Design | CLI tooling (migrations, scaffolding) | Design-time only |
Npgsql.EntityFrameworkCore.PostgreSQL | PostgreSQL EF Core provider | Npgsql.EntityFrameworkCore.PostgreSQL |
Microsoft.EntityFrameworkCore.SqlServer | SQL Server EF Core provider | Microsoft.EntityFrameworkCore.SqlServer |
Microsoft.EntityFrameworkCore.Sqlite | SQLite EF Core provider | Microsoft.EntityFrameworkCore.Sqlite |
Dapper | Micro-ORM | Dapper |
Dapper.AOT | AOT-compatible source generator for Dapper | Dapper.AOT |
Npgsql.DependencyInjection | NpgsqlDataSource DI registration | Npgsql.DependencyInjection |
Npgsql | PostgreSQL ADO.NET provider | Npgsql |
Microsoft.Data.SqlClient | SQL Server ADO.NET provider | Microsoft.Data.SqlClient |
FluentMigrator | Code-based migrations (non-EF) | FluentMigrator |
DbUp | SQL script-based migrations (non-EF) | dbup |
AsNoTracking() and Select() projections, and measure before introducing Dapper.AsNoTracking() and Select() projections meets the performance requirement. The difference is often negligible when EF Core is used correctly.Dapper in AOT-published applications -- it uses Reflection.Emit which is not AOT-compatible. Use Dapper.AOT with the [DapperAot] attribute for AOT scenarios.Npgsql.EntityFrameworkCore.PostgreSQL) and Dapper packages must be explicitly referenced. Agents that generate code without package references produce non-compiling projects.NpgsqlConnection instances manually in DI-registered services -- use NpgsqlDataSource (registered via AddNpgsqlDataSource()) which manages connection pooling. Creating connections manually bypasses pool management.DbContext.Database.GetDbConnection() to share the connection and transaction.GetDbTransaction() to share the transaction.ToQueryString() during development.