Table per Type in Entity Framework Core 2.0

Question

These are my models:

public class Company
{
   public int CompanyId { get; set; }
   public string Name { get; set; }
   public string Address { get; set; }
   public string Email { get; set; }
   public string Url { get; set; }
   //...
}

public class HeadOffice : Company
{
   public int HeadOfficeId { get; set; }
   public virtual List<BranchOffice> BranchOffice { get; set; } = new List<BranchOffice>();
}

public class BranchOffice : Company
{
   public int BranchOfficeId { get; set; }
   public virtual HeadOffice HeadOffice { get; set; }
}

I wanted the following database structure:

Table Company

• CompanyId (PK)
• Name
• Address
• Email
• Url

Table HeadOffice

• HeadOfficeId (PK)
• CompanyId (FK)

Table BranchOffice

• BranchOfficeId (PK)
• HeadOfficeId (FK)
• CompanyId (FK)

How can I do this?

When I create this migration, the EF creates just one table, with all columns! I don't want this approach!

Answer 1

You would have to change your Model to look like this, note that you can't use inheritance using this approach:

public class Company
{
   public int CompanyId { get; set; }
   //...
}

public class Company
{
   public int CompanyId { get; set; }
   public string Name { get; set; }
   //...
}

public class HeadOffice
{
   [ForeignKey(nameof(Company))]
   public int CompanyId { get; set; }
   public Company Company { get; set; }
   // Add Properties here
}

public class BranchOffice
{
   [ForeignKey(nameof(Company))]
   public int CompanyId { get; set; }
   public Company Company { get; set; }
   // Add Properties here
}

Your DbContext:

public class YourContext : DbContext
{
  public DbSet<Company> Companys { get; set; }
  public DbSet<HeadOffice> HeadOffices { get; set; }
  public DbSet<BranchOffice> BranchOffices { get; set; }

  public YourContext(DbContextOptions<YourContext> options)
    : base(options)
  {
  }
}

You could then use EF Core Migrations. The command would look somewhat like this:

dotnet ef migrations add Initial_TPT_Migration -p ./../../ModelProject.csproj -s ./../../ModelProject.csproj -c YourContext -o ./TptModel/CodeFirst/Migrations

It generats a Class Initial_TPT_Migration that contains methods to generate your database.

Usage

To query you would need to map Company Properties to the fieldnames. If you combine this with the Repository Pattern (link), it could actually be as convenient as the default approach in EF Core currently is to use.

YourContext ctx = ...

// Fetch all BranchOffices
var branchOffices = ctx.BranchOffices
          .Select(c => new BranchOffice()
                  {
                    CompanyId = c.CompanyId,
                    Name = c.Company.Name,
                  })
          .ToList();

You can find more informations about this approach here.