Entity Framework - Relationships

---

In relational databases, relationship is a situation that exists between relational database tables through foreign keys. A Foreign Key (FK) is a column or combination of columns that is used to establish and enforce a link between the data in two tables. The following diagram contains three tables.

• Student
• Course
• Enrollment

In the above diagram, you can see some sort of association/relationship between tables. There are three types of relationships between tables and the relationship between different tables depends on how the related columns are defined.

• One-to-Many Relationship
• Many-to-Many Relationship
• One-to-One Relationship

One-to-Many Relationship

• A one-to-many relationship is the most common type of relationship.

• In this type of relationship, a row in table A can have many matching rows in table B, but a row in table B can have only one matching row in table A.

• The foreign key is defined in the table that represents the many end of the relationship.

• For example, in the above diagram Student and Enrollment tables have one-tomany relationship, each student may have many enrollments, but each enrollment belongs to only one student.

In entity framework, these relationship can be created with code as well. Following is an example of Student and Enrollment classes which are associated with one to many relationship.

public class Student {
   public int ID { get; set; }
   public string LastName { get; set; }
   public string FirstMidName { get; set; }
   public DateTime EnrollmentDate { get; set; }
	
   public virtual ICollection<Enrollment> Enrollments { get; set; }
}

public class Enrollment {

   public int EnrollmentID { get; set; }
   public int CourseID { get; set; }
   public int StudentID { get; set; }
	
   public Grade? Grade { get; set; }
   public virtual Course Course { get; set; }
   public virtual Student Student { get; set; }
}

In the above code, you can see that Student class contains the collection of Enrollment, but Enrollment class has a single Student Object.

Many-to-Many Relationship

In many-to-many relationship, a row in table A can have many matching rows in table B, and vice versa.

• You can create such a relationship by defining a third table, called a junction table, whose primary key consists of the foreign keys from both table A and table B.

• For example, the Student and Course tables have many-to-many relationship that is defined by one-to-many relationship from each of these tables to the Enrollment table.

The following code contains the Course class and the above two classes, i.e., Student and Enrollment.

public class Course {
   [DatabaseGenerated(DatabaseGeneratedOption.None)]
	
   public int CourseID { get; set; }
   public string Title { get; set; }
	
   public int Credits { get; set; } 
   public virtual ICollection<Enrollment> Enrollments { get; set; }
}

You can see that both Course class and Student class have collections of Enrollment objects which makes many-to-many relationship via junction class Enrollment.

One-to-One Relationship

• In a one-to-one relationship, a row in table A can have no more than one matching row in table B, and vice versa.

• A one-to-one relationship is created if both of the related columns are primary keys or have unique constraints.

• In a one-to-one relationship, the primary key acts additionally as a foreign key and there is no separate foreign key column for either table.

This type of relationship is not common because most information related in this way would be all in one table. You might use a one-to-one relationship to −

• Divide a table with many columns.
• Isolate part of a table for security reasons.
• Store data that is short-lived and could be easily deleted by simply deleting the table.
• Store information that applies only to a subset of the main table.

The following code is to add another class name StudentProfile which contains the student email id and password.

public class Student {
   public int ID { get; set; }
   public string LastName { get; set; }
   public string FirstMidName { get; set; }
   public DateTime EnrollmentDate { get; set; }
	
   public virtual ICollection<Enrollment> Enrollments { get; set; }
   public virtual StudentProfile StudentProfile { get; set; }
}

public class StudentProfile {

   public StudentProfile() {}
   public int ID { get; set; }
   public string Email { get; set; }
   public string Password { get; set; }
	
   public virtual Student Student { get; set; }
}

You can see that Student entity class contains StudentProfile navigation property and StudentProfile contains Student navigation property.

Each student has only one Email and password to login in university domain. These information can be added to Student table but for security reasons it is separated to another table.