Relationships & Navigation

Level: Intermediate

ℹ️ Where This Fits

Relationships connect entities together. Understanding one-to-many, many-to-many, and loading strategies prevents N+1 queries and ensures efficient data loading.

ℹ️ What You'll Learn

• One-to-Many: Student has many Exams (Student→Exams list)
• Many-to-Many: Student has many Subjects (through StudentSubject junction)
• Foreign key: StudentId in Exam table references Student.Id
• Navigation property: public List<Exam> Exams { get; set; } for relationship access
• Inverse navigation: Exam→Student (optional back reference)
• Eager loading: .Include(s => s.Exams) loads related data upfront
• Lazy loading: Access student.Exams triggers automatic database query
• N+1 problem: 1 query + N queries for related data (solved by Include)
• ThenInclude: Nested includes (.Include(s => s.Exams).ThenInclude(e => e.Subject))
• Select projection: Get only needed data (.Select(s => new { s.Name, ExamCount = s.Exams.Count }))
• Explicit loading: _context.Entry(student).Collection(s => s.Exams).Load() (advanced)
• Cascade delete: Delete student automatically deletes exams
• School Management: Student→Exams, Student→FeeAccounts, Exam→ExamResults
• Common mistakes: Forgetting Include (N+1), circular references, lazy loading in loops

One-to-Many Example

// Student entity
public class Student
{
    public int Id { get; set; }
    public string Name { get; set; }
    
    // One student has many exams
    public List<Exam> Exams { get; set; } = new();
}

// Exam entity
public class Exam
{
    public int Id { get; set; }
    public string ExamName { get; set; }
    
    public int StudentId { get; set; }  // Foreign key
    public Student Student { get; set; }  // Inverse navigation
}

Eager Loading - Include

// Get student WITH exams
var student = await _context.Students
    .Include(s => s.Exams)
    .FirstOrDefaultAsync(s => s.Id == 101);

// Access exams (already loaded)
var examCount = student.Exams.Count;  // No database query

Nested Include - ThenInclude

// Get student with exams and exam subjects
var student = await _context.Students
    .Include(s => s.Exams)
        .ThenInclude(e => e.Subject)
    .FirstOrDefaultAsync(s => s.Id == 101);

var subjects = student.Exams.Select(e => e.Subject).ToList();

Many-to-Many with Junction Table

public class Student
{
    public int Id { get; set; }
    public string Name { get; set; }
    public List<StudentSubject> StudentSubjects { get; set; } = new();
}

public class Subject
{
    public int Id { get; set; }
    public string Name { get; set; }
    public List<StudentSubject> StudentSubjects { get; set; } = new();
}

public class StudentSubject
{
    public int StudentId { get; set; }
    public Student Student { get; set; }
    
    public int SubjectId { get; set; }
    public Subject Subject { get; set; }
}

Get student's subjects:

var student = await _context.Students
    .Include(s => s.StudentSubjects)
        .ThenInclude(ss => ss.Subject)
    .FirstOrDefaultAsync(s => s.Id == 101);

var subjects = student.StudentSubjects
    .Select(ss => ss.Subject)
    .ToList();

Projection - Select Only Needed Data

// Instead of loading all student data + exams, project only what's needed
var studentSummary = await _context.Students
    .Where(s => s.ClassName == "10-A")
    .Select(s => new
    {
        s.Id,
        s.Name,
        ExamCount = s.Exams.Count,
        LatestExamDate = s.Exams.Max(e => e.ExamDate)
    })
    .ToListAsync();

Avoid N+1 - Bad Example

// WRONG - causes N+1 queries
var students = await _context.Students.ToListAsync();
var studentExamCounts = new Dictionary<int, int>();

foreach (var student in students)
{
    // This queries database N times!
    var count = await _context.Exams
        .Where(e => e.StudentId == student.Id)
        .CountAsync();
    
    studentExamCounts[student.Id] = count;
}

Fix N+1 - Good Example

// RIGHT - single query with grouping
var studentExamCounts = await _context.Exams
    .GroupBy(e => e.StudentId)
    .Select(g => new
    {
        StudentId = g.Key,
        ExamCount = g.Count()
    })
    .ToListAsync();

Best Practices

✓ Use Include to prevent N+1 ✓ Use Select projection for performance ✓ Avoid lazy loading in loops ✓ Keep navigation properties virtual (for lazy loading if enabled)

💡 Common Relationship Patterns

• Student→Exams (One-to-Many, direct)
• Exam→ExamResults (One-to-Many)
• Student↔Subject (Many-to-Many via StudentSubject)
• FeeAccount→FeePayments (One-to-Many)

🤖Use AI to Learn Faster

Use ChatGPT, Claude, or Copilot to go deeper on Relationships & Navigation. Try these prompts:

• "What's the N+1 query problem and how do you solve it?"
• "What's the difference between Include and Select projection?"
• "How do you configure a many-to-many relationship?"
• "When should you use ThenInclude?"

💡 Tip: After reading this article, paste your own code into AI and ask "What could go wrong here and why?" — fastest way to find edge cases and deepen understanding.

Next Article

-> 07. Performance Optimization