Published / updated29 Aug 2026

Relationships and Loading Related Data

Before you start

You need: CRUD (Article 05).

Time: about 50 minutes, plus the practice.

Learning objective

Model any relationship correctly and choose a loading strategy that produces the round trips and row counts you intended.

Topics

• Navigation properties and foreign keys
• One-to-many, one-to-one, many-to-many
• Delete behaviour
• Eager loading with Include and ThenInclude
• Filtered includes
• Projection instead of Include
• Explicit loading
• Lazy loading, and why to avoid it
• Cartesian explosion and AsSplitQuery

Navigation properties

public class Student
{
    public int Id { get; set; }
    public int SchoolId { get; set; }
    public string Name { get; set; } = string.Empty;

    // Reference navigation — the "one" side
    public School School { get; set; } = null!;

    // Collection navigations — the "many" side
    public ICollection<ExamResult> ExamResults { get; set; } = new List<ExamResult>();
    public ICollection<FeeAccount> FeeAccounts { get; set; } = new List<FeeAccount>();
}

public class ExamResult
{
    public int Id { get; set; }
    public int SchoolId { get; set; }
    public int StudentId { get; set; }       // foreign key
    public int ExamId { get; set; }
    public decimal? MarksObtained { get; set; }
    public bool IsAbsent { get; set; }

    public Student Student { get; set; } = null!;
    public Exam Exam { get; set; } = null!;
}

Declare the foreign key property explicitly. EF Core can work with only a navigation property (a shadow foreign key), but an explicit StudentId lets you filter and set the relationship without loading the related entity:

// With an explicit FK — no extra query
ExamResult result = new ExamResult { StudentId = 12, ExamId = 5, MarksObtained = 87m };

// Without one, you must load the Student first
result.Student = await _context.Students.FindAsync(12);

= null! on a required reference navigation silences the nullable warning. It is a promise to the compiler that EF Core will populate it — which is true only when you actually load it. An unloaded navigation is null at run time, and that is the source of many null-reference exceptions in EF Core code.

One-to-many

builder.HasOne(r => r.Student)
       .WithMany(s => s.ExamResults)
       .HasForeignKey(r => r.StudentId)
       .OnDelete(DeleteBehavior.Restrict);

Read it as a sentence: this entity has one Student, which has many ExamResults, joined by StudentId.

DeleteBehavior	On delete of the principal
Restrict	Reject the delete
Cascade	Delete dependents too
SetNull	Null the FK — requires a nullable FK
NoAction	Leave it to the database

EF Core defaults to Cascade for a required relationship. Deleting one student then destroys their exam results, attendance and payments. Set Restrict explicitly on anything with history — the rejection forces the application to soft-delete instead, which is what you want.

One-to-one

builder.HasOne(s => s.User)
       .WithOne(u => u.Student)
       .HasForeignKey<Student>(s => s.UserId)
       .OnDelete(DeleteBehavior.SetNull);

HasForeignKey<Student> names which side holds the key — EF Core cannot infer it for a one-to-one. The dependent side needs a unique index, which EF Core adds automatically.

Many-to-many

public class Teacher
{
    public ICollection<Subject> Subjects { get; set; } = new List<Subject>();
}

public class Subject
{
    public ICollection<Teacher> Teachers { get; set; } = new List<Teacher>();
}

builder.HasMany(t => t.Subjects)
       .WithMany(s => s.Teachers)
       .UsingEntity("TeacherSubject");

EF Core 5 and later create the join table implicitly. Adding a relationship is then just:

teacher.Subjects.Add(subject);
await _context.SaveChangesAsync(ct);

When the join carries its own data — when the teacher was assigned, by whom — make it an entity:

public class TeacherSubject
{
    public int TeacherId { get; set; }
    public int SubjectId { get; set; }
    public DateTime AssignedOn { get; set; }
    public string AssignedBy { get; set; } = string.Empty;

    public Teacher Teacher { get; set; } = null!;
    public Subject Subject { get; set; } = null!;
}

builder.HasMany(t => t.Subjects)
       .WithMany(s => s.Teachers)
       .UsingEntity<TeacherSubject>(
           right => right.HasOne(ts => ts.Subject).WithMany().HasForeignKey(ts => ts.SubjectId),
           left => left.HasOne(ts => ts.Teacher).WithMany().HasForeignKey(ts => ts.TeacherId),
           join => join.HasKey(ts => new { ts.TeacherId, ts.SubjectId }));

The composite key both identifies the row and prevents duplicate assignments.

Eager loading with Include

List<ExamResult> results = await _context.ExamResults
    .Include(r => r.Student)
    .Include(r => r.Exam)
        .ThenInclude(e => e.Subject)
    .Where(r => r.SchoolId == schoolId && r.ExamId == examId)
    .ToListAsync(ct);

Include adds a JOIN; ThenInclude continues down the chain from the previously included navigation.

// Two branches from the same root — restart with Include
List<Student> students = await _context.Students
    .Include(s => s.ExamResults)
        .ThenInclude(r => r.Exam)
    .Include(s => s.FeeAccounts)
        .ThenInclude(fa => fa.Payments)
    .Where(s => s.SchoolId == schoolId)
    .ToListAsync(ct);

That query is also the problem the rest of this article is about.

Cartesian explosion

A student with 20 exam results and 2 fee accounts holding 30 payments produces 20 × 2 × 30 = 1,200 rows — for one student. Every student column is repeated in all 1,200.

For 400 students that is roughly half a million rows to transfer a few thousand records. The query is correct and unusably slow, and nothing in the C# looks wrong.

List<Student> students = await _context.Students
    .Include(s => s.ExamResults)
    .Include(s => s.FeeAccounts)
        .ThenInclude(fa => fa.Payments)
    .Where(s => s.SchoolId == schoolId)
    .AsSplitQuery()
    .ToListAsync(ct);

AsSplitQuery issues one statement per collection and stitches them together — four small queries instead of one enormous one.

Set it as the default and opt out where you need a single snapshot:

options.UseSqlServer(connectionString,
    sql => sql.UseQuerySplittingBehavior(QuerySplittingBehavior.SplitQuery));

.AsSingleQuery()   // when consistency across the collections matters

The trade: split queries are several statements, so a concurrent write can produce inconsistent results between them. Inside a transaction, or where consistency matters, use a single query and accept the row count.

Including two or more collections is the signal. One collection plus reference navigations is usually fine.

Filtered includes

List<Student> students = await _context.Students
    .Include(s => s.FeeAccounts.Where(fa => fa.AcademicYear == "2024-25"))
        .ThenInclude(fa => fa.Payments.Where(p => !p.IsCancelled))
    .Where(s => s.SchoolId == schoolId)
    .ToListAsync(ct);

Only the matching children are loaded. Before EF Core 5 this needed a projection or a second query.

A filtered include gives a partial collection. student.FeeAccounts holds only 2024-25 accounts, and code elsewhere treating it as "all accounts" is wrong. Two filtered includes of the same navigation in one query is an error.

Projection instead of Include

For read-only work, this is usually the better answer.

List<ExamResultDto> results = await _context.ExamResults
    .Where(r => r.SchoolId == schoolId && r.ExamId == examId)
    .Select(r => new ExamResultDto
    {
        StudentName = r.Student.Name,
        RollNumber = r.Student.RollNumber,
        SubjectName = r.Exam.Subject.Name,
        MarksObtained = r.MarksObtained,
        MaxMarks = r.Exam.MaxMarks,
        IsAbsent = r.IsAbsent
    })
    .ToListAsync(ct);

No Include at all — EF Core generates the joins from the property access inside Select.

	Include	Projection
Columns fetched	Every column of every entity	Only what you named
Tracked	Yes	No
Memory	Full entity graph	Flat DTOs
Shape	Fixed by the model	Whatever the caller needs

Projection also nests:

List<StudentSummaryDto> summaries = await _context.Students
    .Where(s => s.SchoolId == schoolId && s.Status == StudentStatus.Active)
    .Select(s => new StudentSummaryDto
    {
        PublicId = s.PublicId,
        Name = s.Name,
        RollNumber = s.RollNumber,
        ResultCount = s.ExamResults.Count(r => !r.IsAbsent),
        AverageMarks = s.ExamResults.Where(r => !r.IsAbsent).Average(r => r.MarksObtained),
        TotalOutstanding = s.FeeAccounts.Sum(fa => fa.TotalFees - fa.PaidAmount),
        RecentResults = s.ExamResults
            .OrderByDescending(r => r.Exam.ExamDate)
            .Take(5)
            .Select(r => new ResultDto
            {
                ExamName = r.Exam.ExamName,
                MarksObtained = r.MarksObtained
            })
            .ToList()
    })
    .ToListAsync(ct);

Counts, averages and sums are computed in SQL. The equivalent with Include would load every result and payment into memory to compute the same numbers.

Rule: Include when you will modify the entities. Projection when you will display them.

Explicit loading

Student student = await _context.Students
    .FirstAsync(s => s.SchoolId == schoolId && s.PublicId == publicId, ct);

// Load a collection on demand
await _context.Entry(student)
    .Collection(s => s.ExamResults)
    .Query()
    .Where(r => r.Exam.ExamDate >= cutoff)
    .Include(r => r.Exam)
    .LoadAsync(ct);

// Load a reference
await _context.Entry(student)
    .Reference(s => s.School)
    .LoadAsync(ct);

// Count without loading
int resultCount = await _context.Entry(student)
    .Collection(s => s.ExamResults)
    .Query()
    .CountAsync(ct);

Useful when whether you need the related data depends on something you only know after loading the parent. .Query() before .LoadAsync() lets you filter, which avoids loading a whole collection to use part of it.

Lazy loading

Right-click the project → Manage NuGet Packages, or in the Package Manager Console:

Install-Package Microsoft.EntityFrameworkCore.Proxies

options.UseLazyLoadingProxies().UseSqlServer(connectionString);

public virtual ICollection<ExamResult> ExamResults { get; set; } = new List<ExamResult>();

Navigations must be virtual. Accessing one now issues a query automatically.

This is almost always a mistake.

List<Student> students = await _context.Students
    .Where(s => s.SchoolId == schoolId)
    .ToListAsync(ct);                        // 1 query

foreach (Student student in students)
{
    Console.WriteLine(student.ExamResults.Count);   // 1 query EACH
}

400 students, 401 queries. The C# contains no visible query at all — that is exactly why it is dangerous. The application is fast in development with 20 rows and unusable in production, and the cause is invisible in code review.

Two further problems: it fails outside the DbContext's lifetime (serialising an entity after the context is disposed throws), and a JSON serialiser walking navigations triggers a cascade of queries and often an infinite loop.

Do not enable lazy loading. Use Include or projection, and make every query visible.

Diagnosing N+1

options.LogTo(Console.WriteLine, new[] { DbLoggerCategory.Database.Command.Name },
              LogLevel.Information);

Run the operation and count the statements. One per row is N+1.

Three causes:

• Lazy loading — disable it
• A query inside a loop — restructure to one query, or two plus an in-memory join
• An unloaded navigation accessed after AsNoTracking — it is null, and the null-reference is the symptom

Errors you will hit

Message	Cause	Fix
A navigation property is null	Not loaded — EF Core does not load it for you	Include it
System.NullReferenceException on a navigation property	Same cause	Same fix
The query fans out into hundreds	Lazy loading inside a loop	Include, or project
Include returns duplicated parents	One row per child — expected	AsSplitQuery(), or group in code
The property is not a navigation property	Include on a scalar	Include takes navigations only

EF Core never loads a related entity unless you ask. A null navigation property almost always means a missing Include, not missing data.

Common mistakes

• Leaving DeleteBehavior.Cascade on entities with history
• No explicit foreign key property, forcing an extra load to set a relationship
• Assuming = null! means the navigation is populated
• Two collection Includes with no AsSplitQuery
• Include where a projection was needed, fetching every column
• Treating a filtered include's collection as complete
• Enabling lazy loading
• Serialising an entity graph and triggering cascading queries
• A query inside a loop
• Never counting the statements a page issues

Practice

The course exercise is load related data.

1. Model Student → ExamResult → Exam → Subject with explicit foreign keys and Restrict on delete.
2. Query results with Include and ThenInclude. Read the generated SQL and count the joins.
3. Load students with two collection Includes over seeded data. Log the row count returned for 10 students.
4. Add AsSplitQuery and compare statement count and total rows.
5. Write the same report as a projection. Compare the SQL, the columns fetched, and whether anything is tracked.
6. Use a filtered include for the current academic year. Then access the collection elsewhere as if it were complete, and describe the bug that creates.
7. Write a projection computing ResultCount and AverageMarks in SQL. Confirm from the log that no results were loaded.
8. Load one student and explicitly load their recent results with .Query().Where(...).
9. Enable lazy loading with virtual navigations. Loop 400 students accessing ExamResults.Count and count the queries.
10. Disable it and fix the same loop with a projection. Compare.
11. Serialise a student with a loaded ExamResults collection to JSON. Observe the cycle, then fix it with a DTO.

Then run the course debugging exercise — investigate missing related data. Given code where student.School.Name throws a null-reference, work through the causes: the navigation was never included, AsNoTracking was used with no include, a filtered include excluded it, or a global query filter removed it.

Exercise 9 is the one to see once. Four hundred queries from code containing no query is the clearest argument against lazy loading there is.

You can now

• Model one-to-many and many-to-many relationships
• Load related data with Include and ThenInclude
• Choose between eager, explicit and lazy loading
• Recognise cartesian explosion and use AsSplitQuery
• Say why a navigation property came back null

Review questions

1. Why is DeleteBehavior.Cascade the wrong default for exam results?
2. What is cartesian explosion, and when does AsSplitQuery not help?
3. When is projection better than Include, and why?
4. Why is lazy loading dangerous even though the code looks clean?

Next: Change tracking