Author : HASSAN MD TAREQ

ORM RDBMS relation

ORM RDBMS
DbContext Database
DbSet<TEntity> Table
TEntity Table row
TEntity properties Table columns

Entity class

[Table("Employees")]
public class Employee
{

	[ScaffoldColumn(false)]
	[DatabaseGenerated(DatabaseGeneratedOption.Identity)]
	public int Id { get; set; }

	public string FirstName { get; set; }
	public string LastName { get; set; }

	// Navigational properties here
}

More:

One to one relationship using annotation

Principle entity (parent of the relationship): Samurai.cs

public class Samurai
{
	[Key]
	public int Id { get; set; }

	public string Name { get; set; }

	[InverseProperty(nameof(Samurai))]
	public SecretIdentity SecretIdentity { get; set; } //Navigational Property
}

Dependent entity (child of the relationship): SecretIdentity.cs

public class SecretIdentity
{
	[Key]
	public int Id { get; set; }

	public string RealName { get; set; }

	[ForeignKey(nameof(Samurai))]
	public int SamuraiId { get; set; }

	public Samurai Samurai { get; set; } //Navigational Property
}

SamuraiContext.cs

public class SamuraiContext : DbContext
{
	public DbSet<Samurai> Samurais { get; set; }
	public DbSet<Quote> Quotes { get; set; }
	
	protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
	{
		optionsBuilder.UseSqlServer("Server = (localdb)\\mssqllocaldb; Database = SamuraiData; Trusted_Connection = True; ", options => options.MaxBatchSize(30));
	}
}

[Required]: enforcing one-to-one (not zero)

public class Samurai
{
	[Key]
	public int Id { get; set; }

	public string Name { get; set; }

	[Required]
	[InverseProperty(nameof(Samurai))]
	public SecretIdentity SecretIdentity { get; set; }
}

One to one relationship using fluent api

  • instead of using annotation, you can use fluent api
  • not all configurations are possible using annotation, in that case fluent api must be used

SamuraiContext.cs

public class SamuraiContext : DbContext
{
	public DbSet<Samurai> Samurais { get; set; }

	protected override void OnModelCreating(ModelBuilder modelBuilder)
	{

        modelBuilder.Entity<Samurai>()
            .HasOne(samurai => samurai.SecretIdentity)
            .WithOne(secretIdentity => secretIdentity.Samurai)
            .HasForeignKey<SecretIdentity>(secretIdentity => secretIdentity.Id);
		
		base.OnModelCreating(modelBuilder);
	}

	protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
	{
		optionsBuilder.UseSqlServer("Server = (localdb)\\mssqllocaldb; Database = SamuraiData; Trusted_Connection = True; ", options => options.MaxBatchSize(30));
	}
}

.IsRequired(): enforcing one-to-one (not zero)

protected override void OnModelCreating(ModelBuilder modelBuilder)
{

	modelBuilder.Entity<Samurai>().Property(samurai => samurai.SecretIdentity).IsRequired();

	modelBuilder.Entity<Samurai>()
		.HasOne(samurai => samurai.SecretIdentity)
		.WithOne(secretIdentity => secretIdentity.Samurai)
		.HasForeignKey<SecretIdentity>(secretIdentity => secretIdentity.Id);
	
	base.OnModelCreating(modelBuilder);
}

One to many relationship

  • by convention, cascade delete will be set to Cascade for required relationships and ClientSetNull for optional relationships. See: Cascade Delete
  • [InverseProperty(nameof(Prop))] or [InverseProperty("prop")] is optional, but needed if property name is different i.e
    • Samurai Samurai {get; set; } => [InverseProperty(nameof(Samurai))]
    • Samurai ModernSamurai { get; set; } => [InverseProperty(nameof("ModernSamurai"))]
  • ICollection<T>: used because IEnumerable does not have Add/Remove and ICollection<T> is interface and therefore EF can use different implementations (List<T> or any data structure that implements ICollection<T>)
  • virtual ICollection<T>: keyword virtual is used for lazy loading (not supported yet in EF core?)

One to many relationship using annotation

Principle entity (parent of the relationship): Samurai.cs

public class Samurai
{
    [Key]
	public int Id { get; set; }
	public string Name { get; set; }

	[InverseProperty(nameof(Samurai))]
	public List<Quote> Quotes { get; set; } // Navigational Property

	public Samurai()
	{
		Quotes = new List<Quote>();
	}
}

Dependent entity (child of the relationship): Quote.cs

public class Quote
{
	[Key]
	public int Id { get; set; }
	public string Text { get; set; }

	[ForeignKey(nameof(Samurai))]
	public int SamuraiId { get; set; }

	public Samurai Samurai { get; set; } // Navigational Property
}

SamuraiContext.cs

public class SamuraiContext : DbContext
{
	public DbSet<Samurai> Samurais { get; set; }
	public DbSet<Quote> Quotes { get; set; }
	
	protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
	{
		optionsBuilder.UseSqlServer("Server = (localdb)\\mssqllocaldb; Database = SamuraiData; Trusted_Connection = True; ", options => options.MaxBatchSize(30));
	}
}

One to many relationship using fluent api

  • instead of using annotation, you can use fluent api
  • not all configurations are possible using annotation, in that case fluent api must be used

SamuraiContext.cs

public class SamuraiContext : DbContext
{
	public DbSet<Samurai> Samurais { get; set; }

	public DbSet<Quote> Quotes { get; set; }

	protected override void OnModelCreating(ModelBuilder modelBuilder)
	{

		modelBuilder.Entity<Samurai>()
			.HasMany<Quote>(samurai => samurai.Quotes)
			.WithOne(quote => quote.Samurai)
			.HasForeignKey(quote => quote.SamuraiId)
		
		base.OnModelCreating(modelBuilder);
	}

	protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
	{
		optionsBuilder.UseSqlServer("Server = (localdb)\\mssqllocaldb; Database = SamuraiData; Trusted_Connection = True; ", options => options.MaxBatchSize(30));
	}
}

Or

protected override void OnModelCreating(ModelBuilder modelBuilder)
{

	modelBuilder.Entity<Quote>()
		.HasOne<Samurai>(quote => quote.Samurai)
		.WithMany(samurai => samurai.Quotes)
		.HasForeignKey(quote => quote.SamuraiId);
	
	base.OnModelCreating(modelBuilder);
}

Required and Optional Relationships

You can use the Fluent API to configure whether the relationship is required or optional. Ultimately this controls whether the foreign key property is required or optional. If you have a foreign key property in your entity class then the requiredness of the relationship is determined based on whether the foreign key property is required or optional.

Required FK: [Required]

public class Quote
{
	[Key]
	public int Id { get; set; }
	public string Text { get; set; }

	[Required]
	[ForeignKey(nameof(Samurai))]
	public int SamuraiId { get; set; }

	public Samurai Samurai { get; set; } // Navigational Property
}

Optional FK: int?

public class Quote
{
	[Key]
	public int Id { get; set; }
	public string Text { get; set; }

	[ForeignKey(nameof(Samurai))]
	public int? SamuraiId { get; set; }

	public Samurai Samurai { get; set; } //Navigational Property
}

Required FK: IsRequired()

modelBuilder.Entity<Samurai>()
			.HasMany<Quote>(samurai => samurai.Quotes)
			.WithOne(quote => quote.Samurai)
			.HasForeignKey(quote => quote.SamuraiId)
            .IsRequired();

Optional FK: .IsRequired(false)

  • Has(), With() creates a NOT Null column
  • to Create a Nullable Column you can either use optional type for foreign key or .IsRequired(false)

SamuraiContext.cs

modelBuilder.Entity<Samurai>()
			.HasMany<Quote>(samurai => samurai.Quotes)
			.WithOne(quote => quote.Samurai)
			.HasForeignKey(quote => quote.SamuraiId)
            .IsRequired(false);

Explicitly Enforcing Cascade Delete

.OnDelete(DeleteBehavior.Cascade);

protected override void OnModelCreating(ModelBuilder modelBuilder)
{

	modelBuilder.Entity<Quote>()
		.HasOne<Samurai>(quote => quote.Samurai)
		.WithMany(samurai => samurai.Quotes)
		.HasForeignKey(quote => quote.SamuraiId)
		.OnDelete(DeleteBehavior.Cascade);
		
	base.OnModelCreating(modelBuilder);
}

Many to many relationship

  • join entity is required
  • primary key (composite key) of the join entity is composed of primary kies of Principle and Dependent entities
  • data annotations in EF Core does not have the option of creating Composite Primary Key so fluent api is used

Samurai.cs

public class Samurai
{
	[Key]
	public int Id { get; set; }
	public string Name { get; set; }

	public List<SamuraiBattle> SamuraiBattles { get; set; }
}

Battle.cs

public class Battle
{
	[Key]
	public int Id { get; set; }
	public string Name { get; set; }
	public DateTime StartDate { get; set; }
	public DateTime EndDate { get; set; }

	public List<SamuraiBattle> SamuraiBattles { get; set; }
}

Join entity: SamuraiBattle.cs

public class SamuraiBattle
{
	[ForeignKey(nameof(Samurai))]
	public int SamuraiId { get; set; }
	public Samurai Samurai { get; set; }

	[ForeignKey(nameof(Battle))]
	public int BattleId { get; set; }
	public Battle Battle { get; set; }
}

SamuraiContext.cs

public class SamuraiContext : DbContext
{
	public DbSet<Samurai> Samurais { get; set; }
	public DbSet<Battle> Battles { get; set; }

	protected override void OnModelCreating(ModelBuilder modelBuilder)
	{

	  modelBuilder.Entity<SamuraiBattle>()
		.HasKey(s => new {s.BattleId, s.SamuraiId});
		
		base.OnModelCreating(modelBuilder);
	}

	protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
	{
		optionsBuilder.UseSqlServer("Server = (localdb)\\mssqllocaldb; Database = SamuraiData; Trusted_Connection = True; ", options => options.MaxBatchSize(30));
	}
}

See: