CODEWITHSUNDEEP
c#entity-frameworkdatabase-migration
BG100
BG100

Reputation: 4531

EF Code First Many-to-Many with Additional Foreign Key

I'm using Entity Framework Code First to create a database, with two entities like this:

public class Tenant {

    public Tenant() {
        Users = new HashSet<User>();
    }

    [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<User> Users { get; set; }

}

public class User {

    public User() {
        Tenants = new HashSet<Tenant>();
    }

    [Key]
    public Guid ID { get; set; }

    public virtual ICollection<Tenant> Tenants { get; set; }

}

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:

CREATE TABLE Tenants (
    ID          INT NOT NULL,
    Code        NVARCHAR(12) NOT NULL,
    Description NVARCHAR(200) NOT NULL
)

CREATE TABLE Users (
    ID          UNIQUEIDENTIFIER NOT NULL
)

And it creates a many-to-many link table:

CREATE TABLE UserTenants (
    User_ID     UNIQUEIDENTIFIER NOT NULL,
    Tenant_ID   INT NOT NULL
)

My problem is when I try to add an additional foreign key field and navigation property to the Users entity:

public int CurrentTenantID { get; set; }
public virtual Tenant CurrentTenant { get; set; }

I would expect this to simply add an additional field to the users table:

CurrentTenantID INT NOT NULL

But when I generate a migration for this new foreign key, it does something crazy:

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 => c.Guid());
    AddColumn("acc.Users", "CurrentTenantID", c => c.Int(nullable: false));
    AddColumn("acc.Users", "Tenant_ID", c => 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");
}

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!

Upvotes: 0

Views: 487

Answers (1)

milanio
milanio

Reputation: 4232

I believe, it is because EF now needs a hint from you which relations you want to create. Try to specify it in your DbContext (override OnModelCreating):

 protected override void OnModelCreating(DbModelBuilder modelBuilder)
 {
     base.OnModelCreating(modelBuilder);

     modelBuilder.Entity<User>().HasMany(b => b.Tenants).WithMany(c => c.Users);
     modelBuilder.Entity<User>().HasOptional(b => b.CurrentTenant);
 }

Upvotes: 2

Related Questions