title: EF Code First Many-to-Many with Additional Foreign Key
body: <p>I'm using Entity Framework Code First to create a database, with two entities like this:</p>
  
  <pre><code>public class Tenant {
  
      public Tenant() {
          Users = new HashSet&lt;User&gt;();
      }
  
      [Key]
      public int ID { get; set; }
  
      [Required]
      [StringLength(12)]
      public string Code { get; set; }
  
      [Required]
      [StringLength(200)]
      public string Description { get; set; }
  
      public virtual ICollection&lt;User&gt; Users { get; set; }
  
  }
  
  public class User {
  
      public User() {
          Tenants = new HashSet&lt;Tenant&gt;();
      }
  
      [Key]
      public Guid ID { get; set; }
  
      public virtual ICollection&lt;Tenant&gt; Tenants { get; set; }
  
  }
  </code></pre>
  
  <p>You can see that I have a navigation property from Tenant to Users, and a navigation property from User to Tenants. This correctly creates a many-to-many relationship, and results in the following database structure:</p>
  
  <pre><code>CREATE TABLE Tenants (
      ID          INT NOT NULL,
      Code        NVARCHAR(12) NOT NULL,
      Description NVARCHAR(200) NOT NULL
  )
  
  CREATE TABLE Users (
      ID          UNIQUEIDENTIFIER NOT NULL
  )
  </code></pre>
  
  <p>And it creates a many-to-many link table:</p>
  
  <pre><code>CREATE TABLE UserTenants (
      User_ID     UNIQUEIDENTIFIER NOT NULL,
      Tenant_ID   INT NOT NULL
  )
  </code></pre>
  
  <p>My problem is when I try to add an additional foreign key field and navigation property to the Users entity:</p>
  
  <pre><code>public int CurrentTenantID { get; set; }
  public virtual Tenant CurrentTenant { get; set; }
  </code></pre>
  
  <p>I would expect this to simply add an additional field to the users table:</p>
  
  <pre><code>CurrentTenantID INT NOT NULL
  </code></pre>
  
  <p>But when I generate a migration for this new foreign key, it does something crazy:</p>
  
  <pre><code>public override void Up()
  {
      DropForeignKey("acc.UserTenants", "User_ID", "acc.Users");
      DropForeignKey("acc.UserTenants", "Tenant_ID", "acc.Tenants");
      DropIndex("acc.UserTenants", new[] { "User_ID" });
      DropIndex("acc.UserTenants", new[] { "Tenant_ID" });
      AddColumn("acc.Tenants", "User_ID", c =&gt; c.Guid());
      AddColumn("acc.Users", "CurrentTenantID", c =&gt; c.Int(nullable: false));
      AddColumn("acc.Users", "Tenant_ID", c =&gt; c.Int());
      CreateIndex("acc.Tenants", "User_ID");
      CreateIndex("acc.Users", "CurrentTenantID");
      CreateIndex("acc.Users", "Tenant_ID");
      AddForeignKey("acc.Users", "CurrentTenantID", "acc.Tenants", "ID", cascadeDelete: true);
      AddForeignKey("acc.Tenants", "User_ID", "acc.Users", "ID");
      AddForeignKey("acc.Users", "Tenant_ID", "acc.Tenants", "ID");
      DropTable("acc.UserTenants");
  }
  </code></pre>
  
  <p>Why is it dropping the UserTenants table? All it needs to do is add the new column to the users table, and create a foreign key constraint!</p>