ASP.NET

ASP.NET WEB API With Entity Framework 6 Code First Technique - Part 2

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()  
  2.   
  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.     }  
  10.   
  11.     [Required]  
  12.     public string FirstName   
  13.     {  
  14.         get;  
  15.         set;  
  16.     }  
  17.   
  18.     public string LastName   
  19.     {  
  20.         get;  
  21.         set;  
  22.     }  
  23.   
  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    
  34.   
  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. }  
  4.   
  5. {  
  6.     SELECT[Extent1].[DepartmentID] AS[DepartmentID], [Extent1].[DepartmentName] AS[DepartmentName] FROM[dbo].[Department] AS[Extent1] where[Extent1].[DepartmentID] = @EntityKeyValue1  
  7. }  
  8.   
  9. {  
  10.     SELECT[Extent1].[DepartmentID] AS[DepartmentID], [Extent1].[DepartmentName] AS[DepartmentName] FROM[dbo].[Department] AS[Extent1] where[Extent1].[DepartmentID] = @EntityKeyValue1  
  11. }  
  12.   
  13.   
  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.     }  
  20.   
  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.     }  
  13.   
  14.     public string LastName   
  15.     {  
  16.         get;  
  17.         set;  
  18.     }  
  19.   
  20.     public string DepartmentName   
  21.     {  
  22.         get;  
  23.         set;  
  24.     }  
  25.     public int DepartmentID   
  26.     {  
  27.         get;  
  28.         set;  
  29.     }  
  30.   
  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  
  9.   
  10.     };  
  11.     return employeedetail;   
  12.   
  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);  
  13.   
  14.   
  15.     if (employees == null)   
  16.     {  
  17.         return NotFound();  
  18.     }  
  19.   
  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.     }  
  8.   
  9.     db.Employees.Add(employees);  
  10.     await db.SaveChangesAsync();  
  11.     db.Entry(employees).Reference(s => s.Department).Load();  
  12.   
  13.     var StoreEmp = new EmployeeDTO()   
  14.     {  
  15.         EmployeeID = employees.EmployeeID,  
  16.             FristName = employees.FirstName,  
  17.             DepartmentName = employees.Department.DepartmentName  
  18.   
  19.     };  
  20.   
  21.   
  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.     }  
  8.   
  9.     if (id != employees.EmployeeID)   
  10.     {  
  11.         return BadRequest();  
  12.     }  
  13.   
  14.     db.Entry(employees).State = EntityState.Modified;  
  15.   
  16.     try   
  17.     {  
  18.         await db.SaveChangesAsync();  
  19.   
  20.   
  21.     } catch (DbUpdateConcurrencyException)   
  22.     {  
  23.         if (!EmployeesExists(id))  
  24.         {  
  25.             return NotFound();  
  26.         } else  
  27.         {  
  28.             throw;  
  29.         }  
  30.     }  
  31.   
  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.

Up Next
    Ebook Download
    View all
    Learn
    View all
    Get Started

    You need to be a premium member to use this feature. To access it, you'll have to upgrade your membership.

    Become a sharper developer and jumpstart your career.

    Basic

    $0

    $

    . 00

    monthly

    For Basic members:

    • Standard Profile
    • Access learning courses
    • Access free e-books
    • 1x point
    Premium

    $20

    $

    . 00

    monthly

    For Premium members:

    • Earn 2x reward points
    • Premium profile with Verified check
    • Premium Avatar with status update
    • Learning courses
    • Unlimited Certifications and badges on profile
    • Unlimited Book downloads
    • Access to Premium content
    • Online Training
    • Unlimited PDF conversions and downloads
    • Professional Resume Writing
    • Interview Preparations Guide
    • 25 messages per month
    • 1 "Kodzu" avatar, basic
    Elite

    $45

    $

    . 00

    monthly

    For Elite members:

    • Bundle E-books
    • Access to all Learn Series
    • Unlimited Apply Jobs