How to Optimize LINQ Queries in Entity Framework Core to Avoid the N+1 Problem

Introduction

When working with Entity Framework Core (EF Core), LINQ queries make it easy to fetch and manipulate data. However, if not used carefully, they can lead to serious performance issues. One of the most common problems developers face is the N+1 query problem.

This issue can silently slow down your application by executing multiple unnecessary database queries. In real-world applications with large datasets, this can significantly impact performance and scalability.

In this article, we will understand what the N+1 problem is, why it happens, and how to optimize LINQ queries in EF Core using simple and practical techniques.

What is the N+1 Problem?

The N+1 problem occurs when your application executes:

• 1 query to fetch the main data

• N additional queries to fetch related data (one for each record)

Example of N+1 Problem

var orders = context.Orders.ToList();

foreach (var order in orders)
{
    Console.WriteLine(order.Customer.Name);
}

What Happens Internally?

• First query → Fetch all orders

• Then for each order → Fetch customer separately

If you have 100 orders, this results in 101 queries.

This is highly inefficient.

Why Does the N+1 Problem Happen?

The main reason is lazy loading or accessing navigation properties without explicitly including related data.

When EF Core sees order.Customer, it loads the customer data only when needed, triggering separate queries.

How to Detect the N+1 Problem

You can identify it by:

• Watching SQL logs

• Using profiling tools

• Noticing slow performance

• Seeing repeated similar queries in logs

Best Ways to Optimize LINQ Queries in EF Core

1. Use Eager Loading with Include()

The most common solution is using Include() to load related data in a single query.

var orders = context.Orders
    .Include(o => o.Customer)
    .ToList();

Why This Works

• Fetches orders and customers together

• Reduces multiple queries into one

2. Use ThenInclude() for Nested Data

When you have deeper relationships:

var orders = context.Orders
    .Include(o => o.Customer)
    .ThenInclude(c => c.Address)
    .ToList();

This ensures all related data is loaded efficiently.

3. Use Select() for Projection (Best Practice)

Instead of loading full entities, select only required fields.

var orders = context.Orders
    .Select(o => new
    {
        o.Id,
        CustomerName = o.Customer.Name
    })
    .ToList();

Benefits

• Reduces data transfer

• Improves performance

• Avoids unnecessary joins

4. Disable Lazy Loading

Lazy loading is a common cause of N+1 issues.

You can disable it:

options.UseLazyLoadingProxies(false);

This forces you to explicitly load related data.

5. Use Explicit Loading When Needed

Explicit loading gives you control over when data is fetched.

var order = context.Orders.First();

context.Entry(order)
    .Reference(o => o.Customer)
    .Load();

This is useful when you need data conditionally.

6. Use AsNoTracking() for Read-Only Queries

var orders = context.Orders
    .AsNoTracking()
    .Include(o => o.Customer)
    .ToList();

Benefits

• Improves performance

• Reduces memory usage

7. Use Split Queries for Large Data

EF Core allows splitting queries to avoid complex joins:

var orders = context.Orders
    .Include(o => o.Customer)
    .AsSplitQuery()
    .ToList();

This can improve performance for large datasets.

8. Avoid Calling ToList() Too Early

Bad practice:

var orders = context.Orders.ToList();
var filtered = orders.Where(o => o.Total > 100);

Better approach:

var orders = context.Orders
    .Where(o => o.Total > 100)
    .ToList();

Why?

Filtering in database is faster than filtering in memory.

9. Use Batch Queries When Possible

Instead of multiple queries, combine them logically.

10. Monitor Generated SQL

Always check what SQL EF Core generates:

var query = context.Orders
    .Include(o => o.Customer);

Console.WriteLine(query.ToQueryString());

This helps you understand and optimize queries better.

Difference Between Lazy Loading vs Eager Loading vs Explicit Loading

Feature	Lazy Loading	Eager Loading	Explicit Loading
Queries	Multiple	Single	Controlled
Performance	Poor	Good	Moderate
Control	Low	Medium	High
Use Case	Small data	Most scenarios	Conditional loading

Real-World Optimized Example

Bad Code (N+1 Problem)

var blogs = context.Blogs.ToList();

foreach (var blog in blogs)
{
    Console.WriteLine(blog.Posts.Count);
}

Optimized Code

var blogs = context.Blogs
    .Include(b => b.Posts)
    .ToList();

Now only one query is executed.

Key Takeaways

• Always use Include() for related data

• Prefer Select() for better performance

• Avoid lazy loading in large applications

• Write queries that execute in database, not memory

• Monitor SQL queries regularly

Conclusion

Optimizing LINQ queries in Entity Framework Core is essential for building fast and scalable applications. The N+1 problem is a common mistake, but it can be easily avoided by understanding how EF Core works.

By using techniques like eager loading, projections, disabling lazy loading, and writing efficient queries, you can significantly improve your application's performance.

In simple words, always aim to fetch only what you need, in as few queries as possible. This is the key to mastering EF Core performance optimization.