📖 Loading Related Data (Include) + N+1 Problem 🔥

🗝️ Introduction

In previous lessons, you learned:

✔ filtering (Where)

✔ projection (Select)

✔ aggregation (Count, Sum, Average)

Now we answer a new question:

👉 How do we load related data (relationships)?

Example:

👉 Students → Enrollments
👉 Courses → Instructor

📖 Core Idea

👉 Navigation properties are NOT loaded automatically

👉 If you don’t explicitly load related data:

❌ EF Core will NOT include it

🎯 Key Teaching Point (VERY IMPORTANT)

👉 If you access related data without loading it correctly, you may create the N+1 problem

🧠 Mental Model

Entity → Related Data
   ↓
Include(...)
   ↓
EF builds JOIN
   ↓
ONE SQL query

💻 Full Console Example (BAD vs GOOD)

/*
========================================================
Code Overview
--------------------------------------------------------
Purpose:
- Demonstrate loading related data
- Show N+1 problem using a bad approach
- Show Include() solution using a good approach
- Compare SQL query shapes before execution

Key Points:
- BAD = multiple queries, known as the N+1 problem
- GOOD = related data loaded using Include()
- Include() can reduce repeated database calls
- ToQueryString() previews SQL query shape
- Runtime logging shows actual executed SQL
========================================================
*/

using Microsoft.EntityFrameworkCore;
using Microsoft.Extensions.Configuration;
using Microsoft.Extensions.Logging;
using TrainingCenter.Data;


// Configuration setup
IConfiguration configuration = new ConfigurationBuilder()
    .SetBasePath(Directory.GetCurrentDirectory())
    .AddJsonFile("appsettings.json", optional: false)
    .Build();


// Read connection string
string? connectionString =
    configuration.GetConnectionString("DefaultConnection");


// Validate values
if (string.IsNullOrWhiteSpace(connectionString))
{
    Console.WriteLine("Connection string not found.");
    return;
}


// Create options
var options =
    new DbContextOptionsBuilder<AppDbContext>()
        .UseSqlServer(connectionString)
        .LogTo(Console.WriteLine, LogLevel.Information)
        .EnableSensitiveDataLogging()
        .Options;


// Create context
using var context = new AppDbContext(options);


// Test connection if relevant
if (!context.Database.CanConnect())
{
    Console.WriteLine("Could not connect to TrainingCenterDB.");
    return;
}

Console.WriteLine("Connected successfully.");
Console.WriteLine();


// Call main methods
CompareInclude(context);


/// <summary>
/// Demonstrates bad vs good loading of related data using manual queries vs Include().
/// </summary>
static void CompareInclude(AppDbContext context)
{
    ShowBadNPlusOneApproach(context);

    PrintSeparator();

    ShowGoodIncludeApproach(context);
}


/// <summary>
/// Demonstrates the bad N+1 approach by loading students first,
/// then running one additional count query per student.
/// </summary>
static void ShowBadNPlusOneApproach(AppDbContext context)
{
    Console.WriteLine("BAD APPROACH - N+1 Problem");
    Console.WriteLine("--------------------------");
    Console.WriteLine();

    // Build query first
    var studentsQuery =
        context.Students;

    // Preview SQL before execution
    PreviewSQLUsingToQueryString(studentsQuery.ToQueryString());

    // Execute first query: load all students
    var students = studentsQuery.ToList();

    Console.WriteLine();

    foreach (var student in students)
    {
        // Build query first for each student
        var enrollmentsQuery =
            context.Enrollments
                   .Where(e => e.StudentId == student.StudentId);

        // Preview SQL query shape
        PreviewSQLUsingToQueryString(enrollmentsQuery.ToQueryString());

        // Execute Count() for each student
        // ToQueryString previews query shape,
        // runtime logging shows actual executed SQL for Count().
        int enrollmentsCount =
            enrollmentsQuery.Count();

        Console.WriteLine(
            $"{student.FirstName} {student.LastName} - Enrollments: {enrollmentsCount}");
    }

    Console.WriteLine();
    Console.WriteLine("Problem: One query for students + one query per student.");
    Console.WriteLine();
}


/// <summary>
/// Demonstrates the good approach by loading students with their enrollments using Include().
/// </summary>
static void ShowGoodIncludeApproach(AppDbContext context)
{
    Console.WriteLine("GOOD APPROACH - Include()");
    Console.WriteLine("-------------------------");
    Console.WriteLine();

    // Build query first
    var query =
        context.Students
               .Include(s => s.Enrollments);

    // Preview SQL before execution
    PreviewSQLUsingToQueryString(query.ToQueryString());

    // Execute query
    var studentsWithEnrollments = query.ToList();

    Console.WriteLine();
    foreach (var student in studentsWithEnrollments)
    {
        Console.WriteLine(
            $"{student.FirstName} {student.LastName} - Enrollments: {student.Enrollments.Count}");
    }

    Console.WriteLine();
    Console.WriteLine("Result: Related enrollments are loaded with the students.");
    Console.WriteLine();
}


/// <summary>
/// Prints a separator between examples.
/// </summary>
static void PrintSeparator()
{
    Console.WriteLine(new string('-', 60));
    Console.WriteLine();
}


/// <summary>
/// Displays generated SQL before execution.
/// </summary>
static void PreviewSQLUsingToQueryString(string SQLString)
{
    Console.WriteLine("\nPreview SQL using ToQueryString():");
    Console.WriteLine("----------------------------------");
    Console.WriteLine(SQLString);
    Console.WriteLine();
}

🔍 SQL Comparison

❌ BAD (N+1 Problem)

Query 1

SELECT * FROM Students;

Then for EACH student:

SELECT COUNT(*)
FROM Enrollments
WHERE StudentId = @StudentId;

👉 If you have 100 students:

❌ 101 queries executed

✅ GOOD (Include)

SELECT [s].[StudentId], [s].[FirstName], ..., 
       [e].[EnrollmentId], [e].[StudentId], ...
FROM [Students] AS [s]
LEFT JOIN [Enrollments] AS [e]
ON [s].[StudentId] = [e].[StudentId];

👉 Result:

✅ ONE query only not 101 queries !!!

⚠️ The N+1 Problem (VERY IMPORTANT 🔥)

❌ What is it?

1 query → load main data
N queries → load related data

❌ Example

Students = 100

1 query → students
100 queries → enrollments

Total = 101 queries ❌

✅ Correct Approach

1 query → everything

💡 Real-World Scenario

🎯 Dashboard

👉 Show:

✔ Student Name

✔ Number of Courses

❌ Bad

foreach (var student in students)
{
    context.Enrollments.Count(...)
}

✅ Good

context.Students
    .Include(s => s.Enrollments)

💼 Professional Insight

In real-world systems:

👉 N+1 problem is one of the

most common performance issues

👉 It causes:

❌ slow applications
❌ database overload
❌ unnecessary queries

👉 Professionals ALWAYS:

✔ use Include()
✔ or projection
✔ or optimized queries

🧠 Mental Anchor (Lock This 🔒)

👉 If you see a loop with a database call inside it — you likely have an N+1 problem.

🔗 Interconnection

Now you understand:

✔ how to load related data
✔ what Include() does
✔ how JOIN is generated
✔ how to avoid N+1 problem

👉 Next step:

👉 Projection vs Include (Which one to use? 🔥)

🏁 Conclusion

In this lesson, you learned:

✔ how to load related data using Include()
✔ how N+1 problem happens
✔ how to compare bad vs good approaches
✔ how EF Core generates JOIN queries

👉 This is one of the most critical performance lessons in EF Core 🚀

🚀 Next Lesson

📖 Projection vs Include (When to Use Each 🔥)

Project Source Code: