N + 1 Problem in EF Core (Interview Explanation)

The N + 1 Problem is one of the most commonly asked interview questions in Entity Framework Core.

What is N + 1 Problem?

Definition:

The N + 1 problem occurs when:

1 query is executed to fetch parent data
N additional queries are executed to fetch child data

This results in multiple database calls, causing performance issues.

Simple Example

Suppose you have:

Orders (Parent)
OrderItems (Child)

Bad Code (N + 1 Problem)

var orders = context.Orders.ToList();

foreach (var order in orders)
{
    var items = order.OrderItems.ToList();
}

What Happens Internally

If you have 5 orders, EF Core executes:

1 query → Get Orders
5 queries → Get OrderItems for each order

Total queries:

1 + 5 = 6 queries

This is called N + 1 Problem

Visual Explanation

Orders Query → 1

OrderItems Query → N (for each order)

Total = N + 1

Why is it Bad?

❌ Multiple database calls
❌ Slow performance
❌ High database load
❌ Bad scalability

This becomes very dangerous when:

100 orders → 101 queries
1000 orders → 1001 queries

Why Does This Happen?

Because of Lazy Loading.

EF Core loads related data only when accessed, which causes multiple queries.

How to Fix N + 1 Problem ✅

Use Eager Loading with Include()

Good Code

var orders = context.Orders
    .Include(x => x.OrderItems)
    .ToList();

Now EF Core executes:

1 query → Orders + OrderItems

Problem solved! 🎉

Even Better (Best Practice)

Use Select Projection (Best Performance)

var orders = context.Orders
    .Select(o => new 
    {
        o.Id,
        Items = o.OrderItems
    })
    .ToList();

This improves performance further.

Real Interview Example

Bad:

var employees = context.Departments.ToList();

foreach(var dept in employees)
{
    var emp = dept.Employees;
}

Good:

var employees = context.Departments
    .Include(x => x.Employees)
    .ToList();

Interview Questions & Answers 🎯

What is N + 1 Problem?

N + 1 problem occurs when one query is used to fetch parent data and multiple queries are used to fetch related child data, causing performance issues.

How to identify N + 1 Problem?

Multiple DB calls in logs
Slow performance
Lazy loading usage

How to solve N + 1 Problem?

✅ Use Include()
✅ Use Select() projection
✅ Disable Lazy Loading
✅ Use AsSplitQuery() (Advanced)

Advanced Solution (EF Core 5+)

Use AsSplitQuery() if needed:

var orders = context.Orders
    .Include(x => x.OrderItems)
    .AsSplitQuery()
    .ToList();

One-Line Interview Answer

The N + 1 problem occurs when EF Core executes one query for parent data and N queries for related child data, causing performance issues.

Pro Interview Tip 💡

If interviewer asks:

"How do you detect N + 1 problem?"

Answer:

Enable SQL logging
Use profiler
Check EF Core generated queries