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.