EF-code-first many-to-many related objects generates two different SQL tables (MVC3)

Question

I have read this great Q&A and I tried to make something similar. My Model classes are:

public class Person
{
    public int PersonId { get; set; }
    public string FirstName { get; set; }
    public string LastName { get; set; }

    public ICollection<Course> CoursesAttending { get; set; }

    public Person()
    {
        this.CoursesAttending = new List<Course>();
    }
}

public class Course
{
    public int CourseId { get; set; }
    public string Title { get; set; }

    public ICollection<Person> Students { get; set; }
}

public class PersonCourse
{
    [Key, Column(Order = 0)]
    public int PersonID { get; set; }
    [Key, Column(Order = 1)]
    public int CourseID { get; set; }

    public virtual Person Student { get; set; }
    public virtual Course StudentCourse { get; set; }

    public int Mark { get; set; }
    public string Comment { get; set; }
}

public class SchoolContext : DbContext
{
    public DbSet<Course> Courses { get; set; }
    public DbSet<Person> People { get; set; }
    public DbSet<PersonCourse> PersonCourseLinks { get; set; }

    public SchoolContext()
        : base("ManyToManyTest")
    {
    }
}

Now, I try to add a new person and add a new course to his courses list:

[HttpPost]
    public ActionResult Create(Person person)
    {
        if (ModelState.IsValid)
        {
            Course studentCourse;
            try
            {
                studentCourse = db.Courses.ToList<Course>().First();
            }
            catch
            {
                studentCourse = new Course() { Title = "HTML" };
            }

            person.CoursesAttending.Add(studentCourse);
            db.People.Add(person);
            db.SaveChanges();
            return RedirectToAction("Index");  
        }

        return View(person);
    }

Everything goes well, but when I open my created database, I see there are 2 link tables for the Person and Course classes - PersonCourses(with fields: PersonID, CourseID, Mark, Comment) and PersonCourse1(with fields: PersonID, CourseID), and only PersonCourse1 has rows (actually one row). Why does it happen? Did I do something not correct? I expect to see only one link table - the PersonCourses table...

Depending on what version of EF you're using, it creates the many-many bridge table for you (PersonCourse). It's also worth noting that you don't need to initialise the collections to list in your constructor as the DbContext should take care of that for you. Have you see [This Article](http://weblogs.asp.net/scottgu/archive/2010/07/23/entity-framework-4-code-first-custom-database-schema-mapping.aspx)? — Basic, May 24 '12 at 13:47

score 1 · Answer 1 · answered May 24 '12 at 13:52

I expect to see only one link table - the PersonCourses table

Then you have to link PersonCourse entity with Person entity though CoursesAttending navigational property. Same thing has to be done to Course entity.

public class Person
{
    public int PersonId { get; set; }
    public string FirstName { get; set; }
    public string LastName { get; set; }

    public ICollection<PersonCourse> CoursesAttending { get; set; }

    public Person()
    {
        this.CoursesAttending = new List<PersonCourse>();
    }
}

public class Course
{
    public int CourseId { get; set; }
    public string Title { get; set; }

    public ICollection<PersonCourse> Students { get; set; }
}

score 0 · Answer 2 · answered May 24 '12 at 13:57

Give foreign key associations to the foreign key properties in your gerund table:

public class PersonCourse
{
    [Key, Column(Order = 0)]
    public int PersonID { get; set; }
    [Key, Column(Order = 1)]
    public int CourseID { get; set; }

    [ForeignKey("PersonID")]
    public virtual Person Student { get; set; }

    [ForeignKey("CourseID")]
    public virtual Course StudentCourse { get; set; }

    public int Mark { get; set; }
    public string Comment { get; set; }
}

score 0 · Answer 3 · answered Oct 08 '13 at 10:46

Just so you now why your code wasn't working as expected:

public class PersonCourse
{
    [Key, Column(Order = 0)]
    public int PersonID { get; set; }
    [Key, Column(Order = 1)]
    public int CourseID { get; set; }

    public virtual Person Person { get; set; } // Follow built in conventions
    public virtual Course Course { get; set; }

    public int Mark { get; set; }
    public string Comment { get; set; }
}

This now follows the built in Entity Framework convention of having

public int [ClassName][Id] or [Id] //Database Foreign Key column

and

public NavigationProperty [ClassName] // Navigation Property

EF-code-first many-to-many related objects generates two different SQL tables (MVC3)

3 Answers3