Sql

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 …

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 …