Configure many-to-many relations in Entity Framework for a MySQL database

Entity Framework is an ORM framework for .NET applications. We specify the mapping between .NET classes and corresponding database tables. Tables are related to one another. The common relations are One-to-One, One-to-Many or Many-to-Many.

  • One-to-One: Record in TableA maps to a record in TableB.
  • One-to-Many: Record in TableA maps to multiple records in TableB.
  • Many-to-Many: Record in TableA maps to multiple records in TableB and one record in TableB maps to multiple records in TableA.

We will implement many-to-many relation between two tables using Entity Framework. For many-to-many relation, both tables have a foreign key.

Example Scenario

Consider two tables: Students and Courses which have many-to-many relation. A student can enrol for multiple courses. A course has many students. For our example, we have a StudentCourse table, sort of intermediary table, between Students and Courses. The StudentCourse table has an additional field – CourseScore. CourseScore is the score attained by a student in the course.

The Student class is shown below. We use Data annotations (or attributes) to create the mapping (instead of Fluent API).

[Table("student")]
public class Student
{
    [Key]
    [Column("idstudent")]
    public int Id { get; set; }
    [Column("name")]
    public string Name { get; set; }
    // Important - virtual
    public virtual ICollection<StudentCourse> Courses { get; set;}
}

The Student class maps to student table. The Id property maps to idstudent column. In addition, it is the primary key. The Courses collection has all the courses that the student is enrolled in.

The Course class is shown below.

[Table("course")]
public class Course
{
    [Key]
    [Column("idcourse")]
    public int Id { get; set; }
    [Column("name")]
    public string Name { get; set; }
    public virtual ICollection<StudentCourse> Students { get; set; }
}

The Course class is similar to the Student class. The Students collection has all the students enrolled for the course. Both Student and Course classes have the StudentCourse collection.

Many to many relation

The StudentCourse class associates Students and Course classes with many-to-many relation.

[Table("student_course")]
public class StudentCourse
{
    [Key]
    [Column("idstudent_course")]
    public int Id { get; set; }
    [Column("idstudent")]
    [ForeignKey("Student")]
    public int StudentId { get; set; }
    [Column("idcourse")]
    [ForeignKey("Course")]
    public int CourseId { get; set; }
    [Column("coursescore")]
    public int CourseScore { get; set; }
    
    public virtual Student Student { get; set; }
    public virtual Course Course { get; set; }
}

The Id property maps to idstudent_course column which is also the primary key for the table. There are two foreign keys in the table. A foreign key ensures referential integrity. We specify a foreign key in Entity Framework by using the ForeignKey attribute. It takes a parameter which is the name of the associated table. In our case, the StudentId property maps to idstudent column in the student_course table. But more importantly, it is a foreign key with a relation to the Student class. Every StudentId should be available as a primary key in the Student table.

The following code shows the usage of these classes.

var student = context.Students.SingleOrDefault(s => s.Id == id);
foreach(var course in student.Courses)
{
    Console.WriteLine("Scored {0} in {1}", 
                    course.CourseScore, 
                    course.Course.Name);
}

Entity framework makes the code more maintainable and easy to understand.

Related Posts

Leave a Reply

Your email address will not be published.