Speed up EF Core reads with projections, no-tracking and pooling

Approach

Project to DTOs with Select, use AsNoTracking() for read-only queries, split very wide includes with AsSplitQuery(), and use AddDbContextPool to reuse context instances.

Solution

using Microsoft.EntityFrameworkCore;

var builder = WebApplication.CreateBuilder(args);
builder.Services.AddDbContextPool<ShopDb>(o => o.UseSqlite("Data Source=shop.db"));   // UseNpgsql(...) for PostgreSQL
var app = builder.Build();

app.MapGet("/api/orders", async (ShopDb db, int page = 1) =>
    await db.Orders
        .AsNoTracking()
        .Where(o => o.Status == "paid")
        .OrderByDescending(o => o.CreatedAt).ThenByDescending(o => o.Id)
        .Skip((page - 1) * 50).Take(50)
        .Select(o => new OrderSummary(o.Id, o.Customer.Name, o.Lines.Count, o.Lines.Sum(l => l.Quantity * l.UnitPrice)))
        .ToListAsync());
app.Run();

public record OrderSummary(int Id, string Customer, int Lines, decimal Total);

public class ShopDb(DbContextOptions<ShopDb> options) : DbContext(options)
{
    public DbSet<Order> Orders => Set<Order>();
    protected override void OnModelCreating(ModelBuilder model) =>
        model.Entity<Order>().HasIndex(o => new { o.Status, o.CreatedAt });
}

public class Customer { public int Id { get; set; } public string Name { get; set; } = ""; }
public class Order
{
    public int Id { get; set; }
    public string Status { get; set; } = "pending";
    public DateTime CreatedAt { get; set; }
    public Customer Customer { get; set; } = null!;
    public List<OrderLine> Lines { get; set; } = [];
}
public class OrderLine { public int Id { get; set; } public int Quantity { get; set; } public decimal UnitPrice { get; set; } }

Pitfalls

• Returning entities from APIs (over-fetching, serialization cycles, accidental lazy loading).
• ToList() before filtering, which pulls the table into memory.
• Stateful DbContext customisations with pooling; pooled contexts are reset and reused.

Variations

• Keyset pagination (WHERE (CreatedAt, Id) < (@lastCreated, @lastId)) scales better than Skip.
• Compiled queries (EF.CompileAsyncQuery) shave overhead from very hot queries.

Further reading: Efficient querying