ASP.NET Core

I once inherited a dashboard page that took over five seconds to load. The API endpoint behind it looked innocent enough, but digging in, I found a classic case of death by a thousand cuts: lazy loading, bloated entities, and chatty database calls. It was a textbook example of default EF Core behavior backfiring under real-world load.

After fixing that mess (and many others like it), I’ve developed a small playbook of go-to optimizations. These aren’t wild, complex tricks. They’re five fundamental patterns that I apply to almost every high-traffic ASP.NET Core project.

Here’s what I do to keep my data layers fast and lean.

1. Ditch Full Entities, Project to DTOs

This one is my golden rule for read queries. If you’re just displaying data, stop loading full-blown EF Core entities.

The problem is that when you pull an entity, EF Core has to hydrate every single property. Even the ones you don’t need. This results in bigger SQL queries that join more tables and pull back way more data than your API client will ever see.

It’s pure waste.

Instead, use Select to project directly into a Data Transfer Object (DTO).

// The slow way
var users = await _context.Users
    .Include(u => u.Profile) // Pulls everything from two tables
    .ToListAsync();

// The fast, clean way
var users = await _context.Users
    .Select(u => new UserSummaryDto 
    {
        Id = u.Id,
        Name = u.Name,
        Email = u.Email
    })
    .ToListAsync();

On a …

Last month, our customer portal started timing out. Users browsing past page 1,000 of their order history were met with a dreaded loading spinner that never went away. A quick look at the logs confirmed it: our classic offset-based pagination was hitting a table with 2.3 million orders, and query times had jumped from 50ms to over 4 seconds. SQL Server was burning up CPU cycles.

The culprit was Skip() and Take(). This one has burned me in production more than once. The fix was switching to keyset pagination (sometimes called cursor-based pagination), a technique that uses the last seen value to find the next page instead of counting rows to skip.

Here’s how it works and how to implement it properly in EF Core.

Why Skip() and Take() Grind to a Halt

Standard pagination in LINQ uses Skip(page * pageSize).Take(pageSize). Under the hood, this translates to SQL’s OFFSET and FETCH NEXT. For SQL Server to skip 50,000 rows, it has to actually read all 50,000 rows first.

Here’s the generated SQL for fetching page 2,501 (with a page size of 20):

-- This gets painfully slow as the offset increases
SELECT * FROM Orders 
ORDER BY OrderId 
OFFSET 50000 ROWS FETCH NEXT 20 ROWS ONLY;

Even with a perfect index on OrderId, the database still has to scan 50,020 rows from the index just to return the final 20. It’s an enormous amount of wasted work that gets worse with every page you click.

The Fix: Keyset Pagination (The “Seek Method”)

Instead of telling the …

I remember the first time AutoInclude() burned me in production. We had an Order entity, and someone thought it’d be convenient to always load the Customer and OrderItems. Local tests were snappy. But once we deployed, a simple dashboard API that just needed to show order statuses started timing out. The database CPU was pegged at 100%.

The culprit? A seemingly harmless configuration was forcing massive, unnecessary joins on every single query.

Here’s why AutoInclude() is a performance trap and what you should do instead.

What’s actually happening under the hood?

When you use AutoInclude() in your OnModelCreating configuration, you’re telling EF Core to always run a JOIN for that relationship. Every time. No exceptions.

// In your DbContext's OnModelCreating method
protected override void OnModelCreating(ModelBuilder modelBuilder)
{
    modelBuilder.Entity<Order>()
        .Navigation(o => o.Customer)
        .AutoInclude();
        
    modelBuilder.Entity<Order>()
        .Navigation(o => o.OrderItems)
        .AutoInclude();
}

With this in place, any query for an Order will automatically fetch the Customer and the list of OrderItems.

The Production Nightmare: Silent and Deadly Joins

The real problem with AutoInclude() is that it hides the database cost. The performance penalty isn’t visible in your application code; it’s buried deep in the model configuration.

When your code looks this simple:

var orders = await _context. …

I’ve been there. You join a project, and the first big argument is about the database. One camp wants to stick with the existing, massive SQL Server database and generate code from it (Database-First). The other wants to write C# classes and have Entity Framework Core create the database for them (Code-First).

Picking the wrong one isn’t just a technical mistake; it’s a commitment that can cost your team months of painful refactoring. This happened to me on an e-commerce platform where a premature switch to Code-First crippled our ability to integrate with the company’s legacy inventory system.

Let’s cut through the theory and talk about which approach to choose and when, based on real-world projects.

What’s the Real Difference?

It’s all about the source of truth.

• Database-First: The database schema is the source of truth. You start with an existing database and use a tool to generate your EF Core models (DbContext and entity classes). You never touch the generated model files directly.
• Code-First: Your C# classes are the source of truth. You write your domain models, and EF Core migrations generate and update the database schema to match your code.

Here’s how they stack up in practice.

We’ve all been there. Your new API endpoint flies on your machine, returning customer orders in 50ms. You deploy it, and suddenly… it’s taking two seconds. The logs don’t show any obvious errors, but your app is crawling. The culprit is often a feature designed for convenience: lazy loading.

Lazy loading in Entity Framework Core feels like magic during development, but it can hide a massive performance tax that will absolutely cripple your application at scale.

So What Exactly is Lazy Loading?

Lazy loading means EF Core automatically grabs related data from the database only when you access it. Instead of you explicitly telling EF what you need upfront, EF Core reacts to your code. When you touch a navigation property for the first time, boom, it fires off a new database query to get that data.

Sounds handy, right? The problem is that each of these property accesses can trigger a separate, hidden database round-trip. What looks like a simple line of C# can become a silent storm of SQL queries.

The Sneaky Performance Killer: The N+1 Query Problem

The most common side effect of lazy loading is the infamous N+1 query problem. It starts with one simple query and then spirals.

Take a look at this standard ASP.NET Core controller action that fetches 50 orders:

public async Task<IActionResult> GetOrderSummary()
{
    var orders = await _context.Orders.Take(50).ToListAsync();
    
    // The problem kicks in right here, usually during serialization
    var …

I once spent the better part of a week chasing a bug that only appeared in our staging environment under heavy load. A user from one organization would suddenly see data belonging to another. We’d check the logs, query the database directly, and everything looked fine. It was a ghost.

The culprit? A single line change someone made to “optimize” our database access: switching AddDbContext to AddDbContextPool. That optimization introduced a state-sharing bug that was nearly impossible to reproduce locally.

DbContext pooling in EF Core is one of those features that promises a big performance win, but it comes with a massive, sneaky gotcha. Let’s break down what it is, where it shines, and how to use it without shooting yourself in the foot.

So, what’s DbContext pooling anyway?

Normally, when you register your DbContext in an ASP.NET Core app, you use a scoped lifetime. This is the default and the safest option.

// The standard, safe way: A new instance for every HTTP request.
services.AddDbContext<AppDbContext>(options => 
    options.UseSqlServer(connectionString));

This means for every single web request, the dependency injection container creates a brand-new DbContext instance. When the request is over, that instance is thrown away. Simple, clean, and isolated.

DbContext pooling changes the game. Instead of creating a new instance every time, it keeps a “pool” of DbContext instances ready to go.

// The high-performance way: …