Why am I getting an extra foreign key column with Entity Framework Code First Foreign Key Attributes?

Question

I recently came across this strange problem with Entity Framework Code First.

My class looks like this

public class Status
{
        [Key]
        public int StatusID { get; set; }     
        public string Name { get; set; }
        public int MemberID { get; set; }

        [ForeignKey("MemberID")]
        public virtual Member Member { get; set; }                

        public int PosterID { get; set; }

        [ForeignKey("PosterID")]
        public virtual Member Poster { get; set; }        

        public virtual ICollection<StatusLike> StatusLikes { get; set; }        
        public virtual ICollection<StatusComment> StatusComments { get; set; }
}

My Member class looks like this

 public class Member
    {
        [Key]
        public int MemberID { get; set; }
        public string FirstName { get; set; }
        public string LastName { get; set; }
        public string Bio { get; set; }

        public virtual ICollection<MemberCourseTaken> MemberCourseTakens { get; set; }
        public virtual ICollection<Status> Statuses { get; set; }
        public virtual ICollection<Club> FoundedClubs { get; set; }

        public string EmailAddress { get; set; }
        public string Password { get; set; }
        public string Phone { get; set; }

        public int AccountSourceID { get; set; }
        public AccountSource AccountSource { get; set; }

        public int AddressID { get; set; }
        public Address Address { get; set; }
        public string ProfilePhoto { get; set; }

        public int MemberRankID { get; set; }
        public MemberRank MemberRank { get; set; }
        public DateTime Created { get; set; }
        public DateTime Modified { get; set; }
    }

And for whatever reason the database table that is created has the following columns

StatusID
Name
MemberID
PosterID
Member_MemberID

with MemberID, PosterID, and Member_MemberID being foreign keys.

How can I keep Member_MemberID from being generated?

Answer 1

Your Member_MemberID column is created because of the Member.Statuses property. I can imagine that this is not what you want. Probably members and statuses should exist independent of each other, so you need a junction table.

I don't know if you already use the OnModelCreating override of the DbContext, but that's the place to change the mapping between Member and Status:

protected override void OnModelCreating(DbModelBuilder mb)
{
    mb.Entity<Member>().HasMany(m => m.Statuses).WithMany();
}

This will create a table MemberStatuses table with the two Id columns as foreign keys. This is a way to model a many-to-many relationship without a navigation property on the "other" side of the association. (I don't think you want a Members property in Status).

Answer 2

I've seen this before. In my case (Using EF 6.1), it was because my Fluent API Mapping was set up like so:

// In my EntityTypeConfiguration<Status>
HasRequired(x => x.Member).WithMany().HasForeignKey(x => x.MemberID);

That code works perfectly fine, but it doesn't tell EF that my Member class's Collection Navigational Property Status ha been taken into account. So, while I explicitly handled the existence of a Member Navigational Property in my Status Class, I now left an orphaned related collection property. That orphaned property, being a collection, tells EF that my Status class needs to have a Foreign Key to it. So it creates that on the Status Class.

To fix it, I had to be 100% explicit.

HasRequired(x => x.Member).WithMany(x => x.Statuses).HasForeignKey(x => x.MemberID)

It could bee that your Statuses Collection property in Member needs an attribute telling it that it is already considered, and not to go auto-creating mappings. I don't know that attribute.