8. Relationships

  1. Relationships

About this chapter

Master entity relationships to design normalized, maintainable database schemas using EF Core.

  • One-to-many relationships: Principal and dependent entities with navigation properties
  • Navigation properties: Reference and collection types for querying related data
  • Foreign keys: Explicit and convention-based configuration
  • OnModelCreating configuration: Fluent API for relationship setup
  • Delete behaviors: Cascade, restrict, and set null options
  • Querying relationships: Eager, lazy, and explicit loading strategies

Learning outcomes:

  • Define and configure one-to-many relationships in EF Core
  • Understand foreign key constraints and navigation properties
  • Use Fluent API to configure complex relationship rules
  • Choose appropriate delete behaviors for data integrity
  • Query related data efficiently with loading strategies

7.1 One-to-Many Relationships

// Principal entity (one side)
public class Platform
{    
  public int Id { get; set; }    
  public required string PlatformName { get; set; }        
  
  // Navigation property - collection    
  public ICollection<Command> Commands { get; set; } = new List<Command>();
}
  
// Dependent entity (many side)
public class Command
{    
  public int Id { get; set; }    
  public required string HowTo { get; set; }    
  public required string CommandLine { get; set; }        
  
  // Foreign key property    
  public int PlatformId { get; set; }        
  
  // Navigation property - reference    
  [ForeignKey("PlatformId")]    
  public required Platform Platform { get; set; }
}
  • Convention: EF detects relationships by naming patterns
  • Foreign Key: PlatformId automatically detected as FK to Platform.Id

7.2 Navigation Properties

  • Reference Navigation: Single object (Command.Platform)
  • Collection Navigation: Multiple objects (Platform.Commands)
  • Benefits:
    • Intuitive querying
    • Automatic join generation
    • Lazy/eager loading options

7.3 Configuring Relationships in OnModelCreating

protected override void OnModelCreating(ModelBuilder modelBuilder)
{    
  base.OnModelCreating(modelBuilder);        
  
  // Explicit relationship configuration
  modelBuilder.Entity<Platform>()        
    .HasMany(p => p.Commands)        
    .WithOne(c => c.Platform)        
    .HasForeignKey(c => c.PlatformId)        
    .OnDelete(DeleteBehavior.Cascade); //Delete commands when platform deleted            
    
  // Alternative: Configure from dependent side    
  modelBuilder.Entity<Command>()        
    .HasOne(c => c.Platform)        
    .WithMany(p => p.Commands)        
    .HasForeignKey(c => c.PlatformId);
}
  • Delete Behaviors:
    • Cascade: Delete children when parent deleted
    • Restrict: Prevent delete if children exist
    • SetNull: Set FK to null (FK must be nullable)
    • NoAction: Database handles it

7.4 CORRECTION: Adding Indexes Properly

protected override void OnModelCreating(ModelBuilder modelBuilder)
{    
  // Index on KeyRegistration.KeyIndex for fast lookups    
  modelBuilder.Entity<KeyRegistration>()        
    .HasIndex(k => k.KeyIndex)        
    .HasDatabaseName("Index_KeyIndex")        
    .IsUnique();        
  
  // Composite index for bulk job results    
  modelBuilder.Entity<BulkJobResult>()        
    .HasIndex(b => new { b.JobId, b.Position })        
    .HasDatabaseName("Index_JobId_Position");        
    
  // Index on foreign key (if not auto-created)    
  modelBuilder.Entity<Command>()        
    .HasIndex(c => c.PlatformId)        
    .HasDatabaseName("Index_Command_PlatformId");}
  • When to Add Indexes:
    • Foreign keys (often auto-created)
    • Frequently queried columns
    • Unique constraints
    • Columns in WHERE, JOIN, ORDER BY clauses
  • Trade-off: Faster reads, slower writes

7.5 Eager Loading vs Lazy Loading

// Eager Loading (explicit, one query with JOIN)
var command = await _context.Commands    
  .Include(c => c.Platform)  // Load related platform    
  .FirstOrDefaultAsync(c => c.Id == id);
  
// Multiple levels
var platform = await _context.Platforms    
  .Include(p => p.Commands)        
    .ThenInclude(c => c.CreatedByUser)    
  .FirstOrDefaultAsync(p => p.Id == id);
  
// Lazy Loading (implicit, separate queries - requires config)
// Not recommended for APIs due to N+1 problem
var command = await _context.Commands.FirstOrDefaultAsync(c => c.Id == id);
var platform = command.Platform;  // Separate query fired here
 
// Explicit Loading (load on demand)
var platform = await _context.Platforms.FindAsync(id);
await _context.Entry(platform)    
  .Collection(p => p.Commands)    
  .LoadAsync();
  • Best Practice for APIs: Use eager loading with Include()
  • N+1 Problem: Lazy loading can cause performance issues