Before proceeding to this article, please go through my previous article:

In this article we are going to learn how to handle the related entities and the application of using the DTO classes in ASP.NET WEB API with Entity Framework

GET :/api/employees -> action in Employees Controller, gives the employee details

The response of the /api/employee service, as shown in figure 1,

From the above image we can notice that we are getting null value for department. This is because Entity Framework is not loading the related department entities.

The following trace log of the SQL query confirms this:

  1. {SELECT [Extent1].[EmployeeID] AS [EmployeeID], [Extent1].[FirstName] AS [FirstName], [Extent1].[LastName] AS [LastName], [Extent1].[DepartmentID] AS [DepartmentID] FROM [dbo].[Employees] AS [Extent1]}

The SELECT statement take from the Employee table, and does not reference the Department table.

Now Eager Loading and the lazy loading comes into the picture.

Eager Loading

Entity Framework loads related entities as part of the initial database query, Replace get Action in Employees Controller code to the following code,

  1. public IQueryable<Employees> GetEmployees()
  3. {
  4. return db.Employees.Include(dept=>dept.Department);
  5. }

The trace log of the SQL query,

  1. {SELECT [Extent1].[EmployeeID] AS [EmployeeID], [Extent1].[FirstName] AS [FirstName], [Extent1].[LastName] AS [LastName], [Extent1].[DepartmentID] AS [DepartmentID], [Extent2].[DepartmentID] AS [DepartmentID1], [Extent2].[DepartmentName] AS [DepartmentName] FROM [dbo].[Employees] AS [Extent1] INNER JOIN [dbo].[Departments] AS [Extent2] ON [Extent1].[DepartmentID] = [Extent2].[DepartmentID]}
From the above log we can conclude that Entity Framework performed a join on the Employees and department table.
Now, the response of the /api/employee service as shown in the following figure 2,
We can achieve the same result using the lazy loading,
Lazy Loading

To enable the lazy loading, make the navigation property virtual as in the following code,

  1. public class Employees
  2. {
  3. [Required]
  4. [Key]
  5. public int EmployeeID
  6. {
  7. get;
  8. set;
  9. }
  11. [Required]
  12. public string FirstName
  13. {
  14. get;
  15. set;
  16. }
  18. public string LastName
  19. {
  20. get;
  21. set;
  22. }
  24. public int DepartmentID
  25. {
  26. get;
  27. set;
  28. } //Foreign Key
  29. public virtual Department Department
  30. {
  31. get;
  32. set;
  33. } //Navigation Property
  35. }

In Employee controller replace the GET action method with the following code:

  1. var Employee = db.Employees.ToList();
  2. var dept = Employee[0].Department;
The Department property on Employee[0] causes Entity Framework to query the database for the Department, The trace log of the SQL query:
  1. {
  2. SELECT[Extent1].[EmployeeID] AS[EmployeeID], [Extent1].[FirstName] AS[FirstName], [Extent1].[LastName] AS[LastName], [Extent1].[DepartmentID] AS[DepartmentID] FROM[dbo].[Employees] AS[Extent1]
  3. }
  5. {
  6. SELECT[Extent1].[DepartmentID] AS[DepartmentID], [Extent1].[DepartmentName] AS[DepartmentName] FROM[dbo].[Department] AS[Extent1] where[Extent1].[DepartmentID] = @EntityKeyValue1
  7. }
  9. {
  10. SELECT[Extent1].[DepartmentID] AS[DepartmentID], [Extent1].[DepartmentName] AS[DepartmentName] FROM[dbo].[Department] AS[Extent1] where[Extent1].[DepartmentID] = @EntityKeyValue1
  11. }
  14. {
  15. SELECT[Extent1].[DepartmentID] AS[DepartmentID], [Extent1].[DepartmentName] AS[DepartmentName] FROM[dbo].[Department] AS[Extent1] where[Extent1].[DepartmentID] = @EntityKeyValue1
  16. }

Lazy loading in the Entity Framework will send a query each times it retrieves a related entity.

Circular References:

We have defined a navigation property on the Employee class for the Employee-department relationship, suppose if we add the navigation property to the department class it will create an issue when we serialize the models. If we load the related data, it creates a circular object graph.

  1. public class Department
  2. {
  3. [Required]
  4. public int DepartmentID
  5. {
  6. get;
  7. set;
  8. }
  9. [Required]
  10. public string DepartmentName
  11. {
  12. get;
  13. set;
  14. }
  15. public ICollection < Employees > Employees
  16. {
  17. get;
  18. set;
  19. }
  21. }

Now, the response of the /api/employee service as shown in figure 3,



We can overcome this issue by using Data Transfer Objects (DTOs).

Data Transfer Objects (DTOs):
A DTO is an object that defines how the data will be sent over the network, Create a new folder in project and name it DTO, right click on the DTO folder and create two classes and name it EmployeeDetailDTO and EmployeeDTO respectively.

Code in EmployeeDetailDTO.cs,

  1. public class EmployeeDetailDTO
  2. {
  3. public int EmployeeID
  4. {
  5. get;
  6. set;
  7. }
  8. public string FristName
  9. {
  10. get;
  11. set;
  12. }
  14. public string LastName
  15. {
  16. get;
  17. set;
  18. }
  20. public string DepartmentName
  21. {
  22. get;
  23. set;
  24. }
  25. public int DepartmentID
  26. {
  27. get;
  28. set;
  29. }
  31. }

Code in EmployeeDTO.cs,

  1. public class EmployeeDTO
  2. {
  3. public int EmployeeID
  4. {
  5. get;
  6. set;
  7. }
  8. public string FristName
  9. {
  10. get;
  11. set;
  12. }
  13. public string DepartmentName
  14. {
  15. get;
  16. set;
  17. }
  18. }

Now let we change the API actions in the Employees Controller:

GET action code

/api/employee
  1. public IQueryable < EmployeeDTO > GetEmployees()
  2. {
  3. var employeedetail = from s in db.Employees
  4. select new EmployeeDTO()
  5. {
  6. EmployeeID = s.EmployeeID,
  7. FristName = s.FirstName,
  8. DepartmentName = s.Department.DepartmentName
  10. };
  11. return employeedetail;
  13. }

Response

GET action with id

/api/employee /{emplyeeid}
  1. [ResponseType(typeof(EmployeeDetailDTO))]
  2. public async Task < IHttpActionResult > GetEmployees(int id)
  3. {
  4. EmployeeDetailDTO employees = await db.Employees.Include(s => s.Department).Select(
  5. b => new EmployeeDetailDTO()
  6. {
  7. EmployeeID = b.EmployeeID,
  8. FristName = b.FirstName,
  9. LastName = b.LastName,
  10. DepartmentName = b.Department.DepartmentName,
  11. DepartmentID = b.DepartmentID
  12. }).SingleOrDefaultAsync(c => c.EmployeeID == id);
  15. if (employees == null)
  16. {
  17. return NotFound();
  18. }
  20. return Ok(employees);
  21. }
Response
POST action

POST: /api/employee - This service is used to add the employee records,
  1. [ResponseType(typeof(Employees))]
  2. public async Task < IHttpActionResult > PostEmployees(Employees employees)
  3. {
  4. if (!ModelState.IsValid)
  5. {
  6. return BadRequest(ModelState);
  7. }
  9. db.Employees.Add(employees);
  10. await db.SaveChangesAsync();
  11. db.Entry(employees).Reference(s => s.Department).Load();
  13. var StoreEmp = new EmployeeDTO()
  14. {
  15. EmployeeID = employees.EmployeeID,
  16. FristName = employees.FirstName,
  17. DepartmentName = employees.Department.DepartmentName
  19. };
  22. return CreatedAtRoute("DefaultApi", new
  23. {
  24. id = employees.EmployeeID
  25. }, StoreEmp);
  26. }

Response

PUT Action

PUT: /api/employee - This service is used to update the employee records,
  1. [ResponseType(typeof(void))]
  2. public async Task < IHttpActionResult > PutEmployees(int id, Employees employees)
  3. {
  4. if (!ModelState.IsValid)
  5. {
  6. return BadRequest(ModelState);
  7. }
  9. if (id != employees.EmployeeID)
  10. {
  11. return BadRequest();
  12. }
  14. db.Entry(employees).State = EntityState.Modified;
  16. try
  17. {
  18. await db.SaveChangesAsync();
  21. } catch (DbUpdateConcurrencyException)
  22. {
  23. if (!EmployeesExists(id))
  24. {
  25. return NotFound();
  26. } else
  27. {
  28. throw;
  29. }
  30. }
  32. return StatusCode(HttpStatusCode.NoContent);
  33. }

Response

From the above image it is clear that we are updating the Last Name from Hill to Miller.

Result

This article is going long and long, there are more things need to be discussed regarding this topic, so in part- 3 let me share something about routing in ASP.NET WEB API.

I hope you have enjoyed this article. Your valuable feedback, question, or comments about this article are always welcomed.