Entity Framework DB First – Managing Entity Relationships
In this article, we will see how to model relationships between tables in our Entity Data Model and use Entity Framework to work with these related entities. We will look at the types of relationship between the tables, and then we will see how this can be modeled in our Entity Data Model.
Understanding database relationships
In a very simple application, it is possible to have a single table. However, as the complexity of the application grows and the need to store data increases, the number of tables also increases. If we are trying to store a complex related set of data, then these tables will also relate to each other using foreign keys. To model our database correctly and have a properly normalized database, we will end up with a set of related tables. Let’s look at the various types of relationship that can exist between tables in a database.
There are three types of relationship that can exist between tables:
One-to-many relationship
One-to-one relationship
Many-to-many relationship
Modeling a one-to-many relationship
Now, let’s start our discussion by looking at the models that have one-to-many relationships.
If we look at the Employer and Employee relation, we can see that the Entity Frameworks Entity Data Model generator was able to figure out the one-to-many relationship between the database tables and was able to create the same relationship in the conceptual Entity Data Model as well.
Also, the relationship in the Entity Data Model is represented by 1 with the entity at the one end of the relationship and by * with the entity on the many end of the relationship.
If we look at our Entity Data Model, we can see that the Entity Framework created Navigation Properties in the entities. The entity at the one end of the relationship will have a navigation property of the collection type to hold multiple entities on the many end. Similarly, the Entity on the many end of the relation will have a navigation property that will let us access the entity related to it on the one end.
In our current example, the Employer model contains a navigation property, Employees, using which we can retrieve/update the list of employees associated with Employer. The Employee model also contains a navigation property, Employer, using which we can retrieve/update the Employer information for a given Employee.
If we look at the EDMX XML markup, we can see that the SSDL contains the information about the one-to-many relationship between the tables.
The same relationship is also mentioned in the CSDL to indicate the one-to-many relationship between the entities.
The MSL in this case will simply contain the mapping information of both the tables involved in the relationship to respective database columns.
Modeling a one-to-one relationship using Entity Framework
Now, let’s look at the models that have one-to-one relationships. The Employees table and Passports table has a one-to-one relationship. If we look at the Employee and Passport relationship, we can see that the Entity Framework Entity Data Model generator was able to figure out the one-to-one relationship between these tables and was able to create the same relationship in the conceptual Entity Data Model as well.
lso, one-to-one relationships in the Entity Data Model are represented by 1 with the Employee entity, because this is the main entity in the relation. The Passport entity shows 0..1, which means that there could be either 0 or 1 entries in the Passport table for each employee.
Entity Framework also created Navigation Properties in the entities. Navigation properties are properties that link the entities to related entities. Using navigation properties, we can access the entities related to a given entity. The Employee model contains a navigation property, Passport, using which we can access the related Passport entity and similarly, the Passport model contains a navigation property, Employee, using which we can access the Employee object related to Passport.
If we look at the EDMX XML markup, we can see that the SSDL contains the information about this one-to-one relationship between the tables.
The same relationship is also mentioned in the CSDL to indicate the one-to-one relationship between these entities.
MSL in this case will simply contain the mapping information of both the tables involved in the relationship to the respective database columns.
Modeling a many-to-many relationship using Entity Framework
Now, let’s look at the models that have many-to-many relationships. The Employees table and the Reports table have a many-to-many relationship. If we look at the Employer and Report relationship, we can see that the Entity Framework Entity Data Model generator was able to figure out this many-to-many relationship between these tables and was able to create the same relationship in the conceptual Entity Data Model as well.
The important thing to notice here is that the Entity Framework was also able to understand that the EmployeeReports table was created solely to manage a many-to-many relationship, and is actually not needed in the conceptual model.
Also, the relationship in the Entity Data Model is represented by * with both the entities to denote a many-to-many relationship. Entity Framework also created Navigation Properties in the entities. The Employee model contains a Navigation property, Reports, using which we can access the related records from the Reports table and similarly, the Report model contains a navigation property, Employees, using which we can access Employee records related to Reports.
If we look at the EDMX XML markup in the following diagram, we can see that the SSDL contains information about this many-to-many relationship between the tables.
The same relationship is also mentioned in the CSDL to indicate the many-to-many relationship between these entities.
Now, as the Entity Framework is not showing the joining table and is able to create navigation properties directly in the entities participating in the many-to-many relationship, the mapping part of the EDMX contains information about the joining table that is responsible for facilitating this many-to-many relationship. This is done by defining AssociationSet in the mapping as follows:
Using navigation properties for data access
Retrieving a specific item
If we have the entity at the many end of the application and we want to retrieve the entity at the one end, we can use the navigation property to do this. From our sample application’s perspective, we will see how we can retrieve the employer for a given employee. This can be achieved by using the Employer navigation property, available in the Employer object. Let’s see how this can be done in code:
public Employer GetEmployerByEmployee(int employeeId)
{
Employer employer = null;
using (SampleDbEntities db = new SampleDbEntities())
{
Employee employee = db.Employees.Find(employeeId);
if (employee != null)
{
employer = employee.Employer;
}
}
return employer;
}
In the preceding code, we create a DbContext object, and using this context object, fetch the Employee by the given employeeId. If we find an Employee object for this, we retrieve the Employer object associated with it. We do this by using the navigation properties. Internally, what Entity Framework does is that it creates a SQL query to fetch the employer associated with the employee with a given employeeId.
Retrieving a list of items
Let’s start by looking at how we can use the entity at the one end of the relationship and retrieve the list of all the related entities at the many end. From our sample application’s perspective, we will see how we can retrieve a list of employees if we have the employer object. This can be achieved by using the navigation property, Employees, available in the Employer object. Let’s see how this can be done in code:
public List
{
List
using (SampleDbEntities db = new SampleDbEntities())
{
Employer employer = db.Employers.Find(employerId);
if (employer != null)
{
employeeList = employer.Employees.ToList();
}
}
return employeeList;
}
In the preceding code, we create the DbContext object and using this object, we fetch the Employer by the given employerId function. If we find an Employer object with this ID, we retrieve the Employees associated with it. Internally, Entity Framework is generating a SQL to fetch all the employees associated with this employerid and return all the records as a collection of Employee entities.
Adding an item
If we want to add an entity in a relationship, we can simply assign it or add it to the Navigation property. For example, if we want to associate Employee with Employer, we just need to add the employee object to the employer’s Employee collection. Let’s see how this can be done in code:
public void AddEmployee(Employee employee, int employerId)
{
using (SampleDbEntities db = new SampleDbEntities())
{
Employer employer = db.Employers.Find(employerId);
employer.Employees.Add(employee);
db.SaveChanges();
}
}
In the preceding code, we create the DbContext object and using this object, we fetch Employer by the given employerId. If we find an Employer object with this ID, we add the Employee object to the employer’s Employee collection and finally save the changes to database with db.SaveChanges();. What happens here is that if this Employee entity points to an existing Employee record in the table, the EmployerId value for that employee record will be updated to the ID of the new Employer. If this Employee record does not exist in the database, then a new record for this Employee will be created, and it will have the EmployerId of the given Employer object.
Updating an item
Now, let’s say that we want to update a related entity using the main entity, that is, the entity that is associated via the navigation property with the current entity we are using. For example, we want to update an Employee record using an Employer object that is related to this employee. Let’s see how this can be done:
public void UpdateEmployee(Employee modifiedEmployee, int employerId)
{
using (SampleDbEntities db = new SampleDbEntities())
{
Employer employer = db.Employers.Find(employerId);
Employee employee = employer.Employees.FirstOrDefault (emp => emp.ID == newEmployee.ID);
if (employee != null)
{
employee.EmployeeName = modifiedEmployee.EmployeeName;
}
else
{
employer.Employees.Add(employee);
}
db.SaveChanges();
}
}
In the preceding code, we create the DbContext, and using this object, we fetch the Employer by the given employerId. If we find an Employer object with this ID, we find the existing Employee by using the ID in the new Employee object. Once the Employee is found, we update the object with new values and save the changes to the database with db.SaveChanges();.
Deleting an item
Now, let’s say that we want to delete an associated entity from the many end provided that we have the entity at the one end and the ID of one of the many elements to be deleted. From our sample application’s perspective, let’s see how we can delete an Employee with a given ID by using the Employer entity:
public void DeleteEmployee(int employeeId, int employerId)
{
using (SampleDbEntities db = new SampleDbEntities())
{
Employer employer = db.Employers.Find(employerId);
Employee employee = employer.Employees.FirstOrDefault(emp => emp.ID == employeeId);
if(employee != null)
{
employer.Employees.Remove(employee);
db.SaveChanges();
}
}
}
Let’s try to understand the preceding code:
Create DbContext:
SampleDbEntities db = new SampleDbEntities()
Fetch the Employer by the given employerId:
Employer employer = db.Employers.Find(employerId);
Find the existing Employee by using the ID in the new Employee object:
Employee employee = employer.Employees.FirstOrDefault(emp => emp.ID == employeeId);
If the Employee is found, delete Employee:
employer.Employees.Remove(employee);
Save the changes. Please note that it is very important to call save changes since otherwise there will be no modifications to the data.db.SaveChanges();
Entity Framework – behind the scenes
Let’s try to understand how Entity Framework used these navigation properties to retrieve the data. Let’s look at the same example that we saw while retrieving Employer by using the Employee entity. In this scenario, our Employee entity contains a navigation property, Employer, using which we can access Employer related to Employee. The Employer entity also contains a navigation property, Employees, which will let us access the employees related to the given employer. So, if we need to access Employer related to Employee, it can be done as follows:
using (SampleDbEntities db = new SampleDbEntities())
{
Employee employee = db.Employees.SingleOrDefault
(employeeId);
if (employee != null)
{
employer = employee.Employer;
}
}
What happens behind the scenes is that Entity Framework generates SQL based on our queries and retrieves the data from the tables.
Let’s see what Entity Framework is doing behind the scenes to make this work:
Analyze our object query. In our example, we are simply querying all the results, but this could very well be a complicated LINQ query as well.
Check the database mapping of the Employee entity.
Use the navigation property and find the database mappings of the Employer entity.
Use the one-to-many relationship to create an Inner Join statement.
Generate the appropriate SQL and retrieve the data.
Populate the retrieved data into the Employer and Employee models.
If we look at the generated SQL, it will look like this:
SELECT
..[Extent2].[ID] AS [ID],
..[Extent2].[EmployerName] AS [EmployerName]
FROM
..[dbo].[Employees] AS [Extent1]
INNER JOIN
..[dbo].[Employers] AS [Extent2] ON [Extent1].[EmployerId] = [Extent2].[ID]
WHERE
..([Extent1].[ID] = @p__linq__0) AND (@p__linq__0 IS NOT NULL)
References:
Mastering Entity Framework
By: Rahul Rahul Rajat Singh Singh
