Best resource for Online free Education
In this article, I am going to discuss the Relationships Between Entities in Entity Framework Database First Approach i.e. at the end of this article, you will understand how entity framework manages the relationships between entities. Please read our previous article where we discussed Entity Framework DbSet in detail.
Note: We are going to work with the same example that we created in our Introduction to Entity Framework Database First Video. Please read our introduction to Entity Framework Database First Video before proceeding to this Video.
Entity Framework – Relationships
In relational databases, the 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 are used to establish and enforce a link between the data in two tables. And relational databases there are three types of relationships between the database tables (One-to-One, One-to-Many, and Many-to-Many).
The Entity Framework supports three types of relationships similar to the database. They are as follows:
- One-to-One
- One-to-Many
- Many-to-Many
We have created an Entity Data Model for the EF_Demo_DB database in our Introduction to Entity Framework Database First Approach Video. The diagram below shows the visual designer for that Entity Data Model with all the entities and the relationships between them.
By looking at the above diagram, you might have some doubts. Lets proceed further and understand how each relationship (association) is being managed by the entity framework.
One-to-One Relationship in Entity Framework
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.
In the above figure, we have marked the relationship between Student and StudentAddress as a One-to-One relationship (zero or one). That means a student can have only one or zero addresses. Entity framework adds the Student reference navigation property into the StudentAddress entity, On the other hand, it also adds the StudentAddress navigation entity into the Student entity
The following is the auto-generated Student Entity by the Entity Framework. You can see in the below image; it has a property called StudentAddress.
The following is the auto-generated StudentAddress Entity by the Entity Framework. You can see in the below image; it has a property called Student.
Note: In the StudentAddress database table, we marked the StudentId column as both PrimaryKey and ForeignKey, which makes it a one-to-one relationship between the Student and StudentAddress entity.
One-to-Many Relationship in Entity Framework
A one-to-many relationship is the most common type of relationship between the tables or entities. 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 relationship between Standard and Teacher entities has a One-to-Many relationship which is marked by multiplicity where 1 is for One and * is for Many. That means one Standard can have many Teachers whereas one Teacher can associate with only one Standard.
To represent this one to many relationships, the Standard entity has the collection navigation property Teachers (please notice that its plural), which indicates that one Standard can have a collection of Teachers (many teachers). For better understanding, please have a look at the following auto-generated Standard entity model by Entity Framework.
On the other hand, the Teacher entity has a Standard navigation property (reference property), which indicates that the Teacher is associated with one Standard. The following is the auto-generated Teacher Entity by the Entity Framework. You can see in the below image; it has a property called Standard.
Note: In the Teacher database table, we marked the StandardId column ForeignKey, which makes it a one-to-many relationship between the Teacher and Standard entity.
We also have one-to-many relationships between Standard and Student (one Standard can have many Students whereas one Student can associate with only one Standard), Teacher and Course (one Teacher can have many Courses whereas one Course can associate with only one Teacher).
Many-to-Many Relationship in Entity Framework
The Student and Course entity have a Many-to-Many relationship which is marked by * multiplicity. That means one Student can enroll for many Courses and also, one Course can be taught to many students.
In the database, we have the StudentCourse joining table which includes the primary key of both the Student and Course tables. The Entity Framework represents many-to-many relationships by not having the entity set (DbSet property) for the joining table (in our case the joining table is StudentCourse) in the CSDL and visual designer. Instead, it manages this through mapping.
The Student entity includes the collection navigation property Courses, as well as the Course entity, includes the collection navigation property Students to represent the many-to-many relationship between them.
The following is the auto-generated Student Entity by the Entity Framework. You can see in the below image; it has the Courses collection navigation property.
The following is the auto-generated Course Entity by the Entity Framework. You can see in the below image; it has the Students collection navigation property.
Note: The Entity framework supports many-to-many relationships only when the joining table (StudentCourse in this case) does NOT include any columns other than PKs of both the tables i.e. Student and Course. If the join tables contain additional columns, then the EDM creates an entity for the middle table as well and you will have to manage CRUD operations for many-to-many entities manually.
Open EDM in XML view. You can see that SSDL (storage schema) has the StudentCourse entity set as shown in the below image.
The CSDL doesnt have a StudentCourse entity set. Instead, its being mapped in the navigation property of the Student and Course entities. MSL (C-S Mapping) has a mapping between Student and Course put into the StudentCourse table in the <AssociationSetMapping/> section as shown in the below image.
