EF Core is slow? 8 common causes and how to fix them

At the start, everything is fast. The database has a few thousand rows, screens open instantly, and nobody thinks about what SQL Entity Framework sends. Two years and a few million rows later, the same screens take seconds, and the team starts talking about switching to “plain SQL”.

EF Core is rarely the culprit on its own. It almost always does exactly what it was told, it’s just been told to do something expensive. These are the eight causes we find most often, each with code before and after.

First: look at the SQL EF sends

Until you see the SQL, you’re guessing. EF Core shows it in two ways:

options.UseSqlServer(connectionString)
       .LogTo(Console.WriteLine, LogLevel.Information);          // every query to the console, development only

var sql = db.Orders.Where(o => o.Total > 1000).ToQueryString();  // the SQL of one query, without running it

In production, Application Insights shows the same: every call to the database, how long it took and which request it came from.

1. N+1 queries

One query for the list, then one more for each row. A hundred orders mean a hundred and one trips to the database.

var orders = await db.Orders.ToListAsync();
foreach (var order in orders)
{
    var customer = await db.Customers.FindAsync(order.CustomerId);  // one query per order
}

The fix is to fetch everything at once, with Include or, better still, with the projection from the next point.

2. Whole entities when the screen needs three fields

The order list shows the number, the customer and the total, and the application loads whole orders with all their lines.

var rows = await db.Orders
    .Select(o => new OrderRow(o.Number, o.Customer.Name, o.Total))  // only what the screen shows
    .ToListAsync();

A projection with Select solves both N+1 and the excess data in one go: one query, only the columns you need.

3. Change tracking for read-only data

By default EF tracks every entity it loads, in case you change it. For screens that only display data, that’s unnecessary work and memory.

var products = await db.Products
    .AsNoTracking()                                                 // read only, no tracking
    .Where(p => p.IsActive)
    .ToListAsync();

4. Filtering in memory instead of in the database

One ToList() in the wrong place, and the whole table travels to the application to be filtered there.

// before: the whole table into memory, filtered in the application
var open = (await db.Orders.ToListAsync()).Where(o => o.Status == OrderStatus.Open);

// after: the database filters
var open = await db.Orders.Where(o => o.Status == OrderStatus.Open).ToListAsync();

On a development machine with a thousand rows you won’t see the difference. In production with a million rows, you will.

5. Cartesian explosion with several Includes

When two collections are loaded at once, EF by default sends one query with JOINs. An order with 20 lines and 5 payments then returns 100 rows instead of 26.

var orders = await db.Orders
    .Include(o => o.Lines)
    .Include(o => o.Payments)
    .AsSplitQuery()                                                 // a separate query for each collection
    .ToListAsync();

6. Bulk changes row by row

Loading thousands of rows, changing them one by one and saving means thousands of rows over the network and thousands of updates. EF Core can do it with one statement in the database:

await db.Orders
    .Where(o => o.Status == OrderStatus.Open && o.CreatedAt < cutoff)
    .ExecuteUpdateAsync(s => s.SetProperty(o => o.Status, OrderStatus.Expired));

The same goes for deleting, with ExecuteDeleteAsync.

7. Pagination that gets slower and slower

With Skip the database has to go through all the previous rows every time, so page one hundred and one is much slower than page one. It’s faster to continue from the last row you saw:

// before: the database skips all the previous rows
var page = await db.Orders.OrderBy(o => o.Id).Skip(pageNumber * 20).Take(20).ToListAsync();

// after: it continues from the last row seen
var page = await db.Orders.OrderBy(o => o.Id).Where(o => o.Id > lastId).Take(20).ToListAsync();

This works for “next” and “previous”, not for jumping to any page. For most lists that’s quite enough.

8. Missing indexes

The best-written query is still slow if the database has to read the whole table to find ten rows. In EF Core, indexes are defined with the model, so they are part of the migrations and the code:

modelBuilder.Entity<Order>()
    .HasIndex(o => new { o.CustomerId, o.CreatedAt });              // for "a customer's orders, newest first"

Which indexes to add is shown by the execution plan of the slowest queries, not by gut feeling.

In which order

1. See the SQLLogTo, ToQueryString, Application Insights
2. N+1Queries in loops and too many calls
3. ProjectionsSelect and AsNoTracking
4. IndexesBased on the execution plan

The order matters. An index won’t save a screen that sends two hundred queries, and a projection won’t help a query that reads the whole table. That’s why you start with what the SQL shows. The bigger picture, from the database to the server, is in the article on slow applications.

How we work

ProCoding is a .NET studio from Split, Croatia, and EF Core and SQL Server performance is one of the things we do most often. We go through your slowest screens, show you what SQL they send and why, and fix them, on our own or together with your team. The first step is a free 30-minute call.

Sources

• Performance overview, EF Core, Microsoft Learn
• Efficient querying, EF Core, Microsoft Learn
• Single vs. split queries, EF Core, Microsoft Learn
• ExecuteUpdate and ExecuteDelete, EF Core, Microsoft Learn
• Pagination, EF Core, Microsoft Learn