From dotnet-skills
Database access patterns for performance. Separate read/write models, avoid N+1 queries, use AsNoTracking, apply row limits, and never do application-side joins. Works with EF Core and Dapper. Use when designing data access layers, optimizing slow database queries, choosing between EF Core and Dapper, or avoiding common performance pitfalls.
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.
Use this skill when:
Read and write models are fundamentally different - they have different shapes, columns, and purposes. Don't create a single "User" entity and reuse it everywhere.
src/
MyApp.Data/
Users/
# Read side - multiple optimized projections
IUserReadStore.cs
PostgresUserReadStore.cs
# Write side - command handlers
IUserWriteStore.cs
PostgresUserWriteStore.cs
# Read DTOs - lightweight, denormalized
UserProfile.cs
UserSummary.cs
# Write commands - validation-focused
CreateUserCommand.cs
UpdateUserCommand.cs
Orders/
IOrderReadStore.cs
IOrderWriteStore.cs
(similar structure...)
// Read models: Multiple specialized projections optimized for different use cases
public interface IUserReadStore
{
// Returns detailed profile for single-user view
Task<UserProfile?> GetByIdAsync(UserId id, CancellationToken ct = default);
// Returns lightweight info for lookups
Task<UserProfile?> GetByEmailAsync(EmailAddress email, CancellationToken ct = default);
// Returns paginated summaries - only what the list view needs
Task<IReadOnlyList<UserSummary>> GetAllAsync(int limit, UserId? cursor = null, CancellationToken ct = default);
// Boolean query - no entity needed
Task<bool> EmailExistsAsync(EmailAddress email, CancellationToken ct = default);
}
// Write model: Accepts strongly-typed commands, minimal return values
public interface IUserWriteStore
{
// Returns only the created ID - caller doesn't need the full entity
Task<UserId> CreateAsync(CreateUserCommand command, CancellationToken ct = default);
// Update validates command, returns void (success or throws)
Task UpdateAsync(UserId id, UpdateUserCommand command, CancellationToken ct = default);
// Delete is simple and explicit
Task DeleteAsync(UserId id, CancellationToken ct = default);
}
Key structural differences illustrated:
Never return unbounded result sets. Every read method should have a configurable limit.
public interface IOrderReadStore
{
// Limit is required, not optional
Task<IReadOnlyList<OrderSummary>> GetByCustomerAsync(
CustomerId customerId,
int limit,
OrderId? cursor = null,
CancellationToken ct = default);
}
// Implementation
public async Task<IReadOnlyList<OrderSummary>> GetByCustomerAsync(
CustomerId customerId,
int limit,
OrderId? cursor = null,
CancellationToken ct = default)
{
await using var connection = await _dataSource.OpenConnectionAsync(ct);
const string sql = """
SELECT id, customer_id, total, status, created_at
FROM orders
WHERE customer_id = @CustomerId
AND (@Cursor IS NULL OR created_at < (SELECT created_at FROM orders WHERE id = @Cursor))
ORDER BY created_at DESC
LIMIT @Limit
""";
var rows = await connection.QueryAsync<OrderRow>(sql, new
{
CustomerId = customerId.Value,
Cursor = cursor?.Value,
Limit = limit
});
return rows.Select(r => r.ToOrderSummary()).ToList();
}
public async Task<PaginatedList<OrderSummary>> GetOrdersAsync(
CustomerId customerId,
Paginator paginator,
CancellationToken ct = default)
{
var query = _context.Orders
.AsNoTracking()
.Where(o => o.CustomerId == customerId.Value)
.OrderByDescending(o => o.CreatedAt);
var totalCount = await query.CountAsync(ct);
var orders = await query
.Skip((paginator.PageNumber - 1) * paginator.PageSize)
.Take(paginator.PageSize) // Always limit!
.Select(o => new OrderSummary(
new OrderId(o.Id),
o.Total,
o.Status,
o.CreatedAt))
.ToListAsync(ct);
return new PaginatedList<OrderSummary>(
orders,
totalCount,
paginator.PageSize,
paginator.PageNumber);
}
EF Core's change tracking is expensive. Disable it for read-only queries.
// DO: Disable tracking for reads
var users = await _context.Users
.AsNoTracking()
.Where(u => u.IsActive)
.ToListAsync();
// DON'T: Track entities you won't modify
var users = await _context.Users
.Where(u => u.IsActive)
.ToListAsync(); // Change tracking enabled - wasteful
// For read-heavy applications, consider this in DbContext
protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
{
optionsBuilder.UseQueryTrackingBehavior(QueryTrackingBehavior.NoTracking);
}
Then explicitly enable tracking when needed:
var user = await _context.Users
.AsTracking() // Explicit - we intend to modify
.FirstOrDefaultAsync(u => u.Id == userId);
The N+1 problem: fetching a list, then querying for each item's related data.
// BAD: N+1 queries
var orders = await _context.Orders.ToListAsync();
foreach (var order in orders)
{
// Each iteration hits the database!
var items = await _context.OrderItems
.Where(i => i.OrderId == order.Id)
.ToListAsync();
}
// GOOD: Single query with join
var orders = await _context.Orders
.AsNoTracking()
.Include(o => o.Items)
.ToListAsync();
// GOOD: Two queries, no N+1
const string sql = """
SELECT id, customer_id, total FROM orders WHERE customer_id = @CustomerId;
SELECT oi.* FROM order_items oi
INNER JOIN orders o ON oi.order_id = o.id
WHERE o.customer_id = @CustomerId;
""";
using var multi = await connection.QueryMultipleAsync(sql, new { CustomerId = customerId });
var orders = (await multi.ReadAsync<OrderRow>()).ToList();
var items = (await multi.ReadAsync<OrderItemRow>()).ToList();
// Join in memory (acceptable - data already fetched)
foreach (var order in orders)
{
order.Items = items.Where(i => i.OrderId == order.Id).ToList();
}
Joins must happen in SQL, not in C#.
// BAD: Application join - two queries, memory waste
var customers = await _context.Customers.ToListAsync();
var orders = await _context.Orders.ToListAsync();
var result = customers.Select(c => new
{
Customer = c,
Orders = orders.Where(o => o.CustomerId == c.Id).ToList() // O(n*m) in memory!
});
// GOOD: SQL join - single query
var result = await _context.Customers
.AsNoTracking()
.Include(c => c.Orders)
.ToListAsync();
// GOOD: Explicit join (Dapper)
const string sql = """
SELECT c.id, c.name, COUNT(o.id) as order_count
FROM customers c
LEFT JOIN orders o ON c.id = o.customer_id
GROUP BY c.id, c.name
""";
Multiple Include calls can cause Cartesian products.
// DANGEROUS: Can explode into millions of rows
var product = await _context.Products
.Include(p => p.Reviews) // 100 reviews
.Include(p => p.Images) // 20 images
.Include(p => p.Categories) // 5 categories
.FirstOrDefaultAsync(p => p.Id == id);
// Result: 100 * 20 * 5 = 10,000 rows transferred!
// GOOD: Multiple queries, no Cartesian explosion
var product = await _context.Products
.AsSplitQuery()
.Include(p => p.Reviews)
.Include(p => p.Images)
.Include(p => p.Categories)
.FirstOrDefaultAsync(p => p.Id == id);
// Result: 4 separate queries, ~125 rows total
// BEST: Only fetch what you need
var product = await _context.Products
.AsNoTracking()
.Where(p => p.Id == id)
.Select(p => new ProductDetail(
p.Id,
p.Name,
p.Description,
p.Reviews.OrderByDescending(r => r.CreatedAt).Take(10).ToList(),
p.Images.Take(5).ToList(),
p.Categories.Select(c => c.Name).ToList()))
.FirstOrDefaultAsync();
Define maximum lengths in your EF Core model to prevent oversized data.
public class UserConfiguration : IEntityTypeConfiguration<User>
{
public void Configure(EntityTypeBuilder<User> builder)
{
builder.Property(u => u.Email)
.HasMaxLength(254) // RFC 5321 limit
.IsRequired();
builder.Property(u => u.Name)
.HasMaxLength(100)
.IsRequired();
builder.Property(u => u.Bio)
.HasMaxLength(500);
// For truly large content, use text type explicitly
builder.Property(u => u.Notes)
.HasColumnType("text");
}
}
Generic repositories hide query complexity and make optimization difficult.
// BAD: Generic repository
public interface IRepository<T>
{
Task<T?> GetByIdAsync(int id);
Task<IEnumerable<T>> GetAllAsync(); // No limit!
Task<IEnumerable<T>> FindAsync(Expression<Func<T, bool>> predicate); // Can't optimize
}
// GOOD: Purpose-built read stores
public interface IOrderReadStore
{
Task<OrderDetail?> GetByIdAsync(OrderId id, CancellationToken ct = default);
Task<IReadOnlyList<OrderSummary>> GetByCustomerAsync(CustomerId id, int limit, CancellationToken ct = default);
Task<IReadOnlyList<OrderSummary>> GetPendingAsync(int limit, CancellationToken ct = default);
}
Problems with generic repositories:
For complex read queries, Dapper with explicit SQL is often cleaner and faster.
public sealed class PostgresUserReadStore : IUserReadStore
{
private readonly NpgsqlDataSource _dataSource;
public PostgresUserReadStore(NpgsqlDataSource dataSource)
{
_dataSource = dataSource;
}
public async Task<UserProfile?> GetByIdAsync(UserId id, CancellationToken ct = default)
{
await using var connection = await _dataSource.OpenConnectionAsync(ct);
const string sql = """
SELECT id, email, name, bio, created_at
FROM users
WHERE id = @Id
""";
var row = await connection.QuerySingleOrDefaultAsync<UserRow>(
sql, new { Id = id.Value });
return row?.ToUserProfile();
}
// Internal row type for Dapper mapping
private sealed class UserRow
{
public Guid id { get; set; }
public string email { get; set; } = null!;
public string name { get; set; } = null!;
public string? bio { get; set; }
public DateTime created_at { get; set; }
public UserProfile ToUserProfile() => new(
Id: new UserId(id),
Email: new EmailAddress(email),
Name: new PersonName(name),
Bio: bio,
CreatedAt: new DateTimeOffset(created_at, TimeSpan.Zero));
}
}
| Scenario | Recommendation |
|---|---|
| Simple CRUD | EF Core |
| Complex read queries | Dapper |
| Writes with validation | EF Core |
| Bulk operations | Dapper or raw SQL |
| Reporting/analytics | Dapper |
| Domain-heavy writes | EF Core |
You can use both in the same project - EF Core for writes, Dapper for reads.
| Anti-Pattern | Solution |
|---|---|
| No row limit | Add limit parameter to every read method |
| SELECT * | Project only needed columns |
| N+1 queries | Use Include or batch queries |
| Application joins | Do joins in SQL |
| Cartesian explosion | Use AsSplitQuery or projection |
| Tracking read-only data | Use AsNoTracking |
| Generic repository | Purpose-built read/write stores |
| Unbounded strings | Configure MaxLength in model |