I am having problems to create an Entity Framework Code-First mapping for the following sample database schema (in SQL Server):
Every table contains a TenantId which is part of all (composite) primary and foreign keys (Multi-Tenancy).
A Company is either a Customer or a Supplier and I try to model this via Table-Per-Type (TPT) inheritance mapping:
public abstract class Company
{
public int TenantId { get; set; }
public int CompanyId { get; set; }
public int AddressId { get; set; }
public Address Address { get; set; }
}
public class Customer : Company
{
public string CustomerName { get; set; }
public int SalesPersonId { get; set; }
public Person SalesPerson { get; set; }
}
public class Supplier : Company
{
public string SupplierName { get; set; }
}
Mapping with Fluent API:
modelBuilder.Entity<Company>()
.HasKey(c => new { c.TenantId, c.CompanyId });
modelBuilder.Entity<Customer>()
.ToTable("Customers");
modelBuilder.Entity<Supplier>()
.ToTable("Suppliers");
The base table Companies has a one-to-many relationship to an Address (every company has an address, no matter if customer or supplier) and I can create a mapping for this association:
modelBuilder.Entity<Company>()
.HasRequired(c => c.Address)
.WithMany()
.HasForeignKey(c => new { c.TenantId, c.AddressId });
The foreign key is composed of one part of the primary key - the TenantId - and a separate column - the AddressId. This works.
As you can see in the database schema, from database perspective the relationship between Customer and Person is basically the same kind of one-to-many relationship as between Company and Address - the foreign key is composed again of the TenantId (part of the primary key) and the column SalesPersonId. (Only a customer has a sales person, not a Supplier, therefore the relationship is in the derived class this time, not in the base class.)
I try to create a mapping for this relationship with Fluent API the same way as before:
modelBuilder.Entity<Customer>()
.HasRequired(c => c.SalesPerson)
.WithMany()
.HasForeignKey(c => new { c.TenantId, c.SalesPersonId });
But when EF tries to compile the model an InvalidOperationException is thrown:
The foreign key component 'TenantId' is not a declared property on type 'Customer'. Verify that it has not been explicitly excluded from the model and that it is a valid primitive property.
Apparently I cannot compose a foreign key from a property in the base class and from another property in the derived class (although in the database schema the foreign key is composed of columns both in the derived type's table Customer).
I tried two modifications to get it working perhaps:
Changed the foreign key association between
CustomerandPersonto an independent association, i.e. removed the propertySalesPersonId, and then tried the mapping:modelBuilder.Entity<Customer>() .HasRequired(c => c.SalesPerson) .WithMany() .Map(m => m.MapKey("TenantId", "SalesPersonId"));It doesn't help (I didn't really hope, it would) and the exception is:
Schema specified is not valid. ... Each property name in a type must be unique. Property name 'TenantId' was already defined.
Changed TPT to TPH mapping, i.e. removed the two
ToTablecalls. But it throws the same exception.
I see two workarounds:
Introduce a
SalesPersonTenantIdinto theCustomerclass:public class Customer : Company { public string CustomerName { get; set; } public int SalesPersonTenantId { get; set; } public int SalesPersonId { get; set; } public Person SalesPerson { get; set; } }and the mapping:
modelBuilder.Entity<Customer>() .HasRequired(c => c.SalesPerson) .WithMany() .HasForeignKey(c => new { c.SalesPersonTenantId, c.SalesPersonId });I tested this and it works. But I will have a new column
SalesPersonTenantIdin theCustomerstable in addition to theTenantId. This column is redundant because both columns always must have the same value from business perspective.Abandon inheritance mapping and create one-to-one mappings between
CompanyandCustomerand betweenCompanyandSupplier.Companymust become a concrete type then, not abstract and I would have two navigation properties inCompany. But this model wouldn't express correctly that a company is either a customer or a supplier and cannot be both at the same time. I didn't test it but I believe it would work.
I paste the full example I tested with (console application, reference to EF 4.3.1 assembly, downloaded via NuGet) in here if someone likes to experiment with it:
using System;
using System.Data.Entity;
namespace EFTPTCompositeKeys
{
public abstract class Company
{
public int TenantId { get; set; }
public int CompanyId { get; set; }
public int AddressId { get; set; }
public Address Address { get; set; }
}
public class Customer : Company
{
public string CustomerName { get; set; }
public int SalesPersonId { get; set; }
public Person SalesPerson { get; set; }
}
public class Supplier : Company
{
public string SupplierName { get; set; }
}
public class Address
{
public int TenantId { get; set; }
public int AddressId { get; set; }
public string City { get; set; }
}
public class Person
{
public int TenantId { get; set; }
public int PersonId { get; set; }
public string Name { get; set; }
}
public class MyContext : DbContext
{
public DbSet<Company> Companies { get; set; }
public DbSet<Address> Addresses { get; set; }
public DbSet<Person> Persons { get; set; }
protected override void OnModelCreating(DbModelBuilder modelBuilder)
{
modelBuilder.Entity<Company>()
.HasKey(c => new { c.TenantId, c.CompanyId });
modelBuilder.Entity<Company>()
.HasRequired(c => c.Address)
.WithMany()
.HasForeignKey(c => new { c.TenantId, c.AddressId });
modelBuilder.Entity<Customer>()
.ToTable("Customers");
// the following mapping doesn't work and causes an exception
modelBuilder.Entity<Customer>()
.HasRequired(c => c.SalesPerson)
.WithMany()
.HasForeignKey(c => new { c.TenantId, c.SalesPersonId });
modelBuilder.Entity<Supplier>()
.ToTable("Suppliers");
modelBuilder.Entity<Address>()
.HasKey(a => new { a.TenantId, a.AddressId });
modelBuilder.Entity<Person>()
.HasKey(p => new { p.TenantId, p.PersonId });
}
}
class Program
{
static void Main(string[] args)
{
Database.SetInitializer(new DropCreateDatabaseAlways<MyContext>());
using (var ctx = new MyContext())
{
try
{
ctx.Database.Initialize(true);
}
catch (Exception e)
{
throw;
}
}
}
}
}
Question: Is there any way to map the database schema above to a class model with Entity Framework?