Entity framework conventions making unexpected tables

Question

I am modelling an e-learning system whereby a user can enroll on a course. A course can be made up of a series of content or a series of course sections which in turn contain content.

I have the models defined as follows:

public class Course
{
    [Key]
    public int CourseID { get; set; }

    //1 to many relationship
    public virtual ICollection<Content> Contents { get; set; }

    public virtual ICollection<CourseSection> CourseSections { get; set; }

    //many to many relationship
    public virtual ICollection<User> Users { get; set; }
}

public class CourseSection
{
    [Key]
    public int CourseSectionID { get; set; }

    public int CourseID { get; set; }

    public virtual ICollection<Content> Contents { get; set; }
}

public class Content
{
    [Key]
    public int ContentID { get; set; } 

    //many-to-many mappings
    public virtual ICollection<Course> Courses { get; set; }

    public virtual ICollection<CourseSection> CourseSections { get; set; }
}

After initializing my context I can see that there are two tables created in the database that do not relate to the models that I have created. The following two tables are created unexpectedly and I would like to understand why:

ContentCourse: Content_ContentID, Course_CourseID

CourseSectionContent: CourseSection_CourseSectionID, Content_ContentID

Once I noticed that these two tables were being created I decided to create mapping tables for CourseContent and SectionContent by adding the following two models:

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

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

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

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

Unfortunately these tables are still being created. Can anyone tell from my code where I am going wrong? Any help would be greatly appreciated.

score 1 · Accepted Answer · edited May 23 '17 at 12:05

You say 1 to many relationship (Course.Contents), but it isn't. Course and Content have a many-to-many relationship.

So, the tables are not created unexpectedly. They are created because the only way to model many-to-many associations in a relational database is by creating junction tables. You will notice that when you add existing Content items to e.g. Course.Contents, EF will insert records in ContentCourse, not in Course nor in Content.

The classes you defined yourself are not related to any other class in your class model. Therefore EF will not create tables for them and if it did they would just be disconnected tables.

If you'd decide to incorporate the junction tables into your conceptual model (= class model) the model would have to change quite a bit. For example for CourseContent:

public class CourseContent
{
    [Key, Column(Order = 0)]
    public int CourseID { get; set; }
    [ForeignKey("CourseID")]
    public virtual Course Course { get; set; }

    [Key, Column(Order = 1)]
    public int ContentID { get; set; }
    [ForeignKey("ContentID ")]
    public virtual Content Content { get; set; }
}

public class Course
{
    [Key]
    public int CourseID { get; set; }

    //1 to many relationship (now it is)
    public virtual ICollection<CourseContent> Contents { get; set; }

    ...
}

public class Content
{
    [Key]
    public int ContentID { get; set; } 

    //many-to-many mappings
    public virtual ICollection<CourseContent> Courses { get; set; }

    ...
}

If you want to hang on to the many to many associations but also want to control the names of the junction tables you can use fluent mapping like in this answer.

Cheers Gert good man helped me :) Knew I was missing a key step somewhere!! — Jay, Jul 10 '13 at 13:38

Entity framework conventions making unexpected tables

1 Answers1