Many-to-Many + additonal column EF design, it is adding an additional column to my table

Question

I want the following table structure:

Person
-person_id

Company
-company_id

Company_Person
-person_id
-company_id
-other_column

Location
-id

Currently my EF is resulting in a 'company_id' column in the Person table also as a FK.

My models look like:

public class Person 
{
        [Key]
        [DatabaseGenerated(DatabaseGeneratedOption.Identity)]
        public int PersonId { get; set; }

        public int LocationId {get;set;}

        public virtual Location Location {get; set; }           

        public virtual ICollection<CompanyPerson> CompanyPersons {get; set;}
}


public class Company
{
        [Key]
        [DatabaseGenerated(DatabaseGeneratedOption.Identity)]
        public int CompanyId { get; set; }

        public virtual ICollection<CompanyPerson> CompanyPersons {get; set;}
}


[Table("Company_Person")]
public class CompanyPerson
{
    [Key, Column(Order = 0)]
    public int PersonId { get; set; }
    [Key, Column(Order = 1)]
    public int CompanyId { get; set; }

    public bool IsActive { get; set; }

    public virtual Person Person { get; set; }
    public virtual Company Company { get; set; }
}


public class Location 
{
    [Key]
    [DatabaseGenerated(DatabaseGeneratedOption.Identity)]
     public int id {get;set;}
     public virtual ICollection<Person> Persons {get;set;}
 }

I followed the same pattern as in here: Create code first, many to many, with additional fields in association table

How can I get that extra CompanyId column from being generated in the Person table?

Update

Ok I figured it out, and it turns out it was another association that I didn't post (my bad once again).

In my Company model I had this which I commented out and it generated the correct table. I still want this association so can someone tell me what is why this is happening?

public class Company
{
        [Key]
        [DatabaseGenerated(DatabaseGeneratedOption.Identity)]
        public int CompanyId { get; set; }

        public virtual ICollection<CompanyPerson> CompanyPersons {get; set;}
        public virtual ICollection<History> Histories {get; set; }
}


public class History
{
    [Key]
    [DatabaseGenerated(DatabaseGeneratedOption.Identity)]
    public int Id { get; set; }

    [ForeignKey("Company")]
    public int CompanyId { get; set; }

    public virtual Company Company { get; set; }
}

So when I commented out the everything in the History model except for the Id property, and the Company.History property it generated the table structure I was expecting.

Answer 1

I think that EF is treating your CompanyPerson property as a complex type, because essentially, it can't actually create a M2M relationship with what you've given it. Whether that's actually the problem or not, either way you'll need to fix your CompanyPerson properties to be:

public virtual ICollection<CompanyPerson> CompanyPersons { get; set; }

UPDATE

The oddest part is that your History class would perfectly explain the issue if it only actually was defined as:

public class History : Person

That's why I asked you about any subclasses of Person because EF's default behavior with inheritance is to use TPH (table per hierarchy). In other words, it will simply add all properties of all subclasses to the base class' table, instead of creating a table for each subclass. Plainly and simply, the only source of this column you aren't expecting is going to be one of either:

1. Company, or some subclass of Company has direct relationship to Person (not through CompanyPerson) and it's configured to be a one-to-one.

2. Person or some subclass of Person has a relationship to Company.

Answer 2

Ok I found the problem, and surprise surprise the real bug was with me!

In my Company table had this:

public virtual ICollection<Person> Histories { get; set; }

if you didn't catch that, the type should be History and not Person!

public virtual ICollection<History> Histories { get; set; }

Thanks for all that helped with this!