title: Inheritance and composite foreign keys - one part of the key in base class, the other part in derived class
body: <p>I am having problems to create an Entity Framework Code-First mapping for the following sample database schema (in SQL Server):</p>
  
  <p><img src="https://i.stack.imgur.com/vFGvn.png" alt="Database schema with composite foreign keys"></p>
  
  <p>Every table contains a <code>TenantId</code> which is part of all (composite) primary and foreign keys (Multi-Tenancy).</p>
  
  <p>A <code>Company</code> is either a <code>Customer</code> or a <code>Supplier</code> and I try to model this via Table-Per-Type (TPT) inheritance mapping:</p>
  
  <pre class="lang-cs prettyprint-override"><code>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; }
  }
  </code></pre>
  
  <p>Mapping with Fluent API:</p>
  
  <pre class="lang-cs prettyprint-override"><code>modelBuilder.Entity&lt;Company&gt;()
      .HasKey(c =&gt; new { c.TenantId, c.CompanyId });
  
  modelBuilder.Entity&lt;Customer&gt;()
      .ToTable("Customers");
  
  modelBuilder.Entity&lt;Supplier&gt;()
      .ToTable("Suppliers");
  </code></pre>
  
  <p>The base table <code>Companies</code> has a one-to-many relationship to an <code>Address</code> (every company has an address, no matter if customer or supplier) and I can create a mapping for this association:</p>
  
  <pre class="lang-cs prettyprint-override"><code> modelBuilder.Entity&lt;Company&gt;()
       .HasRequired(c =&gt; c.Address)
       .WithMany()
       .HasForeignKey(c =&gt; new { c.TenantId, c.AddressId });
  </code></pre>
  
  <p>The foreign key is composed of one part of the primary key - the <code>TenantId</code> - and a separate column - the <code>AddressId</code>. This works. </p>
  
  <p>As you can see in the database schema, from database perspective the relationship between <code>Customer</code> and <code>Person</code> is basically the same kind of one-to-many relationship as between <code>Company</code> and <code>Address</code> - the foreign key is composed again of the <code>TenantId</code> (part of the primary key) and the column <code>SalesPersonId</code>. (Only a customer has a sales person, not a <code>Supplier</code>, therefore the relationship is in the derived class this time, not in the base class.)</p>
  
  <p>I try to create a mapping for this relationship with Fluent API the same way as before:</p>
  
  <pre class="lang-cs prettyprint-override"><code>modelBuilder.Entity&lt;Customer&gt;()
      .HasRequired(c =&gt; c.SalesPerson)
      .WithMany()
      .HasForeignKey(c =&gt; new { c.TenantId, c.SalesPersonId });
  </code></pre>
  
  <p>But when EF tries to compile the model an <code>InvalidOperationException</code> is thrown:</p>
  
  <blockquote>
    <p>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.</p>
  </blockquote>
  
  <p>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 <em>both</em> in the derived type's table <code>Customer</code>).</p>
  
  <p>I tried two modifications to get it working perhaps:</p>
  
  <ul>
  <li><p>Changed the foreign key association between <code>Customer</code> and <code>Person</code> to an independent association, i.e. removed the property <code>SalesPersonId</code>, and then tried the mapping:</p>
  
  <pre class="lang-cs prettyprint-override"><code>modelBuilder.Entity&lt;Customer&gt;()
      .HasRequired(c =&gt; c.SalesPerson)
      .WithMany()
      .Map(m =&gt; m.MapKey("TenantId", "SalesPersonId"));
  </code></pre>
  
  <p>It doesn't help (I didn't really hope, it would) and the exception is:</p>
  
  <blockquote>
    <p>Schema specified is not valid. ... Each property name in a type must
    be unique. Property name 'TenantId' was already defined.</p>
  </blockquote></li>
  <li><p>Changed TPT to TPH mapping, i.e. removed the two <code>ToTable</code> calls. But it throws the same exception.</p></li>
  </ul>
  
  <p>I see two workarounds:</p>
  
  <ul>
  <li><p>Introduce a <code>SalesPersonTenantId</code> into the <code>Customer</code> class:</p>
  
  <pre class="lang-cs prettyprint-override"><code>public class Customer : Company
  {
      public string CustomerName { get; set; }
  
      public int SalesPersonTenantId { get; set; }
      public int SalesPersonId { get; set; }
      public Person SalesPerson { get; set; }
  }
  </code></pre>
  
  <p>and the mapping:</p>
  
  <pre class="lang-cs prettyprint-override"><code>modelBuilder.Entity&lt;Customer&gt;()
      .HasRequired(c =&gt; c.SalesPerson)
      .WithMany()
      .HasForeignKey(c =&gt; new { c.SalesPersonTenantId, c.SalesPersonId });
  </code></pre>
  
  <p>I tested this and it works. But I will have a new column <code>SalesPersonTenantId</code> in the <code>Customers</code> table in addition to the <code>TenantId</code>. This column is redundant because both columns always must have the same value from business perspective.</p></li>
  <li><p>Abandon inheritance mapping and create one-to-one mappings between <code>Company</code> and <code>Customer</code> and between <code>Company</code> and <code>Supplier</code>. <code>Company</code> must become a concrete type then, not abstract and I would have two navigation properties in <code>Company</code>. But this model wouldn't express correctly that a company is <em>either</em> a customer <em>or</em> a supplier and cannot be both at the same time. I didn't test it but I believe it would work.</p></li>
  </ul>
  
  <p>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:</p>
  
  <pre class="lang-cs prettyprint-override"><code>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&lt;Company&gt; Companies { get; set; }
          public DbSet&lt;Address&gt; Addresses { get; set; }
          public DbSet&lt;Person&gt; Persons { get; set; }
  
          protected override void OnModelCreating(DbModelBuilder modelBuilder)
          {
              modelBuilder.Entity&lt;Company&gt;()
                  .HasKey(c =&gt; new { c.TenantId, c.CompanyId });
  
              modelBuilder.Entity&lt;Company&gt;()
                  .HasRequired(c =&gt; c.Address)
                  .WithMany()
                  .HasForeignKey(c =&gt; new { c.TenantId, c.AddressId });
  
              modelBuilder.Entity&lt;Customer&gt;()
                  .ToTable("Customers");
  
              // the following mapping doesn't work and causes an exception
              modelBuilder.Entity&lt;Customer&gt;()
                  .HasRequired(c =&gt; c.SalesPerson)
                  .WithMany()
                  .HasForeignKey(c =&gt; new { c.TenantId, c.SalesPersonId });
  
              modelBuilder.Entity&lt;Supplier&gt;()
                  .ToTable("Suppliers");
  
              modelBuilder.Entity&lt;Address&gt;()
                  .HasKey(a =&gt; new { a.TenantId, a.AddressId });
  
              modelBuilder.Entity&lt;Person&gt;()
                  .HasKey(p =&gt; new { p.TenantId, p.PersonId });
          }
      }
  
      class Program
      {
          static void Main(string[] args)
          {
              Database.SetInitializer(new DropCreateDatabaseAlways&lt;MyContext&gt;());
              using (var ctx = new MyContext())
              {
                  try
                  {
                      ctx.Database.Initialize(true);
                  }
                  catch (Exception e)
                  {
                      throw;
                  }
              }
          }
      }
  }
  </code></pre>
  
  <p><strong>Question: Is there any way to map the database schema above to a class model with Entity Framework?</strong></p>