EF Core one entity to several tables

Question

I use EF Core in my project. Parent entity has three child collections of the same Child class.

public class Parent
{
    public virtual List<Child> FirstCollection { get; set; }
    public virtual List<Child> SecondCollection { get; set; }
    public virtual List<Child> ThirdCollection { get; set; }
}

public class Child
{
    public int Order { get; set; }
    public string Name { get; set; }
}

I'd like to store these collections in several tables in db, for example "First", "Second" and "Third".

Is it possible to configure Ef core to do so?

Answer 1

Using EF Core 3.0.

We begin to define the relationship by adding a primary key to the Parent class:

public class Parent
{
    public int Id { get; set; }
    public List<Child> FirstCollection { get; set; }
    public List<Child> SecondCollection { get; set; }
    public List<Child> ThirdCollection { get; set; }
}

To configure the relationships and tables, we use Fluent API. We override the OnModelCreating method:

protected override void OnModelCreating(ModelBuilder modelBuilder)
{
    modelBuilder.Entity<Parent>(entity =>
    {
        entity.OwnsMany(x => x.FirstCollection, a =>
        {
            a.ToTable("First");
            a.HasKey("Id");
        });

        entity.OwnsMany(x => x.SecondCollection, a =>
        {
            a.ToTable("Second");
            a.HasKey("Id");
        });

        entity.OwnsMany(x => x.ThirdCollection, a =>
        {
            a.ToTable("Third");
            a.HasKey("Id");
        });
    });
}

We've used Owned Types to map our classes to the database.

To save the data in three different tables, we add the ToTable method to the configuration.

The result is a table like this (SQLite):

CREATE TABLE "First" (
    "Id" INTEGER NOT NULL CONSTRAINT "PK_First" PRIMARY KEY AUTOINCREMENT,
    "Order" INTEGER NOT NULL,
    "Name" TEXT NULL,
    "ParentId" INTEGER NOT NULL,
    CONSTRAINT "FK_First_Parents_ParentId" FOREIGN KEY ("ParentId") REFERENCES "Parents" ("Id") ON DELETE CASCADE
);