A Simple Analogy

EF relationships are like family connections. One-to-many is like parents and children, many-to-many is like students and courses, and configuring them ensures family trees stay consistent.

---

One-to-Many Relationships

public class Author
{
    public int Id { get; set; }
    public string Name { get; set; }
    
    // Navigation property (one-to-many)
    public List<Book> Books { get; set; } = new();
}

public class Book
{
    public int Id { get; set; }
    public string Title { get; set; }
    public int AuthorId { get; set; }  // Foreign key
    
    // Navigation property back to Author
    public Author Author { get; set; }
}

// Query
var authorWithBooks = await context.Authors
    .Include(a => a.Books)
    .FirstOrDefaultAsync(a => a.Id == 1);

foreach (var book in authorWithBooks.Books)
{
    Console.WriteLine(book.Title);
}

---

Many-to-Many Relationships

public class Student
{
    public int Id { get; set; }
    public string Name { get; set; }
    
    // Many-to-many
    public List<Course> Courses { get; set; } = new();
}

public class Course
{
    public int Id { get; set; }
    public string Title { get; set; }
    
    public List<Student> Students { get; set; } = new();
}

// DbContext configuration
protected override void OnModelCreating(ModelBuilder modelBuilder)
{
    modelBuilder.Entity<Student>()
        .HasMany(s => s.Courses)
        .WithMany(c => c.Students)
        .UsingEntity("StudentCourse");  // Auto-generated junction table
}

// Query
var students = await context.Courses
    .Where(c => c.Title == "Math")
    .SelectMany(c => c.Students)
    .ToListAsync();

---

Many-to-Many with Payload

// Explicit junction table to store extra data
public class StudentCourse
{
    public int StudentId { get; set; }
    public Student Student { get; set; }
    
    public int CourseId { get; set; }
    public Course Course { get; set; }
    
    public DateTime EnrolledDate { get; set; }
    public decimal Grade { get; set; }
}

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

public class Course
{
    public int Id { get; set; }
    public string Title { get; set; }
    
    public List<StudentCourse> StudentCourses { get; set; } = new();
}

// Query with payload
var courseDetails = await context.StudentCourses
    .Where(sc => sc.CourseId == courseId)
    .Select(sc => new {
        StudentName = sc.Student.Name,
        sc.Grade,
        sc.EnrolledDate
    })
    .ToListAsync();

---

Self-Referencing Relationships

public class Employee
{
    public int Id { get; set; }
    public string Name { get; set; }
    public int? ManagerId { get; set; }  // Points to another Employee
    
    // Navigation to manager
    public Employee Manager { get; set; }
    
    // Navigation to subordinates
    public List<Employee> Subordinates { get; set; } = new();
}

// Configuration
protected override void OnModelCreating(ModelBuilder modelBuilder)
{
    modelBuilder.Entity<Employee>()
        .HasOne(e => e.Manager)
        .WithMany(e => e.Subordinates)
        .HasForeignKey(e => e.ManagerId)
        .IsRequired(false);
}

// Query organizational hierarchy
var manager = await context.Employees
    .Include(e => e.Subordinates)
    .FirstOrDefaultAsync(e => e.Id == managerId);

Console.WriteLine($"{manager.Name} manages:");
foreach (var subordinate in manager.Subordinates)
{
    Console.WriteLine($"  - {subordinate.Name}");
}

---

Cascade Delete

public class Author
{
    public int Id { get; set; }
    public string Name { get; set; }
    public List<Book> Books { get; set; } = new();
}

public class Book
{
    public int Id { get; set; }
    public string Title { get; set; }
    public int AuthorId { get; set; }
    public Author Author { get; set; }
}

// Configuration
protected override void OnModelCreating(ModelBuilder modelBuilder)
{
    modelBuilder.Entity<Book>()
        .HasOne(b => b.Author)
        .WithMany(a => a.Books)
        .HasForeignKey(b => b.AuthorId)
        .OnDelete(DeleteBehavior.Cascade);  // Delete books when author deleted
}

// When you delete an author, books are automatically deleted
var author = await context.Authors.FindAsync(1);
context.Authors.Remove(author);
await context.SaveChangesAsync();  // Books with AuthorId=1 are deleted

---

Eager vs Lazy Loading

// Eager loading (specify what to include)
var author = await context.Authors
    .Include(a => a.Books)
    .FirstOrDefaultAsync(a => a.Id == 1);

// Lazy loading (automatic, requires virtual)
var author = await context.Authors.FindAsync(1);
var books = author.Books;  // Loads automatically if virtual and proxy enabled

// Explicit loading
var author = await context.Authors.FindAsync(1);
await context.Entry(author)
    .Collection(a => a.Books)
    .LoadAsync();

// Avoid N+1 problem
// Bad:
var authors = context.Authors.ToList();  // 1 query
foreach (var author in authors)
{
    var books = author.Books;  // N more queries
}

// Good:
var authors = await context.Authors
    .Include(a => a.Books)
    .ToListAsync();  // 1 query with all data

---

Best Practices

1. Use navigation properties: Makes queries easier
2. Configure explicitly: Remove ambiguity
3. Set appropriate delete behavior: Prevent orphans
4. Eager load when needed: Avoid N+1 queries
5. Use projections: Avoid loading unnecessary data

---

Related Concepts

• Foreign key constraints
• Change tracking
• Entity state (Added, Modified, Deleted)
• Transaction management

---

Summary

Master EF Core relationships to model complex data structures. Configure one-to-many, many-to-many, and self-referencing relationships to maintain data consistency and query efficiency.