Fix Slow EF Core Queries with Database Indexing

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 exact SQL being sent to the database, with no magic.

var sql = context.Orders
    .Where(o => o.CustomerId == customerId && o.OrderDate >= DateTime.UtcNow.AddDays(-30))
    .ToQueryString();

Console.WriteLine(sql);

Step 2: Check your WHERE and JOIN clauses.

Once you have the SQL, the decision is pretty simple:

• Is a column in a WHERE clause? If it has high cardinality (lots of unique values, like an email or username), it almost certainly needs an index. This is non-negotiable for performance.
• Is it a foreign key used in a JOIN? Index it. This is critical for speeding up queries that pull related data. EF Core actually does this for you by convention, but it’s good to be aware of.
• Is a column in an ORDER BY clause? This is a strong candidate for an index, especially if it’s paired with a WHERE clause. An index can store the data pre-sorted, which saves the database a ton of work.

What about columns with low cardinality, like a Status enum or a boolean IsActive flag? Don’t bother indexing them. The database can scan the few possible values faster than it can use an index.

How to Add Indexes the Right Way in EF Core

Forget writing raw SQL in your migrations. Define your indexes directly in your DbContext using the Fluent API. It’s cleaner and keeps your configuration in one place.

Single Column Index

This is your bread and butter. It’s for simple lookups, like finding a user by their email.

// In your DbContext's OnModelCreating method
modelBuilder.Entity<User>()
    .HasIndex(u => u.Email)
    .HasDatabaseName("IX_User_Email")
    .IsUnique(); // Enforces that no two users can have the same email

Pro-tip: Always name your indexes with HasDatabaseName. If you don’t, EF Core generates a name for you, and it can change if you modify the index, leading to painful migration issues.

Composite Index

What about queries with multiple conditions? A composite index covers them all in one shot.

// For a query that filters by CustomerId AND OrderDate
modelBuilder.Entity<Order>()
    .HasIndex(o => new { o.CustomerId, o.OrderDate })
    .HasDatabaseName("IX_Order_Customer_Date");

This is a huge one for performance. That index is perfect for the query we looked at earlier. The order of columns in a composite index matters. A good rule of thumb is to put the column with the highest selectivity (the one that filters out the most rows) first.

Covering Index

This is a more advanced trick, but it’s a lifesaver for read-heavy APIs. A covering index includes extra columns of data right in the index itself.

modelBuilder.Entity<Product>()
    .HasIndex(p => p.CategoryId)
    .IncludeProperties(p => new { p.Name, p.Price }) // Include these columns in the index
    .HasDatabaseName("IX_Product_Category_Covering");

When you query for a product’s name and price within a category, the database can get everything it needs directly from the index. It never even has to touch the actual table, which avoids a slow operation called a “key lookup.” This is fantastic for queries that project into DTOs.

Migrations Handle the Heavy Lifting

Once you’ve defined your indexes, creating the migration is business as usual.

dotnet ef migrations add AddUserEmailIndex

EF Core will generate the correct SQL to create the index in your database.

// Inside the generated migration file
migrationBuilder.CreateIndex(
    name: "IX_User_Email",
    table: "Users",
    column: "Email",
    unique: true);

Hold Up, Don’t Index Everything

New developers often make the mistake of indexing every column they filter on. This is a bad idea. Indexes speed up reads (SELECT), but they add overhead to writes (INSERT, UPDATE, DELETE). Every time you change data, the database has to update the table and all of its indexes.

Here’s when I’d avoid adding an index:

• Low-cardinality columns: Think status columns (Active/Inactive) or boolean flags. An index here is just write overhead with almost no read benefit.
• Very small tables: If a table has under a thousand rows, a table scan is already so fast that an index won’t make a meaningful difference.
• Frequently updated columns: If a column’s value changes constantly, the cost of updating the index might outweigh the read benefits.
• Too many indexes on one table: I get nervous when I see more than 5-7 indexes on a single table. It can be a sign that write performance is suffering. It’s time to review and consolidate.

Prove It: Measuring Before and After

Don’t just assume your index worked. Prove it. Use TagWith to label your queries so you can find them easily in your logs or monitoring tools.

// Program.cs
builder.Services.AddDbContext<AppDbContext>(options =>
    options.UseSqlServer(connectionString)
           .LogTo(Console.WriteLine, LogLevel.Information));

// In your repository or service...
// BEFORE: Run the query and log the time
var slowQuery = await context.Orders
    .TagWith("CustomerOrders_BeforeIndex")
    .Where(o => o.CustomerId == customerId && o.OrderDate >= cutoffDate)
    .ToListAsync();
// I've seen this take 3,200ms in production.

// AFTER adding IX_Order_Customer_Date:
var fastQuery = await context.Orders
    .TagWith("CustomerOrders_AfterIndex")
    .Where(o => o.CustomerId == customerId && o.OrderDate >= cutoffDate)
    .ToListAsync();
// The result? Around 45ms. That's a 70x improvement.

This kind of before-and-after measurement is how you justify your work and build confidence in your changes.

Look at the Execution Plan. Seriously.

If you really want to know what the database is doing, you have to look at the query’s execution plan. This is the ultimate source of truth. You can use SQL Server Management Studio (SSMS) or Azure Data Studio.

Paste the SQL generated by ToQueryString() into a query window and check the plan.

You’re looking for a few key things:

• Index Seek: This is good. It means the database used an index to find the data efficiently.
• Table Scan: This is bad. It means the database had to read the entire table.
• Key Lookup: This is a performance warning. It means the database found a record in an index but then had to go back to the main table to fetch other columns. This is the exact problem that covering indexes solve.

My Go-To Indexing Checklist

This is what I run through before shipping code that touches a high-traffic table.

• Index all foreign keys.
• Index columns in WHERE clauses that have high cardinality.
• Use composite indexes for queries with multiple filters or filter + sort.
• Explicitly name all indexes with HasDatabaseName.
• Review the execution plan for any critical query.
• Measure the performance before and after.

And just as important:

• Do NOT index boolean flags or low-cardinality status columns.
• Do NOT guess. See the SQL, check the plan, measure the impact.

When to Use Indexes (and When to Skip Them)

Here’s the bottom line.

I always add indexes for:

• Primary keys (EF Core does this automatically).
• Foreign keys involved in joins.
• Columns that are frequently used for filtering in WHERE clauses, especially if they have many unique values (e.g., Email, Username, ExternalId).

I consider adding indexes for:

• Columns used in ORDER BY clauses, often as part of a composite index.
• Columns needed for read-only projections, where a covering index can prevent key lookups.

I almost never add indexes for:

• Columns with very few distinct values (Status, Type, IsActive). The cost isn’t worth the tiny (if any) benefit.
• Tables that are write-heavy but rarely read.

EF Core gives us amazing tools to build applications quickly, but it doesn’t remove our responsibility to understand the database. Get your indexing right, and you’ll close the performance gap between your laptop and production.

References

• Microsoft Docs: Indexes in EF Core
• Microsoft Docs: Clustered and Nonclustered Indexes Described
• Brent Ozar: How to Think Like the SQL Server Engine

FAQ