Entity Framework code-first generating tables/keys in an unexpected manner

Question

I have many classes representing tables, but three are giving me headaches: Person, Task, and Role, here is their code:

public class Person : BaseModel
{
        public int Id { get; set; }
        public string FName { get; set; }
        public string LName { get; set; }
        public string Title { get; set; }

        public ICollection<TestEvent> TestEventsLed { get; set; }
        public ICollection<TestEvent> TestEventsCreated { get; set; }
        public ICollection<Program> ProgramsLed { get; set; }
        public ICollection<Task> TasksCreated { get; set; }
        public ICollection<PersonalEvent> PersonalEventsCreated { get; set; }

        public virtual ICollection<Role> RolesHeld { get; set; }
        public virtual ICollection<Task> TasksAssigned { get; set; }
}

public class Role : BaseModel
{
        public int Id { get; set; }
        public string Name { get; set; }

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

public class Task : BaseModel
{
        public int Id { get; set; }
        public string Name { get; set; }
        public DateTime SuspenseDatetime { get; set; }
        public DateTime CreatedDatetime { get; set; }
        public int CreatedById { get; set; }
        public bool Completed { get; set; }
        public bool Archived { get; set; }

        public Person CreatedBy { get; set; }

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

What I end up with is mostly what I wanted, except a few hiccups:

Expected:                                       Actual:
- People should have 0 foreign keys, just       - People has 1 FK and 1 extra column out of
  2 many-to-manys for RolesHeld and               nowhere: Task_Id and the FK is for that
  TasksAssigned                                   new column referencing Id in Tasks?

- Task should have 1 foreign key for            - Task has 2 extra columns out of nowhere
  CreatedById linked to a Person                  called Person_Id and Person_Id1 and then
                                                  identical foreign keys attached to them
                                                  (and it has the expected CreatedById FK)

- There should be a RolePersons table           - This part happened correctly and with the 
  with 2 FKs to represent the many-to-many        correct FKs to represent the many-to-many

- There should be a TaskPersons table           - There is no new table at all for this
  with 2 FKs to represent the many-to-many  

The weird thing is, I did some of these the same way (like the two many-to-many relationships) but then only 1 turned out correctly? Can you see what I did incorrectly?

Answer 1

Sometime default mapping is not what we want, so we have to explicitly say to EF what we need. Just add this method to your DbContext and it works as required:

protected override void OnModelCreating(DbModelBuilder modelBuilder)
{
    modelBuilder.Entity<Person>().HasMany(p => p.TasksAssigned).WithMany(t => t.PeopleAssigned);
    modelBuilder.Entity<Person>().HasMany(p => p.TasksCreated).WithRequired(t => t.CreatedBy).WillCascadeOnDelete(false);
}

Answer 2

Entity Framework do something by convention.

Look your Task class and Person class

public class Task : BaseModel
{
    public int Id { get; set; }
    public string Name { get; set; }
    public DateTime SuspenseDatetime { get; set; }
    public DateTime CreatedDatetime { get; set; }
    public int CreatedById { get; set; }
    public bool Completed { get; set; }
    public bool Archived { get; set; }

    public Person CreatedBy { get; set; }

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


public class Person : BaseModel
{
    public int Id { get; set; }
    public string FName { get; set; }
    public string LName { get; set; }
    public string Title { get; set; }

    public ICollection<TestEvent> TestEventsLed { get; set; }
    public ICollection<TestEvent> TestEventsCreated { get; set; }
    public ICollection<Program> ProgramsLed { get; set; }
    public ICollection<Task> TasksCreated { get; set; }
    public ICollection<PersonalEvent> PersonalEventsCreated { get; set; }

    public virtual ICollection<Role> RolesHeld { get; set; }
    public virtual ICollection<Task> TasksAssigned { get; set; }
}

In your Task Class you are putting Person object and as well as a collection of Person.That's the thing is the cause of your headache i guess.

If you need many to many relation between them,then you should not put this property inside your Task Class

 public Person CreatedById { get; set; }
 public Person CreatedBy { get; set; }

Or If you need one to many relation between them,then Remove this property form your Task class

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