In the previous article, I introduced the logic of relationship mapping in Entity Framework Core. The foreign key mapping of EF left in the previous article is not mentioned, that is, the relationship of one-to-one, one to many, many to one, many to many, etc. This article will give you a detailed analysis of how to set up these mappings.

1. Relationship between entities

From the perspective of data tables, there are one-to-one, one to many (many to one) and many to many relationships between the two tables.

One to one means that table A has one record corresponding to table B and at most one record corresponding to it. Conversely, table A has at most one record corresponding to one of table B's records. Specifically, in the data table, table A and table b each have A foreign key pointing to each other.

One to many and many to one are one concept, but the direction of reference is the opposite. The so-called one to many is that there is an attribute or column pointing to another entity in the multi-party, while the "one" has no corresponding attribute pointing to the multi-party.

Many to many means that instances of two classes each have A set attribute pointing to each other. In other words, A has 0 to many B's and B has 0 to many A's. Here is an ER graph about many to many.

2. One to one relationship

Two example classes are given first. For the convenience of understanding, I only keep the primary key and navigation attributes:

public class SingleModel
{
    public int Id { get; set; }
    public SingleTargetModel SingleTarget { get; set; }
}

public class SingleTargetModel
{
    public int Id { get; set; }
    public SingleModel Single { get; set; }
}

Then we start to write the configuration file:

public class SingleModelConfig : IEntityTypeConfiguration<SingleModel>
{
    public void Configure(EntityTypeBuilder<SingleModel> builder)
    {
        builder.ToTable("SingleModel");
        builder.HasKey(p => p.Id);
        builder.Property(p => p.Id).ValueGeneratedOnAdd();
        var relation = builder.HasOne(t => t.SingleTarget).WithOne(r => r.Single);

    }
}

public class SingleTargeModelConfig : IEntityTypeConfiguration<SingleTargetModel>
{
    public void Configure(EntityTypeBuilder<SingleTargetModel> builder)
    {
        builder.ToTable("SingleTargetModel");
        builder.HasKey(p => p.Id);
        builder.Property(p => p.Id).ValueGeneratedOnAdd();
    }
}

Where HasOne represents that the current entity is the "one" in the relationship, and WithOne represents the relationship of the navigation target class.

Of course, if the two configurations are directly applied to the EF Context, the

Update-Database

The following errors are reported:

The child/dependent side could not be determined for the one-to-one relationship between 'SingleModel.SingleTarget' and 'SingleTargetModel.Single'. To identify the child/dependent side of the relationship, configure the foreign key property. If these navigations should not be part of the same relationship configure them without specifying the inverse. See http://go.microsoft.com/fwlink/?LinkId=724062 for more details.

It means that you can't define a child in a one-to-one relationship/Subordination

How to solve it? Previously, EF would automatically generate a foreign key according to the navigation attributes, but this one-to-one would not work. So we have to manually configure the foreign key in the entity class on one side of the navigation property and specify it with HasForeignKey. (if you do not use the Fluent API, you also need to configure the foreign key on one end of the entity class, and not on the other end.).

After modification:

public class SingleModel
{
    public int Id { get; set; }
    public int TargetId { get; set; }
    public SingleTargetModel SingleTarget { get; set; }
}
public class SingleTargetModel
{
    public int Id { get; set; }
    public SingleModel Single { get; set; }
}

So the final configuration should be as follows:

public class SingleModelConfig : IEntityTypeConfiguration<SingleModel>
{
    public void Configure(EntityTypeBuilder<SingleModel> builder)
    {
        builder.ToTable("SingleModel");
        builder.HasKey(p => p.Id);
        builder.Property(p => p.Id).ValueGeneratedOnAdd();
        builder.HasOne(t => t.SingleTarget).WithOne(r => r.Single).HasForeignKey<SingleModel>(t=>t.TargetId);

    }
}

public class SingleTargeModelConfig : IEntityTypeConfiguration<SingleTargetModel>
{
    public void Configure(EntityTypeBuilder<SingleTargetModel> builder)
    {
        builder.ToTable("SingleTargetModel");
        builder.HasKey(p => p.Id);
        builder.Property(p => p.Id).ValueGeneratedOnAdd();
        //builder.HasOne(t => t.Single).WithOne(r => r.SingleTarget).HasForeignKey<SingleTargetModel>("SingleId");
    }
}

Note the row I annotated. Now EF only generates a foreign key relationship in the SingleModel table. When retrieving the SingleTargetModel, EF will retrieve the corresponding foreign key relationship from the SingleModel table and import it.

If you uncomment this line, EF will add a foreign key named SingleId to the SingleTargetModel table, and cancel the foreign key in SingleModel.

However, if a SingleId of a non empty property is added to the SingleTargetModel at this time, SQLite will report an error when inserting data. Error message:

SQLite Error 19: 'FOREIGN KEY constraint failed'.

Other database prompt, foreign key cannot be empty.

So EF doesn't recommend this one-to-one relationship.

This is the generated DDL SQL statement:

create table SingleModel
(
	Id INTEGER not null
		constraint PK_SingleModel
			primary key autoincrement,
	TargetId INTEGER not null
		constraint FK_SingleModel_SingleTargetModel_TargetId
			references SingleTargetModel
				on delete cascade
);

create unique index IX_SingleModel_TargetId
	on SingleModel (TargetId);

create table SingleTargetModel
(
	Id INTEGER not null
		constraint PK_SingleTargetModel
			primary key autoincrement
);

3. One to many or many to one

As usual, let's start with two classes:

public class OneToManySingle
{
    public int Id { get; set; }
    public List<OneToManyMany> Manies { get; set; }
}

public class OneToManyMany
{
    public int Id { get; set; }
    public OneToManySingle One { get; set; }
}

If viewed from one to many single, the relationship is one to many, if viewed from one to many, the relationship is many to one.

Let's take a look at the one to many configuration:

public class OneToManySingleConfig : IEntityTypeConfiguration<OneToManySingle>
{
    public void Configure(EntityTypeBuilder<OneToManySingle> builder)
    {
        builder.ToTable("OneToManySingle");
        builder.HasKey(p => p.Id);
        builder.Property(p => p.Id).ValueGeneratedOnAdd();
        builder.HasMany(t => t.Manies)
            .WithOne(p => p.One);
    }
}
public class OneToManyManyConfig : IEntityTypeConfiguration<OneToManyMany>
{
    public void Configure(EntityTypeBuilder<OneToManyMany> builder)
    {
        builder.ToTable("OneToManyMany");
        builder.HasKey(p => p.Id);
        builder.Property(p => p.Id).ValueGeneratedOnAdd();
        //builder.HasOne(p => p.One).WithMany(t=>t.Manies);
    }
}

When using implicit foreign keys, you only need to set the association of navigation properties. If you want to set up on the Single side, you need to use HasMany to indicate the relationship between setting up a pair of X, then calling WithOne to indicate that it is Many to one. If it is the Many side, you must first declare it is HasOne.

The parameters in WithXXX can be omitted if only one-way navigation is configured.

If the display declares a foreign key, you need to annotate it with HasForeignKey.

The following is the generated DDL SQL statement:

create table OneToManySingle
(
	Id INTEGER not null
		constraint PK_OneToManySingle
			primary key autoincrement
);
create table OneToManyMany
(
	Id INTEGER not null
		constraint PK_OneToManyMany
			primary key autoincrement,
	OneId INTEGER
		constraint FK_OneToManyMany_OneToManySingle_OneId
			references OneToManySingle
				on delete restrict
);

create index IX_OneToManyMany_OneId
	on OneToManyMany (OneId);

4. Many to many

When we talk about many to many, we need to understand a concept first. Many to many, for both ends of the navigation, you can't find the corresponding mark on yourself. That is to say, there will be no foreign key pointing to the other side in each data table. So, how to realize many to many? Add a special middle table to store the relationship between them.

The function of configuring intermediate table in mapping relationship is cancelled in EF Core, so an intermediate table is needed in EF Core:

public class ManyToManyModelA
{
    public int Id { get; set; }
    public List<ModelAToModelB> ModelBs { get; set; }
}
public class ModelAToModelB
{
    public int Id { get; set; }
    public ManyToManyModelA ModelA { get; set; }
    public ManyToManyModelB ModelB { get; set; }
}
public class ManyToManyModelB
{
    public int Id { get; set; }
    public List<ModelAToModelB> ModelAs { get; set; }
}

Let's continue with the configuration file:

public class ManyToManyToModelAConfig : IEntityTypeConfiguration<ManyToManyModelA>
{
    public void Configure(EntityTypeBuilder<ManyToManyModelA> builder)
    {
        builder.ToTable("ManyToManyModelA");
        builder.HasKey(p => p.Id);
        builder.Property(p => p.Id).ValueGeneratedOnAdd();
        builder.HasMany(t => t.ModelBs).WithOne(p => p.ModelA);
    }
}

public class ManyToManyModelBConfig : IEntityTypeConfiguration<ManyToManyModelB>
{
    public void Configure(EntityTypeBuilder<ManyToManyModelB> builder)
    {
        builder.ToTable("ManyToManyModelB");
        builder.HasKey(p => p.Id);
        builder.Property(p => p.Id).ValueGeneratedOnAdd();
        builder.HasMany(t => t.ModelAs).WithOne(p => p.ModelB);
    }
}

Different from the one to many relationship, this requires both parties to configure a many to one mapping, pointing to the intermediate table.

5. Additional

In the foreign key constraint of EF, the navigation attribute is nullable by default. If it is required to be non empty, that is, the other end of the navigation property must exist, you need to add it when configuring the relationship:

IsRequired()

This method is also used to declare that fields are required. This verification is performed when the EF calls SaveChanges.

6. To be continued

As usual, the next article will introduce the usage of EF Core in development.