DotNetEfGuard 1.0.0

DotNetEfGuard

EF Core performance helpers to prevent N+1 queries, optimize pagination, handle tracking efficiently, and support soft deletes.

Problems Solved

• N+1 query problems: Loading related entities causes hundreds of queries
• Overusing Include(): Eager loading entire object graphs unnecessarily
• Slow pagination: Pagination queries are slow on large datasets
• EF tracking memory leaks: Change tracking consumes excessive memory
• Lazy loading surprises: Lazy loading causes unexpected queries
• Migrations breaking production: Database migrations fail in production
• No soft-delete standard: Hard deletes lose data, no standard soft-delete pattern
• Audit fields duplicated: CreatedAt/UpdatedAt fields repeated in every entity
• Transaction handling confusion: Unclear when to use transactions
• Connection pool exhaustion: Too many database connections open
• Deadlocks in SQL Server: Concurrent operations cause deadlocks
• Query translation failures: LINQ queries that don't translate to SQL
• In-memory vs real DB tests: Tests pass with in-memory but fail with real database
• Multiple DbContexts misuse: Using multiple contexts incorrectly
• Slow bulk inserts: Inserting thousands of records is extremely slow

Installation

dotnet add package DotNetEfGuard

Quick Start

1. Efficient Pagination

Problem: Pagination queries are slow and load entire result sets into memory.

using DotNetEfGuard;

// ❌ BAD: Loads all records, then pages in memory
var allUsers = await dbContext.Users.ToListAsync();
var paged = allUsers.Skip((page - 1) * pageSize).Take(pageSize);

// ✅ GOOD: Efficient database-level pagination
var result = await dbContext.Users
    .Where(u => u.IsActive)
    .ToPagedResultAsync(pageNumber: 1, pageSize: 20);

// Result includes:
// - result.Items (IEnumerable<User>)
// - result.PageNumber
// - result.PageSize
// - result.TotalCount
// - result.TotalPages
// - result.HasNextPage
// - result.HasPreviousPage

2. Cursor-Based Pagination

Problem: Offset pagination becomes slow on large datasets.

// ✅ GOOD: Cursor-based pagination for large datasets
var result = await dbContext.Users
    .OrderBy(u => u.Id)
    .ToCursorPagedResultAsync(
        pageSize: 20,
        cursor: request.Cursor, // null for first page
        cursorSelector: u => u.Id.ToString()
    );

// Use result.NextCursor for next page

3. Prevent N+1 Queries

Problem: Loading orders with items causes N+1 queries.

// ❌ BAD: N+1 queries
var orders = await dbContext.Orders.ToListAsync();
foreach (var order in orders)
{
    var items = await dbContext.OrderItems
        .Where(i => i.OrderId == order.Id)
        .ToListAsync(); // N queries!
}

// ✅ GOOD: Use AsNoTracking for read-only queries
var orders = await dbContext.Orders
    .AsNoTrackingQuery()
    .Include(o => o.Items)
    .ToListAsync(); // Single query with JOIN

4. Memory Leak Prevention

Problem: Change tracking consumes excessive memory for read-only queries.

// ❌ BAD: Tracks all entities in memory
var users = await dbContext.Users.ToListAsync();

// ✅ GOOD: No tracking for read-only queries
var users = await dbContext.Users
    .AsNoTrackingQuery()
    .ToListAsync();

// ✅ GOOD: Detach all tracked entities when done
dbContext.DetachAll();

5. Soft Delete Support

Problem: Hard deletes lose data, no standard pattern for soft deletes.

// ✅ GOOD: Implement ISoftDeletable
public class User : ISoftDeletable, IAuditable
{
    public int Id { get; set; }
    public string Name { get; set; } = string.Empty;
    
    // Soft delete
    public bool IsDeleted { get; set; }
    public DateTime? DeletedAt { get; set; }
    
    // Audit fields
    public DateTime CreatedAt { get; set; }
    public DateTime? UpdatedAt { get; set; }
    public string? CreatedBy { get; set; }
    public string? UpdatedBy { get; set; }
}

// Query non-deleted entities
var activeUsers = await dbContext.Users
    .WhereNotDeleted()
    .ToListAsync();

// Soft delete
dbContext.SoftDelete(user);
await dbContext.SaveChangesAsync();

6. Efficient Bulk Operations

Problem: Inserting thousands of records is extremely slow.

// ❌ BAD: Inserts one at a time
foreach (var item in items)
{
    dbContext.Items.Add(item);
    await dbContext.SaveChangesAsync(); // N database round trips!
}

// ✅ GOOD: Batch inserts
await dbContext.BulkInsertAsync(items, batchSize: 1000);

// ✅ GOOD: Batch updates
await dbContext.BulkUpdateAsync(items, batchSize: 1000);

// ✅ GOOD: Batch deletes
await dbContext.BulkDeleteAsync(items, batchSize: 1000);

Real-World Example

public class OrderService
{
    private readonly AppDbContext _dbContext;
    
    public async Task<PagedResult<Order>> GetOrdersAsync(int page, int pageSize)
    {
        // Efficient pagination with filtering
        return await _dbContext.Orders
            .WhereNotDeleted() // Soft delete filter
            .AsNoTrackingQuery() // No tracking for read-only
            .Include(o => o.Customer)
            .OrderByDescending(o => o.CreatedAt)
            .ToPagedResultAsync(page, pageSize);
    }
    
    public async Task<Order> GetOrderWithItemsAsync(int orderId)
    {
        // Prevent N+1 with proper Include
        return await _dbContext.Orders
            .AsNoTrackingQuery()
            .Include(o => o.Items)
            .ThenInclude(i => i.Product)
            .FirstOrDefaultAsync(o => o.Id == orderId);
    }
    
    public async Task ImportOrdersAsync(IEnumerable<Order> orders)
    {
        // Efficient bulk insert
        await _dbContext.BulkInsertAsync(orders, batchSize: 1000);
    }
    
    public async Task DeleteOrderAsync(int orderId)
    {
        var order = await _dbContext.Orders.FindAsync(orderId);
        if (order != null)
        {
            // Soft delete instead of hard delete
            _dbContext.SoftDelete(order);
            await _dbContext.SaveChangesAsync();
        }
    }
}

// Entity with soft delete and audit
public class Order : ISoftDeletable, IAuditable
{
    public int Id { get; set; }
    public int CustomerId { get; set; }
    public Customer Customer { get; set; } = null!;
    public List<OrderItem> Items { get; set; } = new();
    
    // Soft delete
    public bool IsDeleted { get; set; }
    public DateTime? DeletedAt { get; set; }
    
    // Audit
    public DateTime CreatedAt { get; set; }
    public DateTime? UpdatedAt { get; set; }
    public string? CreatedBy { get; set; }
    public string? UpdatedBy { get; set; }
}

Pagination Example

[HttpGet]
public async Task<ActionResult<PagedResult<Order>>> GetOrders(
    int page = 1,
    int pageSize = 20)
{
    var result = await _orderService.GetOrdersAsync(page, pageSize);
    
    return Ok(new
    {
        data = result.Items,
        pagination = new
        {
            page = result.PageNumber,
            pageSize = result.PageSize,
            totalCount = result.TotalCount,
            totalPages = result.TotalPages,
            hasNextPage = result.HasNextPage,
            hasPreviousPage = result.HasPreviousPage
        }
    });
}

Best Practices

1. Use AsNoTrackingQuery() for all read-only queries
2. Use ToPagedResultAsync() for efficient pagination
3. Implement ISoftDeletable instead of hard deletes
4. Use BulkInsertAsync() for inserting many records
5. Use WhereNotDeleted() to filter soft-deleted entities
6. Detach entities with DetachAll() when done with tracking
7. Use cursor pagination for large datasets
8. Include related entities explicitly to prevent N+1 queries

API Reference

• PaginationHelper.ToPagedResultAsync() - Efficient offset-based pagination
• PaginationHelper.ToCursorPagedResultAsync() - Cursor-based pagination
• QueryExtensions.AsNoTrackingQuery() - Read-only queries without tracking
• QueryExtensions.DetachAll() - Detach all tracked entities
• BulkOperations.BulkInsertAsync() - Efficient bulk inserts
• BulkOperations.BulkUpdateAsync() - Efficient bulk updates
• BulkOperations.BulkDeleteAsync() - Efficient bulk deletes
• SoftDeleteExtensions.WhereNotDeleted() - Filter soft-deleted entities
• SoftDeleteExtensions.SoftDelete() - Soft delete an entity
• ISoftDeletable - Interface for soft delete support
• IAuditable - Interface for audit fields