https://docs.microsoft.com/zh-cn/ef/core/modeling/relationships

1. Introduction to terms

• Dependent entity: a dependent entity, an entity containing foreign keys, and a child in an entity relationship.
• Principal entity: principal entity, entity containing primary key / alternate key, and parent entity in entity relationship.
• Principal key: primary key / alternate key
• Foreign key
• Navigation property: navigation property. It refers to the entity appearing on the Dependent entity or Principal entity. It is used to point to other related entities.
  • Collection navigation property: a collection of multiple objects
  • Reference navigation property: single object
  • Reverse navigation property: reverse navigation property
• Self referencing relationship: self referencing relationship, such as id and parentId in tree structure

Refer to the following codes:

public class Blog//Subject entity
{
    public int BlogId { get; set; }//Primary key
    public string Url { get; set; }

    public List<Post> Posts { get; set; }//Navigation properties, also post Reverse navigation properties of Blog
}

public class Post//Dependent entity
{
    public int PostId { get; set; }
    public string Title { get; set; }
    public string Content { get; set; }

    public int BlogId { get; set; }//Foreign key
    public Blog Blog { get; set; }//Navigation attribute, also blog Reverse navigation properties of Posts
}

2. Automatic configuration relationship

If an attribute of an entity class cannot be mapped to a scalar type (scalar type, such as int, long, etc.), it is considered a navigation attribute. If a navigation attribute is found on an entity class, a relationship will be created.

2.1 complete configuration

The definitions of Post and Blog in the previous section are relatively complete like that. There are two requirements for complete definition:

• There are corresponding navigation attributes on both sides of the entity, rather than having one side and not having one side
• If the name of an attribute in dependent entity meets the following rules, it is configured as a foreign key:
  • <navigation property name><principal key property name>
  • <navigation property name>Id
  • <principal entity name><principal key property name>
  • <principal entity name>Id

2.2 configuration without foreign keys

Although it is recommended to have foreign keys, it is ok to have no foreign keys. When there is no foreign key, the shadow attribute whose name conforms to the rules of < navigation property name > < principal key property name > or < principal entity name > < principal key property name > will be introduced as the foreign key.

public class Blog
{
    public int BlogId { get; set; }
    public string Url { get; set; }
    
    public List<Post> Posts { get; set; }
}

public class Post
{
    public int PostId { get; set; }
    public string Title { get; set; }
    public string Content { get; set; }
    
    public Blog Blog { get; set; }
}

A shadow attribute named BlogId is generated.

2.3 configuration without navigation attribute pair

Even if there is no corresponding reverse navigation attribute and no foreign key attribute, the relationship can be established as long as there is a navigation attribute

public class Blog
{
    public int BlogId { get; set; }
    public string Url { get; set; }

    public List<Post> Posts { get; set; }
}

public class Post
{
    public int PostId { get; set; }
    public string Title { get; set; }
    public string Content { get; set; }
}

3. Manually configure relationships

Configure navigation properties using HasOne/HasMany and reverse navigation properties using WithOne/WithMany.

internal class MyContext : DbContext
{
    public DbSet<Blog> Blogs { get; set; }
    public DbSet<Post> Posts { get; set; }

    protected override void OnModelCreating(ModelBuilder modelBuilder)
    {
        modelBuilder.Entity<Post>()
            .HasOne(p => p.Blog)//Indicates that a Post corresponds to a Blog
            .WithMany(b => b.Posts);//Indicates that one Blog corresponds to multiple posts
            
        //The above code is equivalent to the following two lines
        modelBuilder.Entity<Post>().HasOne(p=>p.Blog);
        modelBuilder.Entity<Blog>().HasMany(b=>b.Posts);
    }
}

public class Blog
{
    public int BlogId { get; set; }
    public string Url { get; set; }

    public List<Post> Posts { get; set; }
}

public class Post
{
    public int PostId { get; set; }
    public string Title { get; set; }
    public string Content { get; set; }

    public Blog Blog { get; set; }
}

Of course, it can also be configured through features:

public class Post
{
    public int PostId { get; set; }
    public string Title { get; set; }
    public string Content { get; set; }

    public int AuthorUserId { get; set; }
    public User Author { get; set; }

    public int ContributorUserId { get; set; }
    public User Contributor { get; set; }
}

public class User
{
    public string UserId { get; set; }
    public string FirstName { get; set; }
    public string LastName { get; set; }

    [InverseProperty("Author")]
    public List<Post> AuthoredPosts { get; set; }

    [InverseProperty("Contributor")]
    public List<Post> ContributedToPosts { get; set; }
}

Look at the following table creation statements:

CREATE TABLE "SysRole" (
    "Id" INTEGER NOT NULL CONSTRAINT "PK_SysRole" PRIMARY KEY AUTOINCREMENT,
    "RoleName" TEXT NOT NULL
);

CREATE TABLE "SysUsers" (
    "Id" INTEGER NOT NULL CONSTRAINT "PK_SysUsers" PRIMARY KEY AUTOINCREMENT,
    "UserName" TEXT NOT NULL,
    "Pwd" TEXT NOT NULL,
    "CreateTime" TEXT NULL,
    "RoleId" INTEGER NULL,
    CONSTRAINT "FK_SysUsers_SysRole_RoleId" FOREIGN KEY ("RoleId") REFERENCES "SysRole" ("Id") ON DELETE RESTRICT
);

3.1 configuration without navigation attribute pair

If your navigation properties are not paired, WithOne and WithMany can not pass in parameters.

internal class MyContext : DbContext
{
    public DbSet<Blog> Blogs { get; set; }
    public DbSet<Post> Posts { get; set; }

    protected override void OnModelCreating(ModelBuilder modelBuilder)
    {
        modelBuilder.Entity<Blog>()
            .HasMany(b => b.Posts)
            .WithOne();
    }
}

public class Blog
{
    public int BlogId { get; set; }
    public string Url { get; set; }

    public List<Post> Posts { get; set; }
}

public class Post
{
    public int PostId { get; set; }
    public string Title { get; set; }
    public string Content { get; set; }
}

3.2 manually specifying foreign keys

Using the fluent API, you can configure single and composite foreign keys:

internal class MyContext : DbContext
{
    public DbSet<Blog> Blogs { get; set; }
    public DbSet<Post> Posts { get; set; }

    protected override void OnModelCreating(ModelBuilder modelBuilder)
    {
        modelBuilder.Entity<Post>()
            .HasOne(p => p.Blog)
            .WithMany(b => b.Posts)
            .HasForeignKey(p => p.BlogForeignKey);
    }
}

public class Blog
{
    public int BlogId { get; set; }
    public string Url { get; set; }

    public List<Post> Posts { get; set; }
}

public class Post
{
    public int PostId { get; set; }
    public string Title { get; set; }
    public string Content { get; set; }

    public int BlogForeignKey { get; set; }
    public Blog Blog { get; set; }
}

Composite foreign key:

internal class MyContext : DbContext
{
    public DbSet<Car> Cars { get; set; }

    protected override void OnModelCreating(ModelBuilder modelBuilder)
    {
        modelBuilder.Entity<Car>()
            .HasKey(c => new { c.State, c.LicensePlate });//Composite primary key

        modelBuilder.Entity<RecordOfSale>()
            .HasOne(s => s.Car)
            .WithMany(c => c.SaleHistory)
            .HasForeignKey(s => new { s.CarState, s.CarLicensePlate });//Composite foreign key
    }
}

public class Car
{
    public string State { get; set; }
    public string LicensePlate { get; set; }
    public string Make { get; set; }
    public string Model { get; set; }

    public List<RecordOfSale> SaleHistory { get; set; }
}

public class RecordOfSale
{
    public int RecordOfSaleId { get; set; }
    public DateTime DateSold { get; set; }
    public decimal Price { get; set; }

    public string CarState { get; set; }
    public string CarLicensePlate { get; set; }
    public Car Car { get; set; }
}

A single foreign key configuration feature can be used:

public class Blog
{
    public int BlogId { get; set; }
    public string Url { get; set; }

    public List<Post> Posts { get; set; }
}

public class Post
{
    public int PostId { get; set; }
    public string Title { get; set; }
    public string Content { get; set; }

    public int BlogForeignKey { get; set; }

    [ForeignKey("BlogForeignKey")]//If the BlogForeignKey does not exist, a shadow attribute is created
    public Blog Blog { get; set; }
}

Configure shadow foreign keys:

protected override void OnModelCreating(ModelBuilder modelBuilder)
{
        //1. Configure shadow attributes first
        modelBuilder.Entity<Post>()
            .Property<int>("BlogForeignKey");

        //2. Then configure the shadow attribute as a foreign key
        modelBuilder.Entity<Post>()
            .HasOne(p => p.Blog)
            .WithMany(b => b.Posts)
            .HasForeignKey("BlogForeignKey");
}

Configure the name of foreign key: by default, the name rule of foreign key is FK_< Class name of dependent entity >_< Class name of principal entity >_< The name of the foreign key attribute > can be seen in the database, such as FK_SysUsers_SysRole_RoleId.
Of course, you can also manually change the name of the foreign key:

protected override void OnModelCreating(ModelBuilder modelBuilder)
{
    modelBuilder.Entity<Post>()
        .HasOne(p => p.Blog)
        .WithMany(b => b.Posts)
        .HasForeignKey(p => p.BlogId)
        .HasConstraintName("ForeignKey_Post_Blog");
}

3.3 configuration without navigation attribute

internal class MyContext : DbContext
{
    public DbSet<Blog> Blogs { get; set; }
    public DbSet<Post> Posts { get; set; }

    protected override void OnModelCreating(ModelBuilder modelBuilder)
    {
        modelBuilder.Entity<Post>()
            .HasOne<Blog>()
            .WithMany()
            .HasForeignKey(p => p.BlogId);
    }
}

public class Blog
{
    public int BlogId { get; set; }
    public string Url { get; set; }
}

public class Post
{
    public int PostId { get; set; }
    public string Title { get; set; }
    public string Content { get; set; }

    public int BlogId { get; set; }
}

3.4 point foreign keys to non primary keys

If you want the foreign key to point to the attribute of the non entity primary key. You can configure as follows:

1. Single foreign key

internal class MyContext : DbContext
{
    public DbSet<Car> Cars { get; set; }

    protected override void OnModelCreating(ModelBuilder modelBuilder)
    {
        modelBuilder.Entity<RecordOfSale>()
            .HasOne(s => s.Car)
            .WithMany(c => c.SaleHistory)
            .HasForeignKey(s => s.CarLicensePlate)
            .HasPrincipalKey(c => c.LicensePlate);
    }
}

public class Car
{
    public int CarId { get; set; }
    public string LicensePlate { get; set; }
    public string Make { get; set; }
    public string Model { get; set; }

    public List<RecordOfSale> SaleHistory { get; set; }
}

public class RecordOfSale
{
    public int RecordOfSaleId { get; set; }
    public DateTime DateSold { get; set; }
    public decimal Price { get; set; }

    public string CarLicensePlate { get; set; }
    public Car Car { get; set; }
}

1. Composite foreign key

internal class MyContext : DbContext
{
    public DbSet<Car> Cars { get; set; }

    protected override void OnModelCreating(ModelBuilder modelBuilder)
    {
        modelBuilder.Entity<RecordOfSale>()
            .HasOne(s => s.Car)
            .WithMany(c => c.SaleHistory)
            .HasForeignKey(s => new { s.CarState, s.CarLicensePlate })//The order of carstate and carlicenseplate should correspond to that of state and licenseplate
            .HasPrincipalKey(c => new { c.State, c.LicensePlate });
    }
}

public class Car
{
    public int CarId { get; set; }
    public string State { get; set; }
    public string LicensePlate { get; set; }
    public string Make { get; set; }
    public string Model { get; set; }

    public List<RecordOfSale> SaleHistory { get; set; }
}

public class RecordOfSale
{
    public int RecordOfSaleId { get; set; }
    public DateTime DateSold { get; set; }
    public decimal Price { get; set; }

    public string CarState { get; set; }
    public string CarLicensePlate { get; set; }
    public Car Car { get; set; }
}

3.5 set whether the foreign key can be blank

By default, whether the foreign key can be empty depends on whether the foreign key attribute of the entity class can be empty. However, you can configure whether the shadow foreign key can be empty in the following ways:

protected override void OnModelCreating(ModelBuilder modelBuilder)
{
    modelBuilder.Entity<Post>().HasOne(p => p.Blog).WithMany(b => b.Posts).IsRequired();
}

3.6 setting cascade deletion

When an entity is deleted, the corresponding dependent entity is deleted

protected override void OnModelCreating(ModelBuilder modelBuilder)
{
    modelBuilder.Entity<Post>()
        .HasOne(p => p.Blog)
        .WithMany(b => b.Posts)
        .OnDelete(DeleteBehavior.Cascade);
}

4. Configuration of other relationships

The above examples are all one to many relationships. Next, we will introduce other types of relationships

4.1 one to one

The navigation attributes on both sides are not collections, and there is a unique index on the foreign key attribute. EF will automatically determine the dependent entity and the principal entity.

public class Blog
{
    public int BlogId { get; set; }
    public string Url { get; set; }

    public BlogImage BlogImage { get; set; }
}

public class BlogImage
{
    public int BlogImageId { get; set; }
    public byte[] Image { get; set; }
    public string Caption { get; set; }
    //Defines a foreign key, which EF considers to be a dependent entity
    public int BlogId { get; set; }
    public Blog Blog { get; set; }
}

If the EF automatically determines that the dependent entity and principal entity are wrong, the dependency is manually specified according to the previously described use of fluent API and features.

4.2 many to many

The navigation properties on both sides are collections.

public class Post
{
    public int PostId { get; set; }
    public string Title { get; set; }
    public string Content { get; set; }

    public ICollection<Tag> Tags { get; set; }
}

public class Tag
{
    public string TagId { get; set; }

    public ICollection<Post> Posts { get; set; }
}

An additional associated table will be created:

CREATE TABLE [Posts] (
    [PostId] int NOT NULL IDENTITY,
    [Title] nvarchar(max) NULL,
    [Content] nvarchar(max) NULL,
    CONSTRAINT [PK_Posts] PRIMARY KEY ([PostId])
);

CREATE TABLE [Tags] (
    [TagId] nvarchar(450) NOT NULL,
    CONSTRAINT [PK_Tags] PRIMARY KEY ([TagId])
);

CREATE TABLE [PostTag] (
    [PostsId] int NOT NULL,
    [TagsId] nvarchar(450) NOT NULL,
    CONSTRAINT [PK_PostTag] PRIMARY KEY ([PostsId], [TagsId]),//Composite primary key
    CONSTRAINT [FK_PostTag_Posts_PostsId] FOREIGN KEY ([PostsId]) REFERENCES [Posts] ([PostId]) ON DELETE CASCADE,
    CONSTRAINT [FK_PostTag_Tags_TagsId] FOREIGN KEY ([TagsId]) REFERENCES [Tags] ([TagId]) ON DELETE CASCADE
);

Of course, you can also create an entity to define a many to many relationship:

public class MyContext : DbContext
{
    public MyContext(DbContextOptions<MyContext> options)
        : base(options)
    {
    }

    public DbSet<Post> Posts { get; set; }
    public DbSet<Tag> Tags { get; set; }

    protected override void OnModelCreating(ModelBuilder modelBuilder)
    {
        modelBuilder.Entity<PostTag>()
            .HasKey(t => new { t.PostId, t.TagId });

        modelBuilder.Entity<PostTag>()
            .HasOne(pt => pt.Post)
            .WithMany(p => p.PostTags)
            .HasForeignKey(pt => pt.PostId);

        modelBuilder.Entity<PostTag>()
            .HasOne(pt => pt.Tag)
            .WithMany(t => t.PostTags)
            .HasForeignKey(pt => pt.TagId);
    }
}

public class Post
{
    public int PostId { get; set; }
    public string Title { get; set; }
    public string Content { get; set; }

    public List<PostTag> PostTags { get; set; }
}

public class Tag
{
    public string TagId { get; set; }

    public List<PostTag> PostTags { get; set; }
}

public class PostTag
{
    public DateTime PublicationDate { get; set; }

    public int PostId { get; set; }
    public Post Post { get; set; }

    public string TagId { get; set; }
    public Tag Tag { get; set; }
}