EF Core & SQL Deep Dive — Change Tracker, N+1, and Performance

Learning Context: Part of Phase 1 (Foundation — .NET Internals) in the Senior .NET Engineer Learning Path 2026.

Covers these Phase 1 checklist items:

• ⬜ EF Core Deep Dive — Change tracker, query compilation, AsNoTracking, N+1 detection

Prerequisites: NET Internals — How It All Works (DI lifetimes, async/await)

---

🧸 Baby Explanation: The Library Analogy

Think of your database as a library 📚 and EF Core as a librarian 👩‍💼.

Concept	Library Analogy
Database	The library building with all the books
EF Core	The librarian who fetches books for you
LINQ Query	You telling the librarian what you need
SQL	The librarian’s exact instructions to find the book
Change Tracker	The librarian’s notepad — writing down every book you touch
.ToList()	The librarian actually going to the shelf and bringing the books

The most important thing to understand: EF Core translates your C# LINQ code into SQL. When you write:

var orders = context.Orders.Where(o => o.Status == "Pending").ToList();

EF Core generates this SQL:

SELECT * FROM Orders WHERE Status = 'Pending';

The .ToList() is the trigger — before that, you’re just building the query. After .ToList(), the query executes and data is in memory.

---

1. The N+1 Query Problem 🔥🔥🔥

What It Is

This is the #1 performance killer in EF Core applications. It happens when you load a collection of items, and then access related data for each item individually.

The bad code:

// 1 query for all orders
var orders = context.Orders.ToList();
 
foreach (var order in orders)
{
    // ❌ For EACH order, another query — N queries!
    Console.WriteLine($"Order {order.Id} has {order.Items.Count} items");
}

What actually hits the database:

-- Query 1: Get all orders
SELECT * FROM Orders
 
-- Query 2: Get items for Order 1
SELECT * FROM OrderItems WHERE OrderId = 1
 
-- Query 3: Get items for Order 2
SELECT * FROM OrderItems WHERE OrderId = 2
 
-- ... and so on for N orders

If you have 100 orders, that’s 1 + 100 = 101 queries. That’s why it’s called N+1.

The Fix: Eager Loading

// ✅ One query with JOIN
var orders = context.Orders.Include(o => o.Items).ToList();
 
foreach (var order in orders)
{
    Console.WriteLine($"Order {order.Id} has {order.Items.Count} items");
}

What hits the database now:

-- Just 1 query with a JOIN
SELECT * FROM Orders
LEFT JOIN OrderItems ON Orders.Id = OrderItems.OrderId

How to Detect N+1

Turn on EF Core logging in Program.cs:

builder.Services.AddDbContext<AppDbContext>(options =>
    options.UseSqlServer(connectionString)
           .LogTo(Console.WriteLine, LogLevel.Information));  // 👈 See every query!

Or use SQL Server Profiler / Azure Query Insights in production.

---

2. The Three Loading Strategies

Strategy	How It Works	SQL Generated	When to Use
Eager Loading (.Include())	Loads related data immediately with JOIN	SELECT ... JOIN ...	When you KNOW you need the related data
Explicit Loading (.Entry().Reference().Load())	Loads related data on demand after main query	Multiple queries, but only if you ask	Rare — for conditional loading
Lazy Loading (proxies)	Loads related data automatically when accessed	Query fires on every property access	⚠️ Avoid in most cases — causes N+1

Eager Loading — Deep Dive

// Single level
var orders = context.Orders.Include(o => o.Items).ToList();
 
// Nested (two levels deep)
var orders = context.Orders
    .Include(o => o.Items)
    .ThenInclude(i => i.Product)
    .ToList();
 
// Multiple related entities
var orders = context.Orders
    .Include(o => o.Items)
    .Include(o => o.Customer)
    .ToList();

Lazy Loading — Why to Avoid

// This LOOKS innocent...
var orders = context.Orders.ToList();
 
// But accessing 'Customer' triggers ANOTHER query for EACH order!
foreach (var order in orders)
{
    Console.WriteLine(order.Customer.Name);  // ⚠️ BOOM — N+1!
}

Lazy Loading is disabled by default in modern EF Core. Keep it that way.

---

3. AsNoTracking() — The Performance Freebie

What It Does

EF Core has a Change Tracker — it keeps a copy of every entity it loads so it can detect changes when you call SaveChanges().

// When you load an order...
var order = context.Orders.First();
 
// EF Core stores a "snapshot" of this order internally
// If you modify it and call SaveChanges(), EF knows what changed
 
order.Status = "Shipped";
context.SaveChanges();  // EF generates: UPDATE Orders SET Status = 'Shipped' WHERE Id = ...

This tracking has memory and CPU overhead. For read-only data, it’s wasted work.

// ❌ Tracked — change tracker stores a copy unnecessarily
var products = context.Products.Where(p => p.IsActive).ToList();
 
// ✅ No-tracking — EF skips the snapshot entirely
var products = context.Products
    .Where(p => p.IsActive)
    .AsNoTracking()
    .ToList();

When to Use It

Use AsNoTracking() ✅	Don’t use it ❌
Read-only data (dropdown lists, reports)	Data you plan to update/save
Public-facing API responses	Data that needs change tracking
Large result sets	When you need REFERENCE for relationship
Dashboard / analytics queries

Internally, What Happens?

// Without AsNoTracking:
// 1. EF sends SQL query
// 2. Results come back
// 3. EF creates entity objects
// 4. EF ALSO stores snapshots in change tracker (memory!)
// 5. Returns entities to you
 
// With AsNoTracking:
// 1. EF sends SQL query
// 2. Results come back
// 3. EF creates entity objects
// 4. ✅ Skips change tracker — no snapshot
// 5. Returns entities to you (faster, less memory)

---

4. SQL Indexing Basics

Even with perfect EF Core code, bad database indexing will kill performance.

Clustered Index 🗂️

• One per table (usually the primary key)
• Determines the physical order of data on disk
• Like a phone book — data is sorted by last name

-- Primary key automatically creates a clustered index
CREATE TABLE Orders (
    Id INT PRIMARY KEY,  -- 👈 This creates a clustered index
    ...
);

Non-Clustered Index 📑

• Multiple per table
• A separate structure that points to the actual data
• Like a book index — “See page 42”

-- Create an index for faster lookups on Status
CREATE INDEX IX_Orders_Status ON Orders (Status);

Now this query is fast:

SELECT * FROM Orders WHERE Status = 'Pending';

Without the index, SQL Server has to scan every row in the table (Table Scan).

Covering Index 🏆

An index that contains all columns needed by a query. SQL Server doesn’t need to touch the table at all.

-- If your query only needs Id and Status:
CREATE INDEX IX_Orders_Status_Covering ON Orders (Status) INCLUDE (Id);

How to Detect Bad Indexing

Enable execution plans in SSMS or use:

-- See what SQL Server recommends
SELECT * FROM Orders WHERE Status = 'Pending'
-- Then check the execution plan for "Table Scan" or "Index Scan" vs "Index Seek"

• Index Seek ✅ — Fast (using index to find specific rows)
• Index Scan ⚠️ — Moderate (reading entire index)
• Table Scan ❌ — Slow (reading every row, no useful index)

---

5. Common EF Core Performance Pitfalls

Pitfall 1: .ToList() Too Early

// ❌ BAD: Loads ALL orders into memory, THEN filters
var recentOrders = context.Orders
    .ToList()                           // All rows loaded!
    .Where(o => o.CreatedAt > cutoff);  // Filtered in memory
 
// ✅ GOOD: Filter happens in SQL
var recentOrders = context.Orders
    .Where(o => o.CreatedAt > cutoff)   // Translated to WHERE clause
    .ToList();                          // Only matching rows loaded

Pitfall 2: The Giant JOIN Problem

// ❌ BAD: One massive query with 4 JOINs, lots of duplicate data
var data = context.Orders
    .Include(o => o.Items)
    .ThenInclude(i => i.Product)
    .Include(o => o.Customer)
    .Include(o => o.ShippingAddress)
    .ToList();
 
// ✅ GOOD: Split into multiple queries
var data = context.Orders
    .Include(o => o.Items)
    .ThenInclude(i => i.Product)
    .Include(o => o.Customer)
    .Include(o => o.ShippingAddress)
    .AsSplitQuery()      // 👈 Multiple queries, less duplicate data
    .ToList();

AsSplitQuery() generates separate SQL queries instead of one giant JOIN. This is better when you have many related entities.

Pitfall 3: Inserting Many Rows One by One

// ❌ BAD: 1000 separate INSERT statements, 1000 database round-trips
foreach (var item in items)
{
    context.OrderItems.Add(item);
    context.SaveChanges();  // 👈 Called inside the loop!
}
 
// ✅ GOOD: One batch INSERT
context.OrderItems.AddRange(items);  // 👈 Just adds to change tracker
context.SaveChanges();               // 👈 One database round-trip

Pitfall 4: No Projection

// ❌ BAD: Loads ALL columns, then picks only 2
var users = context.Users.ToList();
var names = users.Select(u => new { u.Id, u.Name });
 
// ✅ GOOD: Only the columns you need are fetched
var names = context.Users
    .Select(u => new { u.Id, u.Name })
    .ToList();

This generates:

-- BAD: SELECT * FROM Users (all columns)
-- GOOD: SELECT Id, Name FROM Users (only what you need)

---

6. Change Tracker — What’s Happening Internally

This is the most misunderstood part of EF Core.

When You Load Data

var order = context.Orders.First(o => o.Id == 1);

Internally:

// EF Core does this (simplified):
// 1. Execute: SELECT * FROM Orders WHERE Id = 1
// 2. Create Order object from the result
// 3. Check if this Order (Id=1) is already being tracked
//    - If YES: return the existing tracked instance
//    - If NO: create a NEW entry in the change tracker
// 4. Store a "snapshot" of Order's current values
// 5. Return the Order object to you

When You Modify Data

order.Status = "Shipped";
// Change tracker notes: "Order 1: Status changed from 'Pending' to 'Shipped'"

When You Call SaveChanges

context.SaveChanges();
// Change tracker says: "Order 1 has changes!"
// EF generates: UPDATE Orders SET Status = 'Shipped' WHERE Id = 1
// Sends ONE command to the database
// If multiple changes: wraps in a TRANSACTION

The Identity Map Pattern

EF Core ensures that loading the same entity twice returns the same instance:

var order1 = context.Orders.First(o => o.Id == 1);
var order2 = context.Orders.First(o => o.Id == 1);
 
Console.WriteLine(ReferenceEquals(order1, order2));  // ✅ TRUE!

This prevents stale data conflicts within the same request.

---

7. The Where() Clause — SQL Translation

Understanding how LINQ translates to SQL helps you write better queries.

// C# LINQ
var result = context.Orders
    .Where(o => o.Status == "Pending" && o.CreatedAt > cutoff)
    .OrderBy(o => o.CreatedAt)
    .Take(10)
    .ToList();
 
// Generated SQL
SELECT TOP(10) * FROM Orders
WHERE Status = 'Pending' AND CreatedAt > @cutoff
ORDER BY CreatedAt;

Common Translations

LINQ	SQL
.First()	SELECT TOP(1) ...
.Count()	SELECT COUNT(*) ...
.Any()	SELECT CASE WHEN EXISTS(...) THEN 1 ELSE 0 END
.Contains(x)	WHERE x IN (...)
.Where(o => o.Name.Contains("abc"))	WHERE Name LIKE '%abc%'
.Skip(10).Take(20)	OFFSET 10 ROWS FETCH NEXT 20 ROWS ONLY

What CAN’T Be Translated

Sometimes EF Core can’t translate your LINQ to SQL and falls back to client-side evaluation:

// ❌ BAD: EF Core can't translate this to SQL
var result = context.Orders
    .Where(o => SomeComplexMethod(o.Status))  // ⚠️ Custom C# method!
    .ToList();
 
// ✅ GOOD: Move the condition to something translatable
var result = context.Orders.ToList()
    .Where(o => SomeComplexMethod(o.Status));  // Filter in memory after

⚠️ Warning: EF Core 3.0+ throws an exception for non-translatable queries (instead of silently doing client evaluation like EF Core 2.x did).

---

📊 Cheat Sheet

Problem	Symptom	Fix
N+1 Queries	Many similar SQL queries in logs	.Include() or .ThenInclude()
Slow first query	Cold start	eager loading, compiled queries
High memory usage	Loading same data repeatedly	.AsNoTracking() for read-only
Giant JOINs	Slow query with many tables	.AsSplitQuery()
Slow inserts	One-by-one SaveChanges() in loop	AddRange() + single SaveChanges()
Too many columns	SELECT * when you need 2 fields	.Select() projection
Table Scan in execution plan	No useful index	Create proper index
Client evaluation	LINQ throws error	Refactor to SQL-translatable expression

---

🧪 Build Challenges

Challenge 1: Profile Your POS API

1. Enable EF Core logging in your POS API
2. Hit the orders endpoint
3. Count how many SQL queries are generated
4. Identify if there’s an N+1 pattern
5. Fix it with .Include() or .AsSplitQuery()

Challenge 2: Measure AsNoTracking()

1. Create two endpoints — one with tracking, one without
2. Both return the same data (100+ rows)
3. Measure response time and memory usage
4. Compare the difference

Challenge 3: Fix a Bad Query

Given this code, find all the problems and fix them:

// Find all problems in this code
var result = context.Orders.ToList()
    .Where(o => o.Status == "Pending")
    .Select(o => new {
        o.Id,
        o.Customer.Name,
        ItemCount = o.Items.Count
    })
    .OrderByDescending(x => x.ItemCount);

Rules:

• ❌ Don’t ask Jeri for code
• ✅ Understand each concept first
• ✅ Use the cheat sheet to identify problems
• ✅ Show Jeri your fixes

---

Related:

• NET Internals — How It All Works — Prerequisite: DI lifetimes, async/await
• Senior .NET Engineer Learning Path 2026 — Phase 1: Foundation