05. EF Core Relationships: Guide to Moving Beyond the Basics

Stop wrestling with N+1 queries and circular references. Learn to design, configure, and optimize EF Core relationships for high-performance, real-world .NET APIs.

🔥 1. A Common Production Problem

An API that was working fine in development suddenly started slowing down in production.

• API response time increased from 200ms → 5 seconds
• Database CPU usage spiked
• Logs showed hundreds of SQL queries for a single request

Root cause?

👉 Incorrect relationship design + wrong loading strategy

• Missing navigation properties
• Improper Include() usage
• N+1 query problem

This is very common.

Relationships are not just database concepts. They directly affect performance, scalability, and maintainability.

📘 2. What Are Relationships in EF Core?

In simple terms, a relationship is how two tables “talk” to each other. In the real world, things rarely exist in isolation. A Customer has Orders. A Book has an Author.

Without defined relationships, your database is just a pile of disconnected spreadsheets. Relationships allow EF Core to “join” these pieces of data together so you can ask complex questions like: “Show me all gold-tier customers who bought a .NET book in the last 30 days.”

Why Relationships Matter

In real applications:

• UI needs nested data
• APIs return combined data
• Reports depend on joins

👉 EF Core uses relationships to generate SQL JOINs automatically

🔐3. One-to-One Relationship (User–Profile)

This relationship is less common but very important in real systems.

👉 One User → One Profile 👉 One Profile → One User

🧠 Real-World Example

Think of:

• User (login details)
• UserProfile (additional info like address, DOB)

We don’t keep everything in one table because:

• Separation of concerns
• Security (sensitive vs public data)
• Scalability

Entity Classes

public class User
{
    public int Id { get; set; }
    public string Email { get; set; }

    public UserProfile Profile { get; set; }
}

public class UserProfile
{
    public int Id { get; set; }
    public string Address { get; set; }

    // Foreign Key
    public int UserId { get; set; }
    public User User { get; set; }
}

⚙️ Fluent API Configuration (Important)

modelBuilder.Entity<User>()
    .HasOne(u => u.Profile)
    .WithOne(p => p.User)
    .HasForeignKey<UserProfile>(p => p.UserId)
    .OnDelete(DeleteBehavior.Cascade);

👉 Key point:

• Foreign key is defined in dependent entity (UserProfile)

🔍 Query Example

var users = await _context.Users
    .Include(u => u.Profile)
    .ToListAsync();

🧾 Generated SQL

SELECT u.*, p.*
FROM Users u
LEFT JOIN UserProfiles p ON u.Id = p.UserId

👉 EF Core uses JOIN even for One-to-One.

⚠️ Important Things to Understand

1️⃣ Principal vs Dependent

• User → Principal
• UserProfile → Dependent

👉 Dependent holds the foreign key

2️⃣ Optional vs Required Relationship

✅ Required

.HasForeignKey<UserProfile>(p => p.UserId)
.IsRequired();

👉 Every User MUST have a Profile

✅ Optional

.IsRequired(false);

👉 User can exist without Profile

3️⃣ Unique Constraint

EF ensures:

👉 One User → One Profile only

(No duplicates allowed)

🚫 Common Mistakes in One-to-One

❌ Not defining foreign key explicitly ❌ Confusion about which entity is dependent ❌ Using One-to-Many instead of One-to-One ❌ Forgetting unique constraint

💡 When Should You Use One-to-One?

Use it when:

✔ Data is logically separate ✔ Security concerns exist ✔ Table size needs to be controlled

Real Use Cases

• User → Profile
• Order → Invoice
• Employee → SalaryDetails
• Product → ProductMetadata

🧱 4. One-to-Many Relationship (Book–Author)

This is the most common relationship. One Author can write many Books, but each Book typically has one primary Author.

👉 One Author → Many Books 👉 One Book → One Author

🧠 Real-World Analogy

Think of:

• One teacher teaches many students
• One customer places many orders
• One company has many employees

👉 This is everywhere in real applications.

🧱 Entity Design (Important)

public class Author
{
    public int Id { get; set; }
    public string Name { get; set; }
    // Collection Navigation Property
    public ICollection<Book> Books { get; set; } = new List<Book>();
}
public class Book
{
    public int Id { get; set; }
    public string Title { get; set; }
    // Foreign Key (VERY IMPORTANT)
    public int AuthorId { get; set; }
    // Reference Navigation Property
    public Author Author { get; set; }
}

🔍 What’s Happening Here?

• Author.Books → Represents "many" side
• Book.Author → Represents "one" side
• AuthorId → Foreign key (connects both tables)

👉 EF Core uses this to build relationships automatically.

⚙️ Fluent API Configuration (Recommended)

modelBuilder.Entity<Book>()
    .HasOne(b => b.Author)       // Book has one Author
    .WithMany(a => a.Books)      // Author has many Books
    .HasForeignKey(b => b.AuthorId)
    .OnDelete(DeleteBehavior.Cascade);

💡 Why Fluent API?

• More control
• Better readability for complex systems
• Preferred in enterprise projects

🧾 Data Annotations (Alternative)

public int AuthorId { get; set; }
[ForeignKey("AuthorId")]
public Author Author { get; set; }

👉 Good for simple projects, but limited for advanced scenarios.

👉 One author linked to multiple books

🔍 Query Example

var books = await _context.Books
    .Include(b => b.Author)
    .ToListAsync();

🧾 Generated SQL (Simplified)

SELECT b.Id, b.Title, a.Name
FROM Books b
INNER JOIN Authors a
ON b.AuthorId = a.Id

👉 EF Core automatically converts relationships into JOIN queries.

⚠️ Important Concepts

1️⃣ Principal vs Dependent

• Author → Principal
• Book → Dependent

👉 Dependent entity holds the foreign key.

2️⃣ Required vs Optional Relationship

✅ Required

.HasForeignKey(b => b.AuthorId)
.IsRequired();

👉 Every Book MUST have an Author

✅ Optional

.IsRequired(false);

👉 Book can exist without Author

3️⃣ Delete Behavior (Very Important)

🔥 Cascade Delete

.OnDelete(DeleteBehavior.Cascade);

👉 Delete Author → all Books deleted

🛑 Restrict

.OnDelete(DeleteBehavior.Restrict);

👉 Prevent delete if related data exists

⚠️ SetNull

.OnDelete(DeleteBehavior.SetNull);

👉 Author deleted → Book.AuthorId becomes NULL

🚫 Common Mistakes in One-to-Many

❌ Missing Foreign Key (AuthorId) ❌ Not defining navigation properties ❌ Overusing Include() ❌ Wrong delete behavior ❌ Circular references in API

🚀 Performance Tips (Real-World)

❌ Bad Approach

_context.Authors.Include(a => a.Books).ToList();

👉 Loads everything (heavy query)

✅ Better Approach

_context.Authors
    .Select(a => new {
        a.Name,
        BookCount = a.Books.Count
    })

👉 Fetch only required data

Use AsNoTracking()

_context.Books.AsNoTracking()

👉 Faster read performance

💡 When to Use One-to-Many?

Use it when:

✔ Data has clear ownership ✔ Parent-child structure exists ✔ Logical grouping is required

Real Use Cases

• Customer → Orders
• Author → Books
• Category → Products
• Company → Employees

🔗 5. Many-to-Many Relationship — Deep Dive (Student–Course)

This relationship is very common in real-world systems but often misunderstood.

👉 One Student → Many Courses 👉 One Course → Many Students

🧠 Real-World Analogy

Think of:

• Students enrolling in courses
• Users having multiple roles
• Products belonging to multiple categories

👉 Both sides depend on each other equally.

🧱 Entity Design (Modern EF Core — Implicit Join)

public class Student
{
    public int Id { get; set; }
    public string Name { get; set; }
    // Navigation Property
    public ICollection<Course> Courses { get; set; } = new List<Course>();
}
public class Course
{
    public int Id { get; set; }
    public string Title { get; set; }
    // Navigation Property
    public ICollection<Student> Students { get; set; } = new List<Student>();
}

🔍 What EF Core Does Internally

👉 EF Core automatically creates a join table

StudentCourse
------------------
StudentId
CourseId

👉 You don’t need to define it manually (EF Core 5+)

⚙️ Fluent API (Optional Configuration)

modelBuilder.Entity<Student>()
    .HasMany(s => s.Courses)
    .WithMany(c => c.Students);

👉 EF handles everything else automatically.

📊 Sample Data

👉 Many-to-Many relationship in action

🔍 Query Example

var students = await _context.Students
    .Include(s => s.Courses)
    .ToListAsync();

🧾 Generated SQL (Simplified)

SELECT s.Id, s.Name, c.Id, c.Title
FROM Students s
JOIN StudentCourse sc ON s.Id = sc.StudentId
JOIN Courses c ON sc.CourseId = c.Id

👉 EF Core uses JOIN + bridge table

⚠️ Important Concepts

1️⃣ Join Table (Bridge Table)

This is the heart of Many-to-Many.

👉 Stores relationships between two tables

2️⃣ No Foreign Key in Main Tables

Unlike One-to-Many:

❌ No CourseId in Student ❌ No StudentId in Course

👉 Relationship exists in third table

🚨 When Implicit Join is NOT Enough

Use Explicit Join Entity when:

✔ You need extra fields ✔ You need audit info ✔ You need business logic

🧱 Explicit Join Entity (Advanced)

public class Enrollment
{
    public int StudentId { get; set; }
    public Student Student { get; set; }
    public int CourseId { get; set; }
    public Course Course { get; set; }
    public DateTime EnrolledOn { get; set; }
    public string Grade { get; set; }
}

⚙️ Fluent API Configuration

modelBuilder.Entity<Enrollment>()
    .HasKey(e => new { e.StudentId, e.CourseId });

modelBuilder.Entity<Enrollment>()
    .HasOne(e => e.Student)
    .WithMany()
    .HasForeignKey(e => e.StudentId);

modelBuilder.Entity<Enrollment>()
    .HasOne(e => e.Course)
    .WithMany()
    .HasForeignKey(e => e.CourseId);

🔍 Query Example (Explicit Join)

var enrollments = await _context.Enrollments
    .Include(e => e.Student)
    .Include(e => e.Course)
    .ToListAsync();

⚠️ Common Mistakes in Many-to-Many

❌ Using One-to-Many instead of Many-to-Many ❌ Forgetting join table concept ❌ Overusing Include() (huge joins) ❌ Not handling large datasets ❌ Ignoring performance

🚀 Performance Tips (Very Important)

❌ Bad Approach

_context.Students.Include(s => s.Courses).ToList();

👉 Loads everything → heavy query

✅ Better Approach (Projection)

_context.Students
    .Select(s => new {
        s.Name,
        CourseCount = s.Courses.Count
    })

👉 Efficient and optimized

Use AsSplitQuery()

_context.Students
    .Include(s => s.Courses)
    .AsSplitQuery()

👉 Prevents large JOIN explosion

💡 When to Use Many-to-Many?

Use it when:

✔ Both entities are independent ✔ Relationship is flexible ✔ No strict ownership

Real Use Cases

• User ↔ Roles
• Product ↔ Categories
• Student ↔ Courses
• Actor ↔ Movies

⚡ 6. Loading Strategies Explained

🔹 Eager Loading

var books = _context.Books
    .Include(b => b.Author)
    .ToList();

👉 Loads related data immediately

SQL

JOIN Authors

🔹 Lazy Loading

var book = _context.Books.First();
var author = book.Author;

👉 Loads data when accessed

⚠️ Can cause N+1 issue

🔹 Explicit Loading

var book = await _context.Books.FirstAsync();
await _context.Entry(book)
    .Reference(b => b.Author)
    .LoadAsync();

👉 Controlled loading

When to Use

🚀 7. Real .NET API Example

DbContext

public class AppDbContext : DbContext
{
    public DbSet<Book> Books { get; set; }
    public DbSet<Author> Authors { get; set; }
}

Service Layer

public async Task<List<BookDto>> GetBooksAsync()
{
    return await _context.Books
        .Include(b => b.Author)
        .Select(b => new BookDto
        {
            Title = b.Title,
            AuthorName = b.Author.Name
        })
        .ToListAsync();
}

Controller

[HttpGet]
public async Task<IActionResult> GetBooks()
{
    var result = await _service.GetBooksAsync();
    return Ok(result);
}

Response

[
  {
    "title": "C# Basics",
    "authorName": "John"
  }
]

⚠️ 8. Common Mistakes

❌ Overusing Include() ❌ N+1 queries ❌ Wrong navigation properties ❌ Circular references ❌ Lazy loading misuse

📊 9. Performance Considerations

Use Select Instead of Include

.Select(b => new { b.Title, b.Author.Name })

Avoid Loading Full Graph

Don’t load everything blindly.

Indexing

Ensure foreign keys are indexed.

Reduce DB Calls

Combine queries when possible.

📋 10. Best Practices

✅ Use navigation properties properly ✅ Prefer Select over Include ✅ Avoid Lazy Loading in APIs ✅ Use DTOs ✅ Keep relationships simple ✅ Use indexes ✅ Test queries ✅ Monitor SQL logs

🔚 11. Conclusion

EF Core relationships are simple in theory.

But in real projects:

👉 They affect performance 👉 They affect architecture 👉 They affect scalability

Key Takeaways

• Understand relationships deeply
• Choose loading strategy wisely
• Avoid common mistakes