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 estabpsh and enforce a pnk 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.

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

pubpc class Enrollment {

   pubpc int EnrollmentID { get; set; }
   pubpc int CourseID { get; set; }
   pubpc int StudentID { get; set; }
	
   pubpc Grade? Grade { get; set; }
   pubpc virtual Course Course { get; set; }
   pubpc 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.

pubpc class Course {
   [DatabaseGenerated(DatabaseGeneratedOption.None)]
	
   pubpc int CourseID { get; set; }
   pubpc string Title { get; set; }
	
   pubpc int Credits { get; set; } 
   pubpc 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-pved and could be easily deleted by simply deleting the table.

Store information that apppes 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.

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

pubpc class StudentProfile {

   pubpc StudentProfile() {}
   pubpc int ID { get; set; }
   pubpc string Email { get; set; }
   pubpc string Password { get; set; }
	
   pubpc 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.