Entity Relations In OData V4 Using Web API 2.2

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.

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

namespaceWebAPITest
{
usingSystem.Collections.Generic;
usingSystem.ComponentModel.DataAnnotations;
usingSystem.ComponentModel.DataAnnotations.Schema;
[Table("Employee")]
publicpartialclassEmployee
{
publicint Id
{
get;
set;
}
[Required]
[StringLength(50)]
publicstring Name
{
get;
set;
}
publicintDepartmentId
{
get;
set;
}
[Column(TypeName = "money")]
publicdecimal ? Salary
{
get;
set;
}
[StringLength(255)]
publicstringEmailAddress
{
get;
set;
}
[StringLength(50)]
publicstringPhoneNumber
{
get;
set;
}
publicstring Address
{
get;
set;
}
publicvirtualDepartmentDepartment
{
get;
set;
}
}
[Table("Department")]
publicpartialclassDepartment
{
public Department()
{
Employees = newHashSet < Employee > ();
}
publicintDepartmentId
{
get;
set;
}
[StringLength(50)]
publicstringDepartmentName
{
get;
set;
}
publicvirtualICollection < Employee > Employees
{
get;
set;
}
}
}
namespaceWebAPITest
{
using System;
usingSystem.Data.Entity;
usingSystem.ComponentModel.DataAnnotations.Schema;
usingSystem.Linq;
publicpartialclassEntityModel: DbContext
{
publicEntityModel(): base("name=EntityModel") {}
publicvirtualDbSet < Department > Departments
{
get;
set;
}
publicvirtualDbSet < Employee > Employees
{
get;
set;
}
protectedoverridevoidOnModelCreating(DbModelBuildermodelBuilder)
{
}
}
}

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.

namespaceWebAPITest
{
usingMicrosoft.OData.Edm;
usingSystem.Web.Http;
usingSystem.Web.OData.Batch;
usingSystem.Web.OData.Builder;
usingSystem.Web.OData.Extensions;
publicstaticclassWebApiConfig
{
publicstaticvoid Register(HttpConfigurationconfig)
{
config.MapODataServiceRoute("odata", null, GetEdmModel(), newDefaultODataBatchHandler(GlobalConfiguration.DefaultServer));
config.EnsureInitialized();
}
privatestaticIEdmModelGetEdmModel()
{
ODataConventionModelBuilder builder = newODataConventionModelBuilder();
builder.Namespace = "WebAPITest";
builder.ContainerName = "DefaultContainer";
builder.EntitySet < Employee > ("Employee");
varedmModel = builder.GetEdmModel();
returnedmModel;
}
}
}

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.

namespaceWebAPITest.Controllers
{
usingSystem.Linq;
using System.Net;
usingSystem.Net.Http;
usingSystem.Web.OData;
publicclassEmployeeController: ODataController
{
EntityModel context = newEntityModel();
[EnableQuery]
publicIQueryable < Employee > Get()
{
returncontext.Employees;
}
///<summary>
/// Read Operation
///</summary>
///<param name="key">Key</param>
///<returns></returns>
publicHttpResponseMessage Get([FromODataUri] int key)
{
Employee data = context.Employees.Where(k => k.Id == key).FirstOrDefault();
if (data == null)
{
returnRequest.CreateResponse(HttpStatusCode.NotFound);
}
returnRequest.CreateResponse(HttpStatusCode.OK, data);
}
///<summary>
/// Dispose
///</summary>
///<param name="disposing"></param>
protectedoverridevoid Dispose(bool disposing)
{
context.Dispose();
base.Dispose(disposing);
}
}
}

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

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

Step 3: Add method to the controller

To support above said request, add following method to the employee controller class.

publicclassEmployeeController: ODataController
{….….
[EnableQuery]
publicSingleResult < Department > GetDepartment([FromODataUri] int key)
{
var result = context.Employees.Where(m => m.Id == key).Select(m => m.Department);
returnSingleResult.Create(result);
}……
}

This method follows the default naming convention,

Method name - GET {navigation property name}
Parameter name - key

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.

namespaceWebAPITest.Controllers
{
usingSystem.Linq;
using System.Net;
usingSystem.Net.Http;
usingSystem.Web.OData;
publicclassDepartmentController: ODataController
{
EntityModel context = newEntityModel();
[EnableQuery]
publicIQueryable < Employee > GetEmployees([FromODataUri] int key)
{
returncontext.Departments.Where(m => m.DepartmentId == key).SelectMany(m => m.Employees);
}
protectedoverridevoid Dispose(bool disposing)
{
context.Dispose();
base.Dispose(disposing);
}
}
}

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.

POST - if the navigation property is single entity
PUT - If the navigation property is collection

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

[AcceptVerbs("POST", "PUT")]
publicasyncTask<IHttpActionResult>CreateRef([FromODataUri] int key, stringnavigationProperty, [FromBody] Uri link)
{
…
…
}

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.

namespaceWebAPITest
{
usingMicrosoft.OData.Core;
usingMicrosoft.OData.Core.UriParser;
using System;
usingSystem.Collections.Generic;
usingSystem.Linq;
usingSystem.Net.Http;
usingSystem.Web.Http.Routing;
usingSystem.Web.OData.Extensions;
usingSystem.Web.OData.Routing;
publicclassHelper
{
publicstaticTKeyGetKeyFromUri < TKey > (HttpRequestMessage request, Uriuri)
{
if (uri == null)
{
thrownewArgumentNullException("uri");
}
varurlHelper = request.GetUrlHelper() ? ? newUrlHelper(request);
//Get service root from route name
stringserviceRoot = urlHelper.CreateODataLink(
request.ODataProperties().RouteName,
request.ODataProperties().PathHandler, newList < ODataPathSegment > ());
//Get path
varodataPath = request.ODataProperties().PathHandler.Parse(
request.ODataProperties().Model,
serviceRoot, uri.LocalPath);
//Get Key segment
varkeySegment = odataPath.Segments.OfType < KeyValuePathSegment > ().FirstOrDefault();
if (keySegment == null)
{
thrownewInvalidOperationException("The reference link does not contain a key.");
}
//Retrieve the value of key
var value = ODataUriUtils.ConvertFromUriLiteral(keySegment.Value, ODataVersion.V4);
return (TKey) value;
}
}
}

Following is controller method that used to add a relationship to a Department.

[AcceptVerbs("POST", "PUT")]
publicasyncTask < IHttpActionResult > CreateRef([FromODataUri] int key, stringnavigationProperty, [FromBody] Uri link)
{…….
[AcceptVerbs("POST", "PUT")]
publicasyncTask < IHttpActionResult > CreateRef([FromODataUri] int key, stringnavigationProperty, [FromBody] Uri link)
{
var department = context.Departments.SingleOrDefault(p => p.DepartmentId == key);
if (department == null)
{
returnNotFound();
}
switch (navigationProperty)
{
case "Employees":
varrelatedKey = Helper.GetKeyFromUri < int > (Request, link);
var employee = context.Employees.SingleOrDefault(f => f.Id == relatedKey);
if (employee == null)
{
returnNotFound();
}
department.Employees.Add(employee);
break;
default:
returnStatusCode(HttpStatusCode.NotImplemented);
}
awaitcontext.SaveChangesAsync();
returnStatusCode(HttpStatusCode.NoContent);
}….…..
}

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.

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.

publicasyncTask < IHttpActionResult > DeleteRef([FromODataUri] intkey, stringnavigationProperty, [FromBody] Uri link)
{
//Remove the entity....write code for this
returnStatusCode(HttpStatusCode.NoContent);
}

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.