Fix Slow Entity Framework Core Queries: Practical Guide

This guide walks you through diagnosing and fixing slow Entity Framework Core queries in your .NET applications. You’ll learn how to identify performance bottlenecks, apply targeted optimizations like AsNoTracking and compiled queries, and avoid common pitfalls. By the end, you’ll have a clear set of steps to reduce query execution times and improve overall application responsiveness.

What You’ll Need

A .NET project using Entity Framework Core (version 3.0 or later is recommended, but most steps apply to earlier versions).
Access to the database server logs or a profiling tool (like SQL Server Profiler or the EF Core logging output).
Basic familiarity with C# and LINQ.
Optionally, a tool like MiniProfiler or Application Insights for in-app query timing.

Step-by-Step

Enable EF Core Logging
Add logging to your DbContext to see the SQL generated and execution time. In your context configuration, enable logging to the console or a file:
```
protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
{
    optionsBuilder
        .UseSqlServer(connectionString)
        .LogTo(Console.WriteLine, LogLevel.Information)
        .EnableSensitiveDataLogging(); // For development only
}
```
This shows each query, its parameters, and how long it took. Look for queries that take longer than expected.
Identify the Slow Queries
Run your application and perform the actions that seem slow. Review the logged SQL. Note the exact query text and its duration. Also look for repeated queries that hit the same dataâ€”this often indicates an N+1 problem.
Use AsNoTracking for Read-Only Queries
If a query is read-only and you don’t need change tracking, add .AsNoTracking() to the query. This avoids the overhead of tracking entities and can significantly speed up execution, especially for large result sets.
```
var products = await context.Products
    .AsNoTracking()
    .Where(p => p.Price > 100)
    .ToListAsync();
```
Only use this when you won’t update the returned entities.
Project with Select to Only Needed Columns
Instead of loading entire entities, use Select to fetch only the columns you need. This reduces data transfer and database load.
```
var productNames = await context.Products
    .Where(p => p.CategoryId == 1)
    .Select(p => new { p.Id, p.Name })
    .ToListAsync();
```
This also prevents over-fetching and can make the query faster.
Eager Load with Include for Related Data
If you need related data, use Include or ThenInclude to load it in one query instead of multiple lazy-load calls. This avoids the N+1 problem.
```
var orders = await context.Orders
    .Include(o => o.Customer)
    .Include(o => o.Items)
    .ThenInclude(i => i.Product)
    .ToListAsync();
```
Be careful not to over-include; only include what you actually use.

Use Compiled Queries for Repeated Queries
If you run the same query many times with different parameters, compile it once using EF.CompileQuery or EF.CompileAsyncQuery.

private static Func<MyContext, int, IAsyncEnumerable<Product>> _getByCategory =
    EF.CompileAsyncQuery((MyContext ctx, int categoryId) =>
        ctx.Products.Where(p => p.CategoryId == categoryId));

// Usage:
await foreach (var p in _getByCategory(context, 1)) { ... }

This caches the query plan and reduces overhead.

Add Missing Indexes
Examine the SQL generated for slow queries. Look for table scans or key lookups in the execution plan. Use the database’s indexing tools to add indexes on columns used in WHERE, JOIN, and ORDER BY clauses. For example, if you filter by CategoryId, add an index on that column.
Split Queries for Large Data Sets (EF Core 5+)
When loading multiple collections with Include, EF Core may generate a single query with joins that cause a cartesian explosion. Use AsSplitQuery() to generate separate queries for each collection.
```
var data = await context.Orders
    .Include(o => o.Items)
    .AsSplitQuery()
    .ToListAsync();
```
This can reduce the amount of data transferred.
Disable Change Tracking for Bulk Operations
For updates or deletes that affect many rows, use ExecuteUpdate or ExecuteDelete (EF Core 7+) instead of loading entities and modifying them. These execute directly in the database and are much faster.
```
await context.Products
    .Where(p => p.Price < 10)
    .ExecuteUpdateAsync(setters => setters.SetProperty(p => p.IsActive, false));
```
For older versions, consider raw SQL.
Use Raw SQL for Complex Queries
If LINQ generates inefficient SQL, write a raw SQL query using FromSqlRaw or FromSqlInterpolated. This gives you full control over the SQL.
```
var results = await context.Products
    .FromSqlRaw("SELECT * FROM Products WHERE Price > {0}", 100)
    .ToListAsync();
```
Ensure you use parameterization to avoid SQL injection.

Common Mistakes

Overusing AsNoTracking: Using it on queries where you later update the entities will cause errors or extra work. Only use when read-only.
Lazy Loading in Loops: Accessing navigation properties inside a loop triggers a new query each time. Always use eager loading.
Selecting Entire Entities When Only a Few Fields Are Needed: This wastes memory and bandwidth.
Ignoring the SQL Log: Not checking the actual SQL can hide inefficiencies like unnecessary columns or missing joins.
Adding Too Many Indexes: Indexes speed up reads but slow down writes. Only add indexes that are actually used.

Tips

Use Stopwatch in your code to measure query times before and after changes.
Always test with a realistic dataset. Small datasets can hide performance issues.
Consider using a second-level cache (like Redis) for frequently accessed, rarely changing data.
Keep your EF Core version up to date; each release includes performance improvements.
Use AsNoTrackingWithIdentityResolution if you need to avoid tracking but want to keep reference tracking for related entities in the same query.

Troubleshooting

Query still slow after adding AsNoTracking and Select?
Check the execution plan in your database. Look for missing indexes or implicit type conversions. Also verify that the query is not being blocked by locksâ€”use WITH (NOLOCK) only if you understand the consequences of dirty reads.

Compiled query throws an error?
Make sure the compiled query method is static and the context type matches. Also, compiled queries cannot use Include with a lambda that references local variables; use a string include or a different approach.

AsSplitQuery causes an error about multiple queries?
This is expected; split queries execute multiple round trips. If you get an error about data consistency, ensure your database supports it (SQL Server 2012+).

Raw SQL is not returning the expected type?
Use FromSqlRaw with a query that returns all columns of the entity. If you need a custom shape, use a DTO and FromSqlRaw with a projection.

Frequently Asked Questions

Why is my query slow even with an index?

The index might not be used because of a function on the column (e.g., WHERE YEAR(Date) = 2024). Use a range condition instead. Also, the query might be returning too many rowsâ€”consider pagination with Skip and Take.

Should I always use AsNoTracking?

No. Use it only for read-only queries. If you plan to update the entities, keep tracking to avoid extra database calls.

What is the N+1 problem?

It occurs when you load a list of entities and then access a navigation property for each one, causing one query per entity. Use Include to load related data in a single query.

Can I use compiled queries with async?

Yes, use EF.CompileAsyncQuery for async methods. Note that it returns an IAsyncEnumerable, so you need to iterate with await foreach.

Is it better to use raw SQL for all queries?

No. LINQ provides type safety and maintainability. Use raw SQL only for complex queries that LINQ cannot express efficiently.

How do I know if my query is slow?

Enable logging and look for durations above your threshold. Also, use database profiling tools to see actual execution times and waits.

Method: the standard formula for this calculation, rounded as each result states. See how the calculators work or report an error.

More cards in this drawer

The whole Numbers and Powers drawer →