Introduction

Most data sets contain the relation between entities, and from this relation we can get navigation property data. Web API 2 supports the $expand, $select and $value options with OData. These options are very helpful in controlling the response back from server. The $expand is useful in Expanding the child navigation property inside the entity set. Using OData we can also get the navigation property without using $expand. OData supports Entity Relation and supports this operation.

Example

Here, I am using entity framework as data source and I have created two tables named Employee and Department. Each employee has one department. The following figure shows the relation between the entities.

relation

The following code is for defining entity classes (Employee and Department) and entity model.

  1. namespaceWebAPITest
  2. {
  3. usingSystem.Collections.Generic;
  4. usingSystem.ComponentModel.DataAnnotations;
  5. usingSystem.ComponentModel.DataAnnotations.Schema;
  7. [Table("Employee")]
  8. publicpartialclassEmployee
  9. {
  10. publicint Id
  11. {
  12. get;
  13. set;
  14. }
  16. [Required]
  17. [StringLength(50)]
  18. publicstring Name
  19. {
  20. get;
  21. set;
  22. }
  24. publicintDepartmentId
  25. {
  26. get;
  27. set;
  28. }
  30. [Column(TypeName = "money")]
  31. publicdecimal ? Salary
  32. {
  33. get;
  34. set;
  35. }
  37. [StringLength(255)]
  38. publicstringEmailAddress
  39. {
  40. get;
  41. set;
  42. }
  44. [StringLength(50)]
  45. publicstringPhoneNumber
  46. {
  47. get;
  48. set;
  49. }
  51. publicstring Address
  52. {
  53. get;
  54. set;
  55. }
  57. publicvirtualDepartmentDepartment
  58. {
  59. get;
  60. set;
  61. }
  62. }
  64. [Table("Department")]
  65. publicpartialclassDepartment
  66. {
  67. public Department()
  68. {
  69. Employees = newHashSet < Employee > ();
  70. }
  72. publicintDepartmentId
  73. {
  74. get;
  75. set;
  76. }
  78. [StringLength(50)]
  79. publicstringDepartmentName
  80. {
  81. get;
  82. set;
  83. }
  85. publicvirtualICollection < Employee > Employees
  86. {
  87. get;
  88. set;
  89. }
  90. }
  91. }
  94. namespaceWebAPITest
  95. {
  96. using System;
  97. usingSystem.Data.Entity;
  98. usingSystem.ComponentModel.DataAnnotations.Schema;
  99. usingSystem.Linq;
  101. publicpartialclassEntityModel: DbContext
  102. {
  103. publicEntityModel(): base("name=EntityModel") {}
  105. publicvirtualDbSet < Department > Departments
  106. {
  107. get;
  108. set;
  109. }
  110. publicvirtualDbSet < Employee > Employees
  111. {
  112. get;
  113. set;
  114. }
  116. protectedoverridevoidOnModelCreating(DbModelBuildermodelBuilder)
  117. {
  119. }
  120. }
  121. }
Now I am configuring the end point for the employee entity set. Endpoint configured in WebApiConfig.cs file under App_Start. If application has multiple OData endpoints then create separate route for each.
  1. namespaceWebAPITest
  2. {
  3. usingMicrosoft.OData.Edm;
  4. usingSystem.Web.Http;
  5. usingSystem.Web.OData.Batch;
  6. usingSystem.Web.OData.Builder;
  7. usingSystem.Web.OData.Extensions;
  8. publicstaticclassWebApiConfig
  9. {
  10. publicstaticvoid Register(HttpConfigurationconfig)
  11. {
  12. config.MapODataServiceRoute("odata", null, GetEdmModel(), newDefaultODataBatchHandler(GlobalConfiguration.DefaultServer));
  13. config.EnsureInitialized();
  15. }
  16. privatestaticIEdmModelGetEdmModel()
  17. {
  18. ODataConventionModelBuilder builder = newODataConventionModelBuilder();
  19. builder.Namespace = "WebAPITest";
  20. builder.ContainerName = "DefaultContainer";
  21. builder.EntitySet < Employee > ("Employee");
  22. varedmModel = builder.GetEdmModel();
  23. returnedmModel;
  24. }
  25. }
  26. }
Next step is to define controller. Controller class is needed to inherit from ODataController and base class of ODataController is ApiController. In the body of the controller, I have defined GET method that returns the employee list and individual employee by id.
  1. namespaceWebAPITest.Controllers
  2. {
  3. usingSystem.Linq;
  4. using System.Net;
  5. usingSystem.Net.Http;
  6. usingSystem.Web.OData;
  7. publicclassEmployeeController: ODataController
  8. {
  9. EntityModel context = newEntityModel();
  10. [EnableQuery]
  11. publicIQueryable < Employee > Get()
  12. {
  13. returncontext.Employees;
  14. }
  16. ///<summary>
  17. /// Read Operation
  18. ///</summary>
  19. ///<param name="key">Key</param>
  20. ///<returns></returns>
  21. publicHttpResponseMessage Get([FromODataUri] int key)
  22. {
  23. Employee data = context.Employees.Where(k => k.Id == key).FirstOrDefault();
  24. if (data == null)
  25. {
  26. returnRequest.CreateResponse(HttpStatusCode.NotFound);
  27. }
  29. returnRequest.CreateResponse(HttpStatusCode.OK, data);
  30. }
  32. ///<summary>
  33. /// Dispose
  34. ///</summary>
  35. ///<param name="disposing"></param>
  36. protectedoverridevoid Dispose(bool disposing)
  37. {
  38. context.Dispose();
  39. base.Dispose(disposing);
  40. }
  41. }
  42. }
Using the $expand, we can get or load the related entity.

URI: http://localhost:24367/Employee?$expand=Department

Output

Output

When I am trying to get only department data for a particular employee, Web API throws 404 – Not Found error.

URI: http://localhost:24367/Employee(1)/Department

Output

Output

To work above URI, we need to modify code. To achieve this, we need to perform following steps.

Step 1 - Define ForeignKey relation at entity level

ForeignKey

Step 2 - In WebApiConfig.cs, Define the Entity set for "Department"

Department

Step 3: Add method to the controller

To support above said request, add following method to the employee controller class.
  1. publicclassEmployeeController: ODataController
  2. {….….
  3. [EnableQuery]
  4. publicSingleResult < Department > GetDepartment([FromODataUri] int key)
  5. {
  6. var result = context.Employees.Where(m => m.Id == key).Select(m => m.Department);
  7. returnSingleResult.Create(result);
  8. }……
  9. }
This method follows the default naming convention,

If we follow the above said naming convention, Web API automatically maps HTTP request to this controller method.

Now I am using same URL and it is working as expected, it returns department data.

URI: http://localhost:24367/Employee(1)/Department

Output

Output

We have to use IQueryable<T> as return type instead of a SingleResult<T>, if we want to return collection related entity.

  1. namespaceWebAPITest.Controllers
  2. {
  3. usingSystem.Linq;
  4. using System.Net;
  5. usingSystem.Net.Http;
  6. usingSystem.Web.OData;
  7. publicclassDepartmentController: ODataController
  8. {
  9. EntityModel context = newEntityModel();
  10. [EnableQuery]
  11. publicIQueryable < Employee > GetEmployees([FromODataUri] int key)
  12. {
  13. returncontext.Departments.Where(m => m.DepartmentId == key).SelectMany(m => m.Employees);
  14. }
  16. protectedoverridevoid Dispose(bool disposing)
  17. {
  18. context.Dispose();
  19. base.Dispose(disposing);
  20. }
  21. }
  22. }
URI - http://localhost:24367/Department(1)/Employees

Output

Output

Creating a Relationship between Entities

OData supports creating / deleting the relation between entities. In OData V3 relationship is referred to as "link" and in OData V4 relationship is refer as "reference". The relationship (reference) has its own URL in form of "/Entity/NavigationProperty/$ref". To add the relationship, client needs to send either POST or PUT request to the address.

To add a relation to the department, we need to add following method.

  1. [AcceptVerbs("POST", "PUT")]
  2. publicasyncTask<IHttpActionResult>CreateRef([FromODataUri] int key, stringnavigationProperty, [FromBody] Uri link)
  3. {
  4. …
  5. …
  6. }
The navigationProperty parameter helps us to specify that which relationship to set and the link parameter contains the URI of the employee. Web API is able to parses the request body and get the parameter value. Following helper method is used to lookup employee from the key parameter which is part of link.
  1. namespaceWebAPITest
  2. {
  3. usingMicrosoft.OData.Core;
  4. usingMicrosoft.OData.Core.UriParser;
  5. using System;
  6. usingSystem.Collections.Generic;
  7. usingSystem.Linq;
  8. usingSystem.Net.Http;
  9. usingSystem.Web.Http.Routing;
  10. usingSystem.Web.OData.Extensions;
  11. usingSystem.Web.OData.Routing;
  12. publicclassHelper
  13. {
  14. publicstaticTKeyGetKeyFromUri < TKey > (HttpRequestMessage request, Uriuri)
  15. {
  16. if (uri == null)
  17. {
  18. thrownewArgumentNullException("uri");
  19. }
  21. varurlHelper = request.GetUrlHelper() ? ? newUrlHelper(request);
  23. //Get service root from route name
  24. stringserviceRoot = urlHelper.CreateODataLink(
  25. request.ODataProperties().RouteName,
  26. request.ODataProperties().PathHandler, newList < ODataPathSegment > ());
  28. //Get path
  29. varodataPath = request.ODataProperties().PathHandler.Parse(
  30. request.ODataProperties().Model,
  31. serviceRoot, uri.LocalPath);
  33. //Get Key segment
  34. varkeySegment = odataPath.Segments.OfType < KeyValuePathSegment > ().FirstOrDefault();
  35. if (keySegment == null)
  36. {
  37. thrownewInvalidOperationException("The reference link does not contain a key.");
  38. }
  40. //Retrieve the value of key
  41. var value = ODataUriUtils.ConvertFromUriLiteral(keySegment.Value, ODataVersion.V4);
  43. return (TKey) value;
  44. }
  46. }
  47. }
Following is controller method that used to add a relationship to a Department.
  1. [AcceptVerbs("POST", "PUT")]
  2. publicasyncTask < IHttpActionResult > CreateRef([FromODataUri] int key, stringnavigationProperty, [FromBody] Uri link)
  3. {…….
  4. [AcceptVerbs("POST", "PUT")]
  5. publicasyncTask < IHttpActionResult > CreateRef([FromODataUri] int key, stringnavigationProperty, [FromBody] Uri link)
  6. {
  7. var department = context.Departments.SingleOrDefault(p => p.DepartmentId == key);
  8. if (department == null)
  9. {
  10. returnNotFound();
  11. }
  12. switch (navigationProperty)
  13. {
  14. case "Employees":
  15. varrelatedKey = Helper.GetKeyFromUri < int > (Request, link);
  16. var employee = context.Employees.SingleOrDefault(f => f.Id == relatedKey);
  17. if (employee == null)
  18. {
  19. returnNotFound();
  20. }
  22. department.Employees.Add(employee);
  23. break;
  25. default:
  26. returnStatusCode(HttpStatusCode.NotImplemented);
  27. }
  28. awaitcontext.SaveChangesAsync();
  29. returnStatusCode(HttpStatusCode.NoContent);
  30. }….…..
  31. }
In this example, Client send PUT request "Department(1)/Employees/$ref", this URI for the employee for the Department with Id = 1. If the request successfully parsed then server sends 204 - No content - as response.

example

Following is debugging snap on Department controller. Here we are able to fetch key value (Department Id), navigation property name and URI link.

controller

Deleting a Relationship between Entities

To delete the relation, client needs to send delete HTTP request and also needs to send $ref URI.

Following is code for deleting the relation.
  1. publicasyncTask < IHttpActionResult > DeleteRef([FromODataUri] intkey, stringnavigationProperty, [FromBody] Uri link)
  2. {
  3. //Remove the entity....write code for this
  4. returnStatusCode(HttpStatusCode.NoContent);
  5. }
To support the delete operation, client must send the related entity which needs to be delete. Client sends the URI of related entity in query string.

URI - http://localhost:24367/Department(1)/employee/$ref?https://localhost:24367/Employee(1)

output

Following is debugging snap on Department controller. Here we are able to fetch key value (Department Id), navigation property name, and URI link. From the URI link, we can get the employee that needs to be removed from the department.

output

Summary

This article helps us to understand how to create entity relation in OData with web API.