EF Core Relationships: Loading Related Data – Data Access | Lesson 60

Published / updated7 Oct 2026

Introduction

A student has enrollments, an enrollment has a section, and a section belongs to a class. In SQL, you follow these links with joins (Lesson 17). In EF Core, you follow them with navigation properties: enrollment.Section.SchoolClass.Name.

This lesson maps the relationships between the SMS tables, and shows the different ways to load related data. It also shows the mistake behind the hook: related data that was never loaded looks exactly like data that does not exist.

By the end of this lesson, you will be able to:

Map one-to-many relationships with HasOne, HasMany, WithOne, and WithMany.
Load related data with Include and ThenInclude, including filtered includes.
Use explicit loading, and explain why lazy loading is risky.
Follow navigations in projections without any Include.
Hide soft-deleted rows with a global query filter, and save to tables that have triggers.

Why this matters

Almost every SMS screen shows related data: a report card needs marks, papers, subjects, and an exam; a receipt needs a payment, an invoice, an enrollment, and a student. Loading these well is the difference between one query and hundreds.

Loading them wrongly is worse than slow. In this lesson's first step, an invoice loaded without its payments reports the wrong balance, and nothing warns you.

The concept

A navigation property is a property that points to another entity, instead of holding a value. There are two kinds:

Kind	Example in SchoolApp	Points to
Reference navigation	`Enrollment.Section`, `FeePayment.FeeInvoice`	One related entity (or `null`)
Collection navigation	`Student.Enrollments`, `FeeInvoice.Payments`	A list of related entities

Each relationship also has a foreign key property, such as Enrollment.SectionId. The foreign key is always loaded; the navigation is loaded only when you ask for it.

Example: relationship types

Relationship	SMS example	Mapped with
One-to-many	One invoice has many payments	`HasMany(i => i.Payments).WithOne(p => p.FeeInvoice)`
Many-to-one	Many sections belong to one class	`HasOne(s => s.SchoolClass).WithMany()`
Many-to-many	Students and guardians, through `StudentGuardians`	A join entity with two one-to-many relationships
One-to-one	A user and the teacher it belongs to	`HasOne(...).WithOne(...)`

WithMany() with nothing inside means the other side has no collection property. SchoolClass does not need a list of its sections, so it does not have one.

Example: four ways to load related data

Think of fetching a student's file from the school office:

Strategy	Like asking the office for	How	SQL queries
Eager loading	"The file, with the fee folder inside"	`Include`, `ThenInclude`	One, with joins
Explicit loading	"The file now; the fee folder later, if I need it"	`Entry(...).Collection(...).LoadAsync()`	One more per load
Lazy loading	"Bring each folder the moment I open the file's tab"	Proxies, switched on separately	One per navigation touched
Projection	"Just copy these five lines onto a sheet"	`Select` across navigations	One, with joins

SchoolApp never switches on lazy loading. It hides a query behind every property access, which turns a loop over 40 students into 40 queries without one visible line of database code.

Apply it to the SMS

Step 1: the invoice keeps its payments

Until now, FeeInvoice kept the amount paid in a private field. That only worked in memory: an invoice read from SchoolDb started at zero, whatever the real payments were.

Now the invoice has a Payments collection, and AmountPaid adds up the payments that are not deleted. RecordPayment adds a FeePayment to the list, so EF Core can save both together (Lesson 64).

Show src/SchoolApp.Core/Entities/FeeInvoice.cs

src/SchoolApp.Core/Entities/FeeInvoice.cs
using SchoolApp.Core.Enums;
using SchoolApp.Core.Exceptions;

namespace SchoolApp.Core.Entities;

// Lesson 38: an invoice that protects its own rules (encapsulation).
// Lesson 43: Status is an InvoiceStatus enum instead of a string.
// Lesson 44: every column of the FeeInvoices table, plus the audit columns.
// Lesson 60: the invoice now holds its payments, and the amount paid is worked out from them,
// so an invoice loaded from SchoolDb together with its payments knows its real balance.
public class FeeInvoice : AuditableEntity
{
    public int Id { get; set; }
    public string InvoiceNumber { get; init; }  // init: can be set only while the object is created
    public int EnrollmentId { get; set; }
    public DateOnly InvoiceDate { get; set; }
    public DateOnly DueDate { get; set; }
    public decimal TotalAmount { get; }         // no setter: fixed by the constructor
    public decimal DiscountAmount { get; }
    public InvoiceStatus Status { get; private set; } = InvoiceStatus.Unpaid;  // only this class can write

    public Enrollment? Enrollment { get; set; }  // navigation property (Lesson 44)

    // Lesson 60: a collection navigation property: every payment made against this invoice.
    public List<FeePayment> Payments { get; } = new List<FeePayment>();

    public FeeInvoice(string invoiceNumber, decimal totalAmount, decimal discountAmount = 0m)
    {
        if (totalAmount < 0)
        {
            throw new ArgumentOutOfRangeException(nameof(totalAmount), "The total cannot be negative.");
        }
        if (discountAmount < 0 || discountAmount > totalAmount)
        {
            throw new ArgumentOutOfRangeException(nameof(discountAmount), "The discount must be between 0 and the total.");
        }

        InvoiceNumber = invoiceNumber;
        TotalAmount = totalAmount;
        DiscountAmount = discountAmount;
    }

    // Deleted (refunded) payments do not count, the same rule as usp_RecordFeePayment.
    public decimal AmountPaid => Payments.Where(p => !p.IsDeleted).Sum(p => p.Amount);
    public decimal Balance => TotalAmount - DiscountAmount - AmountPaid;

    // Lesson 51: an event. Other code subscribes with +=; the invoice raises it after each payment,
    // without knowing who is listening.
    public event EventHandler<PaymentRecordedEventArgs>? PaymentRecorded;

    // The only way to pay. The same rules and error numbers as usp_RecordFeePayment (Lesson 22).
    // Lesson 60: the payment is added to Payments; the receipt number comes from the caller.
    public FeePayment RecordPayment(decimal amount, PaymentMode paymentMode, string receiptNumber)
    {
        if (Status == InvoiceStatus.Cancelled)
        {
            throw new SchoolRuleException(50011, "Invoice is cancelled.");
        }
        if (amount <= 0)
        {
            throw new SchoolRuleException(50012, "Amount must be more than zero.");
        }
        if (amount > Balance)
        {
            throw new SchoolRuleException(50013, "Amount is more than the balance due.");
        }

        FeePayment payment = new FeePayment
        {
            ReceiptNumber = receiptNumber,
            FeeInvoiceId = Id,
            Amount = amount,
            PaidOn = DateTime.UtcNow,
            PaymentMode = paymentMode
        };
        Payments.Add(payment);
        Status = Balance == 0 ? InvoiceStatus.Paid : InvoiceStatus.PartiallyPaid;

        // ?.Invoke: raise the event only if someone has subscribed.
        PaymentRecorded?.Invoke(this, new PaymentRecordedEventArgs(InvoiceNumber, amount, Balance, Status));
        return payment;
    }

    // An invoice with payments on it cannot be cancelled; the payments must be refunded first.
    // Lesson 60: Status is checked too, so the rule holds even if Payments was not loaded.
    public void Cancel()
    {
        if (AmountPaid > 0 || Status is InvoiceStatus.PartiallyPaid or InvoiceStatus.Paid)
        {
            throw new InvalidOperationException("An invoice with payments cannot be cancelled.");
        }
        Status = InvoiceStatus.Cancelled;
    }
}

Cancel also checks Status now, so the "no cancelling paid invoices" rule holds even when the payments were not loaded.

Step 2: map the relationships

src/SchoolApp.Data/EfCore/Configurations/FeeConfigurations.cs
using Microsoft.EntityFrameworkCore;
using Microsoft.EntityFrameworkCore.Metadata.Builders;
using SchoolApp.Core.Entities;

namespace SchoolApp.Data.EfCore.Configurations;

// Lesson 60: dbo.FeeInvoices and dbo.FeePayments: one invoice has many payments.
public class FeeInvoiceConfiguration : IEntityTypeConfiguration<FeeInvoice>
{
    public void Configure(EntityTypeBuilder<FeeInvoice> builder)
    {
        builder.ToTable("FeeInvoices");
        builder.Property(i => i.InvoiceNumber).HasMaxLength(20).IsUnicode(false);

        // No setters: mapped because they are named here, filled through the constructor.
        builder.Property(i => i.TotalAmount).HasPrecision(10, 2);
        builder.Property(i => i.DiscountAmount).HasPrecision(10, 2);
        builder.Property(i => i.Status).HasMaxLength(13).IsUnicode(false).HasConversion<string>();

        builder.HasOne(i => i.Enrollment).WithMany().HasForeignKey(i => i.EnrollmentId);

        // One-to-many: the invoice's Payments list and each payment's FeeInvoice point at each other.
        builder.HasMany(i => i.Payments).WithOne(p => p.FeeInvoice).HasForeignKey(p => p.FeeInvoiceId);

        builder.HasAuditColumns();
    }
}

public class FeePaymentConfiguration : IEntityTypeConfiguration<FeePayment>
{
    public void Configure(EntityTypeBuilder<FeePayment> builder)
    {
        builder.ToTable("FeePayments");
        builder.Property(p => p.ReceiptNumber).HasMaxLength(20).IsUnicode(false);
        builder.Property(p => p.Amount).HasPrecision(10, 2);
        builder.Property(p => p.PaidOn).HasPrecision(0);
        builder.Property(p => p.PaymentMode).HasMaxLength(12).IsUnicode(false).HasConversion<string>();
        builder.Property(p => p.TransactionReference).HasMaxLength(50).IsUnicode(false);

        builder.HasAuditColumns();
        builder.Property(p => p.DeletedAt).HasPrecision(0);

        // A global query filter: every query on FeePayments gets "IsDeleted = 0" added.
        builder.HasQueryFilter(SoftDeleteFilter.Name, p => !p.IsDeleted);
    }
}

The one-to-many relationship is described once, from the invoice's side: HasMany(i => i.Payments).WithOne(p => p.FeeInvoice).HasForeignKey(p => p.FeeInvoiceId). EF Core now knows both navigations and the foreign key that links them.

Show the exam configurations and the DbContext

src/SchoolApp.Data/EfCore/Configurations/ExamConfigurations.cs
using Microsoft.EntityFrameworkCore;
using Microsoft.EntityFrameworkCore.Metadata.Builders;
using SchoolApp.Core.Entities;

namespace SchoolApp.Data.EfCore.Configurations;

// Lesson 60: the exam tables from Lesson 13. A mark belongs to one exam paper (ExamSubject),
// which belongs to one exam and one subject.
public class SubjectConfiguration : IEntityTypeConfiguration<Subject>
{
    public void Configure(EntityTypeBuilder<Subject> builder)
    {
        builder.ToTable("Subjects");
        builder.Property(s => s.Code).HasMaxLength(10).IsUnicode(false);
        builder.Property(s => s.Name).HasMaxLength(50);
    }
}

public class ExamConfiguration : IEntityTypeConfiguration<Exam>
{
    public void Configure(EntityTypeBuilder<Exam> builder)
    {
        builder.ToTable("Exams");
        builder.Property(e => e.Name).HasMaxLength(50);
        builder.Property(e => e.ExamType).HasMaxLength(10).IsUnicode(false).HasConversion<string>();
        builder.HasAuditColumns();
    }
}

public class ExamSubjectConfiguration : IEntityTypeConfiguration<ExamSubject>
{
    public void Configure(EntityTypeBuilder<ExamSubject> builder)
    {
        builder.ToTable("ExamSubjects");
        builder.Property(es => es.MaxMarks).HasPrecision(5, 2);
        builder.Property(es => es.PassMarks).HasPrecision(5, 2);
        builder.HasOne(es => es.Exam).WithMany().HasForeignKey(es => es.ExamId);
        builder.HasOne(es => es.Subject).WithMany().HasForeignKey(es => es.SubjectId);
        builder.HasAuditColumns();
    }
}

public class MarkConfiguration : IEntityTypeConfiguration<Mark>
{
    public void Configure(EntityTypeBuilder<Mark> builder)
    {
        // Marks has two triggers (Lesson 23). EF Core must know, because it cannot use its faster
        // OUTPUT clause on a table with triggers; without this, saving a mark fails.
        builder.ToTable("Marks", table =>
        {
            table.HasTrigger("tr_Marks_ValidateMaxMarks");
            table.HasTrigger("tr_Marks_Audit");
        });
        builder.Property(m => m.MarksObtained).HasPrecision(5, 2);
        builder.Property(m => m.Remarks).HasMaxLength(200);
        builder.HasOne(m => m.ExamSubject).WithMany().HasForeignKey(m => m.ExamSubjectId);
        builder.HasOne(m => m.Enrollment).WithMany().HasForeignKey(m => m.EnrollmentId);
        builder.HasAuditColumns();
    }
}

src/SchoolApp.Data/EfCore/SchoolDbContext.cs
using Microsoft.EntityFrameworkCore;
using SchoolApp.Core.Entities;

namespace SchoolApp.Data.EfCore;

// Lesson 59: EF Core's view of SchoolDb. Each DbSet is a table you can query with LINQ.
// The entity classes are our own, from SchoolApp.Core; the Configurations folder maps them to tables.
public class SchoolDbContext : DbContext
{
    public SchoolDbContext(DbContextOptions<SchoolDbContext> options) : base(options)
    {
    }

    public DbSet<Student> Students => Set<Student>();
    public DbSet<AcademicYear> AcademicYears => Set<AcademicYear>();
    public DbSet<SchoolClass> Classes => Set<SchoolClass>();
    public DbSet<Section> Sections => Set<Section>();
    public DbSet<Enrollment> Enrollments => Set<Enrollment>();

    // Lesson 60: exams, marks, and fees, with their relationships.
    public DbSet<Subject> Subjects => Set<Subject>();
    public DbSet<Exam> Exams => Set<Exam>();
    public DbSet<ExamSubject> ExamSubjects => Set<ExamSubject>();
    public DbSet<Mark> Marks => Set<Mark>();
    public DbSet<FeeInvoice> FeeInvoices => Set<FeeInvoice>();
    public DbSet<FeePayment> FeePayments => Set<FeePayment>();

    protected override void OnModelCreating(ModelBuilder modelBuilder)
    {
        // Finds every IEntityTypeConfiguration class in this project and applies it.
        modelBuilder.ApplyConfigurationsFromAssembly(typeof(SchoolDbContext).Assembly);
    }
}

Step 3: the program

Show src/SchoolApp.Console/Program.cs

src/SchoolApp.Console/Program.cs
// Lesson 60: EF Core relationships: loading related data, query filters, and tables with triggers.
using Microsoft.EntityFrameworkCore;
using Microsoft.EntityFrameworkCore.Storage;
using SchoolApp.Core.Entities;
using SchoolApp.Core.Reports;
using SchoolApp.Data;
using SchoolApp.Data.EfCore;
using SchoolApp.Data.EfCore.Configurations;

SqlConnectionFactory connections = SqlConnectionFactory.FromEnvironment();
int queries = 0;  // counts every SQL command EF Core sends

// A new, short-lived DbContext for each step, as a web request would have (Lesson 59).
SchoolDbContext NewContext() => SchoolDbContextFactory.Create(connections, _ => queries++);

// 1. Related data is not loaded unless you ask: Diya's invoice, without and with its payments.
await using (SchoolDbContext db = NewContext())
{
    FeeInvoice invoice = await db.FeeInvoices.SingleAsync(i => i.InvoiceNumber == "INV-2026-000002");
    Console.WriteLine($"Without Include: {invoice.Payments.Count} payments, balance {invoice.Balance}  <- wrong");
}
await using (SchoolDbContext db = NewContext())
{
    FeeInvoice invoice = await db.FeeInvoices
        .Include(i => i.Payments)
        .SingleAsync(i => i.InvoiceNumber == "INV-2026-000002");
    Console.WriteLine($"With Include:    {invoice.Payments.Count} payment,  balance {invoice.Balance}, paid {invoice.AmountPaid}");
}

// 2. Several levels with ThenInclude, and a filtered Include that sorts the enrollments.
await using (SchoolDbContext db = NewContext())
{
    queries = 0;
    Student kabir = await db.Students
        .Include(s => s.Enrollments.OrderBy(e => e.EnrolledOn))
            .ThenInclude(e => e.Section!)
            .ThenInclude(section => section.SchoolClass)
        .Include(s => s.Enrollments)
            .ThenInclude(e => e.AcademicYear)
        .SingleAsync(s => s.AdmissionNumber == "ADM-2017-0003");

    foreach (Enrollment e in kabir.Enrollments)
    {
        Console.WriteLine($"{kabir.FullName}, {e.AcademicYear!.Name}: {e.Section!.SchoolClass!.Name}-{e.Section.Name}, roll {e.RollNumber}");
    }
    Console.WriteLine($"  ({queries} query for the student, enrollments, sections, classes, and years)");
}

// 3. A report card: each mark with its exam paper, the paper's subject, and the exam.
await using (SchoolDbContext db = NewContext())
{
    List<Mark> marks = await db.Marks
        .Include(m => m.ExamSubject!).ThenInclude(es => es.Subject)
        .Include(m => m.ExamSubject!).ThenInclude(es => es.Exam)
        .Where(m => m.EnrollmentId == 16 && m.ExamSubject!.Exam!.Name == "Unit Test 1")  // Meera, 2026-27
        .OrderBy(m => m.ExamSubject!.Subject!.Name)
        .ToListAsync();

    ReportCard card = new SecondaryReportCard("Meera Iyer", "Class 10-A");
    Console.WriteLine($"{card.Heading}, {marks[0].ExamSubject!.Exam!.Name}");
    foreach (Mark mark in marks)
    {
        Console.WriteLine(card.FormatLine(mark.ExamSubject!.Subject!.Name, mark.MarksObtained, mark.ExamSubject.MaxMarks));
    }
}

// 4. Explicit loading: load the invoice first, then its payments only when needed.
await using (SchoolDbContext db = NewContext())
{
    queries = 0;
    FeeInvoice invoice = await db.FeeInvoices.SingleAsync(i => i.InvoiceNumber == "INV-2026-000001");
    await db.Entry(invoice).Collection(i => i.Payments).LoadAsync();
    Console.WriteLine($"Explicit loading: {invoice.InvoiceNumber} is {invoice.Status}, paid {invoice.AmountPaid} ({queries} queries)");
}

// 5. A projection follows navigations with no Include: EF Core writes the joins itself.
await using (SchoolDbContext db = NewContext())
{
    var receipts = await db.FeePayments
        .OrderBy(p => p.ReceiptNumber)
        .Select(p => new
        {
            p.ReceiptNumber,
            p.Amount,
            Student = p.FeeInvoice!.Enrollment!.Student!.FirstName + " " + p.FeeInvoice.Enrollment.Student.LastName
        })
        .ToListAsync();
    foreach (var receipt in receipts)
    {
        Console.WriteLine($"Receipt {receipt.ReceiptNumber}: {receipt.Amount} from {receipt.Student}");
    }
}

// 6. The soft-delete query filter, and a mark changed on a table with triggers. Rolled back.
await using (SchoolDbContext db = NewContext())
{
    await using IDbContextTransaction transaction = await db.Database.BeginTransactionAsync();

    Student kabir = await db.Students.SingleAsync(s => s.AdmissionNumber == "ADM-2017-0003");
    kabir.SoftDelete(DateTime.UtcNow);
    await db.SaveChangesAsync();
    Console.WriteLine($"After soft-deleting Kabir: {await db.Students.CountAsync()} students; " +
                      $"{await db.Students.IgnoreQueryFilters([SoftDeleteFilter.Name]).CountAsync()} with the filter off");

    Mark meeraMaths = await db.Marks.SingleAsync(m => m.EnrollmentId == 16 && m.ExamSubjectId == 8);
    meeraMaths.MarksObtained = 96m;
    await db.SaveChangesAsync();

    // tr_Marks_Audit (Lesson 23) wrote the change to AuditLogs. SqlQuery<int> reads one column named Value.
    int auditRows = await db.Database
        .SqlQuery<int>($"SELECT COUNT(*) AS Value FROM dbo.AuditLogs WHERE TableName = 'Marks' AND RecordId = {meeraMaths.Id}")
        .SingleAsync();
    Console.WriteLine($"Meera's Maths mark saved as {meeraMaths.MarksObtained}; audit rows written by the trigger: {auditRows}");

    await transaction.RollbackAsync();
}

Output
Without Include: 0 payments, balance 45500.00  <- wrong
With Include:    1 payment,  balance 25500.00, paid 20000.00
Kabir Singh, 2025-26: Class 9-A, roll 3
Kabir Singh, 2026-27: Class 10-A, roll 3
  (1 query for the student, enrollments, sections, classes, and years)
Report card: Meera Iyer, Class 10-A, Unit Test 1
  Mathematics      95 / 100
  Science          Absent
Explicit loading: INV-2026-000001 is Paid, paid 45500.00 (2 queries)
Receipt RCP-2026-000001: 45500.00 from Aarav Sharma
Receipt RCP-2026-000002: 20000.00 from Diya Patel
Receipt RCP-2026-000003: 39500.00 from Advika Sharma
After soft-deleting Kabir: 10011 students; 10012 with the filter off
Meera's Maths mark saved as 96; audit rows written by the trigger: 1

Example: the missing Include

The first query loaded Diya's invoice without Include. Payments was an empty list, not null, so nothing failed. AmountPaid was 0, and the balance said 45,500.

With .Include(i => i.Payments), the same invoice had its one payment of 20,000, and the balance was the real 25,500. This bug is common, and it is quiet: always ask, "did I load what this code reads?"

Example: Include and ThenInclude

Include loads a navigation of the entity you query; ThenInclude continues from there. Kabir's query loads his enrollments, then each enrollment's section, then each section's class. A second Include starts again from the student to add the academic year.

Include(s => s.Enrollments.OrderBy(e => e.EnrolledOn)) is a filtered include: it can sort or filter the related rows, so 2025-26 prints before 2026-27. All of it arrived in one SQL query.

Example: a report card from three tables

The report card query includes each mark's exam paper, the paper's subject, and the exam. The Where reaches through navigations too: m.ExamSubject!.Exam!.Name == "Unit Test 1" became a join and a condition in SQL.

SecondaryReportCard is the class from Lesson 41. Entities loaded by EF Core are ordinary objects, so every class from Module 7 works with them. Meera's absent Science paper prints as "Absent", because MarksObtained is null.

Example: both sides are connected

When EF Core loads Diya's payment through Include, it also sets the payment's FeeInvoice property back to the same invoice object. This is called relationship fix-up: every loaded navigation is connected in both directions, with no extra query.

It is convenient, but it creates a loop: invoice to payment to invoice. Module 10 returns DTOs from the API instead of entities, partly so that such loops never reach the JSON serializer.

Example: explicit loading

db.Entry(invoice).Collection(i => i.Payments).LoadAsync() loads the payments of an invoice that is already in memory. It used 2 queries: one for the invoice, one for its payments.

That suits rare cases, such as loading payments only when a clerk opens the "payments" tab. Inside a loop, though, it means one query per row: the N+1 problem, which Lesson 61 measures.

The receipts query selects the receipt number, the amount, and the student's name, three links away. There is no Include: in a Select, EF Core sees which navigations you use and writes the joins.

Projections also load only the columns you name, which is why Lesson 61 makes them the default for read-only screens.

Example: a global query filter for soft delete

Soft-deleted rows (Lesson 14) should not appear anywhere. Instead of writing !s.IsDeleted in every query, the configuration adds a global query filter: HasQueryFilter(SoftDeleteFilter.Name, s => !s.IsDeleted). EF Core adds it to every query on that entity, including through Include.

After soft-deleting Kabir, a plain count found 10,011 students. IgnoreQueryFilters([SoftDeleteFilter.Name]) switched off that one filter and found 10,012. Naming filters is new in EF Core 10; with names, a query can switch off one filter and keep the others.

Example: tables with triggers

Marks has two triggers from Lesson 23. EF Core normally reads results back with an OUTPUT clause, which SQL Server does not allow on tables with triggers. Without HasTrigger, saving a mark failed:

Output without HasTrigger
Microsoft.EntityFrameworkCore.DbUpdateException: Could not save changes because the target table has database triggers.
 ---> Microsoft.Data.SqlClient.SqlException (0x80131904): The target table 'Marks' of the DML statement cannot have any enabled triggers if the statement contains an OUTPUT clause without INTO clause.

Declaring the triggers in ToTable("Marks", table => table.HasTrigger(...)) makes EF Core use a slightly slower method that works. The audit trigger then wrote its row, as the last output line shows.

Common mistakes

Reading a navigation that was never loaded. It is null or empty, and totals come out wrong. Fix: Include what the code reads, or project what the screen needs.
Switching on lazy loading to "fix" missing data. Each property access becomes a hidden query. Fix: load deliberately with Include or a projection.
Including everything "just in case". Large joins move data nobody uses. Fix: include only what this use case reads.
Repeating !IsDeleted in every query. One forgotten copy shows deleted rows. Fix: a global query filter.
Saving to a table with triggers without HasTrigger. Saves fail at run time. Fix: declare every trigger in the configuration.

In real projects

From 16 years of .NET projects

A fee reminder job once sent "you owe the full amount" messages to parents who had already paid part of their fees. The code was correct; the query was not.

Someone had removed an Include of the payments to "make it faster". The invoices loaded fine, the payment lists were empty, and every balance looked unpaid.

We fixed it with a projection that summed the payments in SQL, and added a test that checks a partly paid invoice's balance. Missing data never throws, so only a test catches it.

Best practices

Map every relationship explicitly when the foreign key name is not the default.
Load with Include for entities you will change; use projections for screens that only read.
Keep lazy loading off.
Put soft-delete rules in a named global query filter.
Declare every database trigger with HasTrigger.

Recap

Navigation properties link entities; foreign keys are always loaded, navigations only on request.
Include and ThenInclude load related data in one query; filtered includes can sort and filter.
Explicit loading fetches a navigation later; lazy loading hides queries and is best left off.
Projections follow navigations with no Include.
Global query filters apply rules such as soft delete to every query.
Tables with triggers need HasTrigger, or saves fail.

Homework

Library module task: Map BookIssues with navigations to its copy, the copy's book, and the student. List every issue with Include and ThenInclude.

Then return The Jungle Book with a fine of 24, and show that the trigger from your Lesson 23 homework marked the copy Available. Roll it back.

A correct result looks like:

Output
Wings of Fire copy 2 to Aarav Sharma: returned 2026-07-14
The Jungle Book copy 1 to Advika Sharma: not returned
Before return: copy 3 is Issued
After return:  copy 3 is Available, fine 24

Show the solution

library-assignment/060-library-relationships.cs
// Library homework, Lesson 60: book issues with their copy, book, and student, using Include and
// ThenInclude, and returning a book on a table that has a trigger.
// Run with:  dotnet run library-assignment/060-library-relationships.cs
#:project ../src/SchoolApp.Data/SchoolApp.Data.csproj
#:property PublishAot=false
using Microsoft.EntityFrameworkCore;
using Microsoft.EntityFrameworkCore.Storage;
using SchoolApp.Data;

DbContextOptions<LibraryDbContext> options = new DbContextOptionsBuilder<LibraryDbContext>()
    .UseSqlServer(SqlConnectionFactory.FromEnvironment().ConnectionString)
    .Options;

// 1. Every issue with its copy, the copy's book, and the student: three navigations, one query.
await using (LibraryDbContext db = new LibraryDbContext(options))
{
    List<BookIssue> issues = await db.BookIssues
        .Include(i => i.Copy!).ThenInclude(c => c.Book)
        .Include(i => i.Student)
        .OrderBy(i => i.IssuedOn)
        .ToListAsync();

    foreach (BookIssue issue in issues)
    {
        string returned = issue.ReturnedOn is null ? "not returned" : $"returned {issue.ReturnedOn:yyyy-MM-dd}";
        Console.WriteLine($"{issue.Copy!.Book!.Title} copy {issue.Copy.CopyNumber} to " +
                          $"{issue.Student!.FirstName} {issue.Student.LastName}: {returned}");
    }
}

// 2. Return The Jungle Book. The trigger on BookIssues marks the copy Available. Rolled back.
await using (LibraryDbContext db = new LibraryDbContext(options))
{
    await using IDbContextTransaction transaction = await db.Database.BeginTransactionAsync();

    BookIssue open = await db.BookIssues.Include(i => i.Copy).SingleAsync(i => i.ReturnedOn == null);
    Console.WriteLine($"Before return: copy {open.BookCopyId} is {open.Copy!.Status}");

    open.ReturnedOn = new DateOnly(2026, 8, 1);
    open.FineAmount = 24m;   // 12 days late, 2 per day
    await db.SaveChangesAsync();

    await db.Entry(open.Copy).ReloadAsync();   // read the copy again: the trigger changed it
    Console.WriteLine($"After return:  copy {open.BookCopyId} is {open.Copy.Status}, fine {open.FineAmount}");

    await transaction.RollbackAsync();
}

class Book
{
    public int Id { get; set; }
    public string Title { get; set; } = "";
}

class BookCopy
{
    public int Id { get; set; }
    public int BookId { get; set; }
    public short CopyNumber { get; set; }
    public string Status { get; set; } = "";
    public Book? Book { get; set; }
}

class Borrower
{
    public int Id { get; set; }
    public string FirstName { get; set; } = "";
    public string LastName { get; set; } = "";
}

class BookIssue
{
    public int Id { get; set; }
    public int BookCopyId { get; set; }
    public int StudentId { get; set; }
    public DateOnly IssuedOn { get; set; }
    public DateOnly DueOn { get; set; }
    public DateOnly? ReturnedOn { get; set; }
    public decimal FineAmount { get; set; }
    public BookCopy? Copy { get; set; }
    public Borrower? Student { get; set; }
}

class LibraryDbContext : DbContext
{
    public LibraryDbContext(DbContextOptions<LibraryDbContext> options) : base(options)
    {
    }

    public DbSet<BookIssue> BookIssues => Set<BookIssue>();

    protected override void OnModelCreating(ModelBuilder modelBuilder)
    {
        modelBuilder.Entity<Book>().ToTable("Books");
        modelBuilder.Entity<BookCopy>(copy =>
        {
            copy.ToTable("BookCopies");
            copy.Property(c => c.Status).HasMaxLength(9).IsUnicode(false);
            copy.HasOne(c => c.Book).WithMany().HasForeignKey(c => c.BookId);
        });
        modelBuilder.Entity<Borrower>().ToTable("Students");  // only the columns the class has
        modelBuilder.Entity<BookIssue>(issue =>
        {
            // The trigger keeps BookCopies.Status in step (Lesson 23 homework).
            issue.ToTable("BookIssues", table => table.HasTrigger("tr_BookIssues_SyncCopyStatus"));
            issue.Property(i => i.FineAmount).HasPrecision(8, 2);
            issue.HasOne(i => i.Copy).WithMany().HasForeignKey(i => i.BookCopyId);
            issue.HasOne(i => i.Student).WithMany().HasForeignKey(i => i.StudentId);
        });
    }
}

ReloadAsync reads the copy again from the database. EF Core cannot know what a trigger changed, so without it, the copy in memory would still say Issued.

Quiz

Diya's invoice was loaded without Include. What is in invoice.Payments?
1. Her one payment
2. An empty list
3. An exception is thrown
4. null, and AmountPaid throws
Show answer
Answer: B. An empty list Nothing fails, which is why the wrong balance of 45,500 went unnoticed.
Which call loads each enrollment's section after Include(s => s.Enrollments)?
1. Include(e => e.Section)
2. ThenInclude(e => e.Section)
3. Load(e => e.Section)
4. Join(e => e.Section)
Show answer
Answer: B. ThenInclude(e => e.Section) ThenInclude continues from the navigation that was just included.
Why does SchoolApp keep lazy loading off?
1. It is not supported in EF Core 10
2. It hides a query behind every property access
3. It cannot load collections
4. It needs stored procedures
Show answer
Answer: B. It hides a query behind every property access A loop over 40 students can quietly become 40 queries.
What does a global query filter do?
1. Deletes rows
2. Adds a condition to every query on that entity
3. Filters columns
4. Runs only in Include
Show answer
Answer: B. Adds a condition to every query on that entity The soft-delete filter adds IsDeleted = 0 everywhere.
Saving a mark fails with an error about OUTPUT and triggers. What is the fix?
1. Drop the triggers
2. Declare them with HasTrigger in the configuration
3. Use AsNoTracking
4. Call SaveChanges twice
Show answer
Answer: B. Declare them with HasTrigger in the configuration EF Core then saves without the OUTPUT clause.

Interview questions

Questions are grouped by experience level. Click a question to see a model answer. All questions: Data Access interview questions.

Fresher (0-2 yrs)

F What is a navigation property in EF Core? concept

A property that points to a related entity instead of holding a value. A reference navigation points to one entity, such as Enrollment.Section; a collection navigation holds many, such as FeeInvoice.Payments. The foreign key, such as SectionId, is always loaded; navigations only when requested.

See the example · Permalink

F What do Include and ThenInclude do? coding

Include loads a navigation of the entity being queried; ThenInclude continues from that navigation to the next level. Kabir's student, enrollments, sections, classes, and years came back in one query with Include(s => s.Enrollments).ThenInclude(e => e.Section).ThenInclude(...).

See the example · Permalink

F What is the difference between eager, explicit, and lazy loading? concept

Eager loading fetches related data in the same query with Include. Explicit loading fetches it later, on request, with Entry(...).Collection(...).LoadAsync(). Lazy loading fetches it automatically the first time a navigation is read, through proxies, which hides queries and often causes N+1 problems.

See the example · Permalink

Mid (3-5 yrs)

M How do you configure a one-to-many relationship with the Fluent API? coding

From either side, for example builder.HasMany(i => i.Payments).WithOne(p => p.FeeInvoice) .HasForeignKey(p => p.FeeInvoiceId). Use WithMany() with no argument when the other side has no collection. Configuring it explicitly is needed when names do not follow conventions, such as ClassId with a SchoolClass navigation.

See the example · Permalink

M What is a global query filter, and when would you use one? concept

A condition EF Core adds to every query on an entity, configured with HasQueryFilter. It suits rules that must never be forgotten, such as hiding soft-deleted rows or, in multi-tenant systems, other tenants' rows. IgnoreQueryFilters switches it off; EF Core 10 can switch off one named filter.

See the example · Permalink

M Why might SaveChanges fail on a table with triggers, and how do you fix it? concept

EF Core reads generated values back with an OUTPUT clause, and SQL Server rejects OUTPUT without INTO on tables with enabled triggers. Declare each trigger with ToTable("Marks", t => t.HasTrigger("tr_Marks_Audit")), and EF Core switches to a method that works.

See the example · Permalink

Senior (6-9 yrs)

S A fee report shows full balances for invoices that are partly paid. The code calls invoice.Balance. What do you suspect? scenario

The invoices were loaded without their payments, so Payments is empty and AmountPaid is 0. Nothing throws. Add Include(i => i.Payments), or better for a report, project the sum of payments in SQL, and add a test with a partly paid invoice so the bug cannot return quietly.

How to answer: Explain why missing related data is silent, give both fixes and when each fits, and finish with how you would prevent a regression.

See the example · Permalink

S Would you enable lazy loading in a web API? Why or why not? design

Generally no. Every navigation access can issue a query, so loops become N+1 problems, the queries are invisible in the code, and serializing an entity can trigger loads across the whole graph. Load deliberately with Include or project to DTOs, which also keeps the API's shape independent of the entities.

How to answer: Give the performance and predictability reasons, mention serialization, and offer the alternative you would use. Acknowledge where lazy loading can be acceptable, such as small desktop tools.

See the example · Permalink

Architect / Lead (10+ yrs)

A How would you design soft delete and multi-tenancy filtering so no query can forget them? design

Put both rules in named global query filters on every affected entity, set the tenant from the request through the DbContext, and allow switching a filter off only in specific admin or repair code paths. Add database-side protection too, such as row-level security or filtered views for tenancy, and tests that fail if an entity lacks the filter.

How to answer: Cover the EF Core mechanism, the source of the tenant value, the controlled escape hatch, defense in depth in the database, and automated checks. Module 13 builds the multi-tenant version.

See the example · Permalink

Resources

Code for this lesson: git tag lesson-60-ef-core-relationships (code repository coming soon)
Files: src/SchoolApp.Core/Entities/FeeInvoice.cs, src/SchoolApp.Data/EfCore/Configurations/FeeConfigurations.cs, src/SchoolApp.Data/EfCore/Configurations/ExamConfigurations.cs, src/SchoolApp.Console/Program.cs, library-assignment/060-library-relationships.cs
Video: coming soon

What's next

EF Core now loads exactly what you ask for. Next: making it fast, by measuring tracking, projections, N+1 queries, split queries, and bulk updates on 10,000 students. Lesson 61: EF Core performance

Want live sessions, code reviews, and interview preparation? Join the next batch at Sahasra Technologies.

EF Core Relationships: Loading Related Data – Data Access | Lesson 60

Introduction

Why this matters

The concept

Example: navigation properties

Example: relationship types

Example: four ways to load related data

Apply it to the SMS

Step 1: the invoice keeps its payments

Step 2: map the relationships

Step 3: the program

Example: the missing Include

Example: Include and ThenInclude

Example: a report card from three tables

Example: both sides are connected

Example: explicit loading

Example: projections need no Include

Example: a global query filter for soft delete

Example: tables with triggers

Common mistakes

In real projects

Best practices

Recap

Homework

Quiz

Interview questions

Fresher (0-2 yrs)

Mid (3-5 yrs)

Senior (6-9 yrs)

Architect / Lead (10+ yrs)

Resources

What's next

Introduction​

Why this matters​

The concept​

Example: navigation properties​

Example: relationship types​

Example: four ways to load related data​

Apply it to the SMS​

Step 1: the invoice keeps its payments​

Step 2: map the relationships​

Step 3: the program​

Example: the missing Include​

Example: Include and ThenInclude​

Example: a report card from three tables​

Example: both sides are connected​

Example: explicit loading​

Example: projections need no Include​

Example: a global query filter for soft delete​

Example: tables with triggers​

Common mistakes​

In real projects​

Best practices​

Recap​

Homework​

Quiz​

Interview questions​

Fresher (0-2 yrs)​

Mid (3-5 yrs)​

Senior (6-9 yrs)​

Architect / Lead (10+ yrs)​

Resources​

What's next​

Introduction

Why this matters

The concept

Example: navigation properties

Example: relationship types

Example: four ways to load related data

Apply it to the SMS

Step 1: the invoice keeps its payments

Step 2: map the relationships

Step 3: the program

Example: the missing Include

Example: Include and ThenInclude

Example: a report card from three tables

Example: both sides are connected

Example: explicit loading

Example: projections need no Include

Example: a global query filter for soft delete

Example: tables with triggers

Common mistakes

In real projects

Best practices

Recap

Homework

Quiz

Interview questions

Fresher (0-2 yrs)

Mid (3-5 yrs)

Senior (6-9 yrs)

Architect / Lead (10+ yrs)

Resources

What's next