CODEWITHSUNDEEP
c#asp.netasp.net-mvcentity-frameworkentity-framework-6
Reft
Reft

Reputation: 2433

Entity Framework - Code first - Data annotations - Unnecessary foreign key columns

Entity Framework is creating two unnecessary columns in my AccountCompanyRole table.

AccountCompany

public class AccountCompany
{
    [Key, Column(Order = 0), ForeignKey("Account")]
    public int AccountID { get; set; }

    [Key, Column(Order = 1), ForeignKey("Company")]
    public int CompanyID { get; set; }

    public virtual Account Account { get; set; }

    public virtual Company Company { get; set; }

    public virtual ICollection<AccountCompanyRole> AccountCompanyRoles { get; set; }
}

AccountCompanyRoles

public class AccountCompanyRole
{
    [Key, Column(Order = 0), ForeignKey("AccountCompany")]
    public int AccountID { get; set; }

    [Key, Column(Order = 1), ForeignKey("AccountCompany")]
    public int CompanyID { get; set; }

    [Key, Column(Order = 2), ForeignKey("Role")]
    public int RoleID { get; set; }

    public virtual AccountCompany AccountCompany { get; set; }

    public virtual Role Role { get; set; }
}

OnModelCreating

    protected override void OnModelCreating(DbModelBuilder modelBuilder)
    {
        modelBuilder.Entity<AccountCompanyRole>()
            .HasRequired(p => p.AccountCompany)
            .WithMany()
            .WillCascadeOnDelete(false);

        base.OnModelCreating(modelBuilder);
    }

Result in DB

Result in DB

As you can see, EF has added two extra foreign keys in AccountCompanyRoles even tho I specified primary key and foreign key in Data Annotations.

Questions

  1. How do I prevent it from creating AccountCompany_AccountID and AccountCompany_CompanyID?

  2. Is this the best structure for what I am after? Should I instead use one primarykey and put IsUnique index on AccountID and CompanyID? This would probably solve my problem..

Upvotes: 3

Views: 615

Answers (2)

Ivan Stoev
Ivan Stoev

Reputation: 205589

How do I prevent it from creating AccountCompany_AccountID and AccountCompany_CompanyID?

First, remove the ForeignKey attribute from CompanyID and RoleID.

Then either put ForeignKey attribute on your AccountCompany member like this:

public class AccountCompanyRole
{
    [Key, Column(Order = 0)]
    public int AccountID { get; set; }

    [Key, Column(Order = 1)]
    public int CompanyID { get; set; }

    [Key, Column(Order = 2), ForeignKey("Role")]
    public int RoleID { get; set; }

    [ForeignKey("AccountID, CompanyID")]
    public virtual AccountCompany AccountCompany { get; set; }

    public virtual Role Role { get; set; }
}

or use fluent configuration like this:

modelBuilder.Entity<AccountCompanyRole>()
            .HasRequired(r => r.AccountCompany)
            .WithMany(c => c.AccountCompanyRoles)
            .HasForeignKey(r => new { r.AccountID, r.CompanyID })
            .WillCascadeOnDelete(false);

Is this the best structure for what I am after? Should I instead use one primarykey and put IsUnique index on AccountID and CompanyID? This would probably solve my problem..

IMO both are valid approaches, use the one that better suits your needs.

Upvotes: 3

Indregaard
Indregaard

Reputation: 1195

You have to specify the foreign keys in your fluent relationship like this:

modelBuilder.Entity<AccountCompanyRole>()
            .HasRequired(p => p.AccountCompany)
            .WithMany().HasForeignKey(d => new { d.AccountID, d.CompanyID })
            .WillCascadeOnDelete(false);

        modelBuilder.Entity<AccountCompany>()
            .HasMany(c => c.AccountCompanyRoles)
            .WithRequired(d => d.AccountCompany)
            .HasForeignKey(e => new {e.AccountID, e.CompanyID});

Edit: Added fluent config for AccountCompany

Upvotes: 1

Related Questions