Creating a Many To Many Mapping Using Code First

August 1, 2011

Creating a Many To Many Mapping Using Code First

Creating a Many To Many Mapping Using Code FirstToday I got a question about how to create a many to many mapping using Entity Framework Code First. The question was how to create the mapping with an existing database. Here is an explanation how to achieve that.

Creating Many To Many Mapping

Sometimes there is a need to create a many to many relation in the database. When we want to achieve that we will create a relation table which will hold the primary key from every table in the many to many relation. Here is an example of a simple many to many relation that can exist in a database:

DatabaseDiagram

In Code First, when we want to create such a relation all we have to do is to create the relevant entities which will hold an ICollection in each side of the relation and create the relevant DbContext. Here is an example for a many to many relation in Code First:

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()
  {
    CoursesAttending = new HashSet<Course>();
  }
}
 
public class Course
{
  public int CourseId { get; set; }
  public string Title { get; set; }
 
  public ICollection<Person> Students { get; set; }
 
  public Course()
  {
    Students = new HashSet<Person>();
  }
}
 
public class SchoolContext : DbContext
{
  public DbSet<Course> Courses { get; set; }
  public DbSet<Person> People { get; set; }
 
  public SchoolContext()
    : base("MyDb")
  {
  }
}

Simple as that. The problem starts when we have an existing database where the column names or the relation name aren’t generated by Code First convention engine. In the previous figure of the database, Code First will generate the column names – Person_PersonId and Course_CourseId which don’t match to the existing table.

Using Code First Fluent API to Create a Many To Many Relation Mapping

So we have a small problem with existing relation tables. We can solve the problem by overriding the OnModelCreating method and adding the mapping using Code First fluent API. Here is an example of such a mapping to solve the previous model’s problem:

public class SchoolContext : DbContext
{
  public DbSet<Course> Courses { get; set; }
  public DbSet<Person> People { get; set; }
 
  public SchoolContext()
    : base("MyDb")
  {
  }
 
  protected override void OnModelCreating(DbModelBuilder modelBuilder)
  {
    modelBuilder.Entity<Course>().
      HasMany(c => c.Students).
      WithMany(p => p.CoursesAttending).
      Map(
       m =>
       {
         m.MapLeftKey("CourseId");
         m.MapRightKey("PersonId");
         m.ToTable("PersonCourses");
       });
  }
}

In the OnModelCreating you will use the HasMany to indicate that course has many students and WithMany to indicate that a person can attend a lot of courses. The Map method will tell Code First how to build the relation table with the relevant name (using ToTable mapping) and with the relevant keys (MapLeftKey and MapRightKey).

Summary

Creating a many to many relation with Code First is easy. You need to get used to the fluent API if you have an existing database but the API is self explanatory so it makes your job easier.

Add comment
facebook linkedin twitter email

Leave a Reply

Your email address will not be published.

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong>

*