Performance

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 …

I’ve seen this happen a dozen times. An API endpoint is dog-slow, taking 2-3 seconds to respond. Everyone blames the network or the database. We check the query execution plan, and it’s fine—the database returns data in 50 milliseconds. So what’s the problem?

The problem is the 900KB of data we’re pulling from the database, serializing, and then throwing away for every single request. The culprit is a silent performance killer in Entity Framework Core: fetching full entities when you only need a few properties.

The Default Behavior is a Trap

It’s easy to write code like this. It’s clean, it’s simple, and it works.

var orders = await _dbContext.Orders
    .Where(o => o.Status == "Active")
    .ToListAsync();

But under the hood, EF Core is trying to be helpful by generating a SELECT * style query. If your Orders table has a few large NVARCHAR(MAX) columns for notes, addresses, or serialized metadata, you’re in for a bad time.

Here’s the SQL it probably generates:

SELECT [o].[Id], [o].[CustomerId], [o].[OrderDate], [o].[Status], 
       [o].[Notes], [o].[ShippingAddress], [o].[BillingAddress],
       [o].[InternalComments], [o].[AuditData], [o].[SerializedMetadata]
FROM [Orders] AS [o]
WHERE [o].[Status] = 'Active'

If your API only needs to display the order ID, customer name, and total, you just paid a heavy price in network I/O and memory allocation for nothing. That SerializedMetadata column alone could be megabytes.

The Fix: Project Only What You Need

The …

The API was screaming. A single endpoint, perfectly fine on my machine, was bringing our database to its knees in production. After digging through the logs, I found the culprit: one request was generating 241 separate SQL queries.

This one burned me badly early in my career. It’s a classic trap that every developer using an ORM like Entity Framework Core will eventually fall into: the N+1 query problem. It’s silent in development but absolutely lethal at scale.

So, What’s an N+1 Query Anyway?

It’s a sneaky performance bug. You ask EF Core for a list of items, and it happily obliges with one query. That’s the “1”.

Then, you loop through that list, and for each item, you access a related property (like a post’s author). If you haven’t told EF Core to load that related data upfront, it goes back to the database for every single item. That’s the “N”.

So, to get 100 blog posts and their authors, you end up with:

• 1 query to get the 100 posts.
• 100 more queries to get each post’s author.
• Total: 101 queries for what should have been a simple operation.

In that production disaster I mentioned, fixing the N+1 pattern dropped the query count from 241 to just 3. The response time fell by over 85%.

How to Spot an N+1 Ambush

You can’t fix what you can’t see. Here are my go-to methods for hunting down these hidden performance killers.

1. Just Watch the Logs

This is the most direct way to see the problem. Turn on …

I once spent half a day chasing a performance bug in an API endpoint. It was a simple read operation, but it was inexplicably slow and chewing up memory under load. The culprit? A single, innocent-looking Entity Framework Core default that was tracking thousands of objects for changes that would never happen.

EF Core is fantastic, but its defaults are designed for getting-started tutorials, not production applications. They prioritize ease of use over performance and safety. Here are the five settings I change in every single production project before writing a single line of business logic.

1. The Silent Killer: QueryTrackingBehavior.TrackAll

By default, every time you query for data, EF Core’s DbContext assumes you’re going to change it. It loads the entities and sets up change tracking, which consumes memory and CPU cycles to keep an eye on every property.

This is a complete waste for the 90% of queries that are read-only: fetching data for an API response, a report, or a UI display. I’ve seen this add 20-40% overhead on high-traffic endpoints. It’s the number one performance gotcha I find in code reviews.

The Fix: Turn it off globally.

// In your Program.cs or Startup.cs
services.AddDbContext<AppDbContext>(options =>
    options.UseSqlServer(connectionString)
           .UseQueryTrackingBehavior(QueryTrackingBehavior.NoTracking));

This simple change makes all queries non-tracking by default. For the rare cases where you do need to fetch and then …

Of course. Here is the rewritten blog post, following your guidelines.

We’ve all been there. The EF Core query is blazing fast on your machine with 1,000 test records. Then it hits production with two million rows, and that same query goes from 50ms to 3 seconds. Suddenly, alerts are firing and your app is crawling.

The culprit is almost always the same: missing database indexes. This performance cliff between dev and prod is a classic “gotcha,” and it’s burned me more than once. Here’s how to spot it and fix it for good.

Why Your Query Is Fast Locally but Slow in Prod

When you write a clean piece of LINQ, EF Core does its job and generates the SQL you’d expect.

var user = await context.Users
    .Where(u => u.Email == "[email protected]")
    .FirstOrDefaultAsync();

Looks harmless, right? But without an index on the Email column, you’re asking SQL Server to do a full table scan. It literally has to check every single row to find a match. On a small local database, that’s instant. On a production table with millions of records, it’s a disaster.

Let’s be clear: EF Core isn’t slow. Your database is just doing what you told it to do. Our job is to give it the tools (indexes) to work smarter, not harder.

A Quick Sanity Check for Indexes

Not sure if a column needs an index? Here’s the first thing I do.

Step 1: See the SQL EF Core is actually running.

The ToQueryString() method is your best friend. It shows you the …

Of course. Here is the rewritten blog post, following your style guide and prompt.

Stop Flying Blind: Safely Log EF Core SQL in Production

I remember the incident vividly. P95 latency for our main API shot through the roof overnight. Our logs showed a bunch of slow HTTP requests, but nothing about why they were slow. We were flying blind. After hours of frantic debugging, we found the culprit: a seemingly innocent LINQ query was generating a monster SQL join, causing a full table scan on a massive table.

We had no visibility into what Entity Framework Core was doing. This one burned me, and I promised myself: never again.

Most developers face this. They either accept the black box and pray, or they enable dangerous logging flags that leak sensitive user data. There’s a much better way to see exactly what SQL EF Core is executing in production without compromising security.

TL;DR: Use a custom DbCommandInterceptor to log SQL, execution time, and parameter metadata (but never the values). Correlate logs with a TraceId and use sampling or thresholds to avoid log noise.

The Common Mistakes That’ll Get You Paged at 3 AM

Before we get to the right way, let’s talk about the traps. I’ve seen these “temporary fixes” make their way into production code, and the results are always painful.

• Never use EnableSensitiveDataLogging() in production. I can’t stress this enough. It logs parameter values. That means passwords, personal user info, and …

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. …

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: …

I’ll never forget the first time I got called at midnight for a performance issue that was my fault. A dashboard in our SaaS app was timing out, and customers were angry. After hours of digging, the culprit was a monster LINQ query generated by Entity Framework Core. It was trying to be clever, but the generated SQL was a performance disaster.

We rewrote that one query using raw SQL with Dapper, and boom: the endpoint went from taking 15 seconds to under 200 milliseconds.

That experience taught me a crucial lesson. The endless debate about EF Core vs. Dapper is asking the wrong question. It’s not about which one is “better.” It’s about knowing which tool to grab for the job at hand. This isn’t about benchmarks; it’s about shipping features that are fast and easy to maintain.

Let’s break down how I decide in the real world.

EF Core: Your Go-To for Business Logic

I start every new project with EF Core. The productivity boost is just too massive to ignore. It shines when your main job is translating complex business rules into code.

Modeling Your Business, Not Just Tables

When your app has real business rules, you want your code to reflect that. EF Core’s mapping for C# objects (POCOs) is fantastic. Features like navigation properties, value objects, and owned types let you build a rich domain model that makes sense.

Think about an Order class. With EF Core, it’s clean and reflects the business.

public class Order
{ …