Tuesday, March 4, 2014

OData in WebApi 2


Web API 2 comes with OData query support. It supports the following query options for webAPI client applications to customize the result set returned by the API based on there requirement.

Option Description
$expand Expands related entities inline.
$value Gets the raw value of a property.
$filter Filters the results, based on a Boolean condition.
$inlinecount Tells the server to include the total count of matching entities in the response. (Useful for server-side paging.)
$orderby Sorts the results.
$select Selects which properties to include in the response.
$skip Skips the first n results.
$top Returns only the first n the results.


To use OData queries you need to enable it globally, controller or specific action level.

Enable Globally

1. In you MVC project go to App_Start folder

2. There you can find the WebApiConfig.Cs

3. In there add the following code.

  1. public static void Register(HttpConfiguration config)
  2.    {
  3.        config.EnableQuerySupport();
  4.    }


Enable Controller Level

  2. [Queryable]
  3. public class EmployeeController : ApiController
  4. {


Enable Action Level

  1. [Queryable]
  2.         public IEnumerable<string> Get()
  3.         {
  4.             return new string[] { "value1", "value2" };
  5.         }

lets see examples of each option one by one. We will consider the following model for explaining the queries.

  1. public class Employee
  2.     {
  3.         [Key]
  4.         public int Id { get; set; }
  5.         public string Name { get; set; }
  6.         public int Age { get; set; }
  7.         public string Gender { get; set; }
  8.         public string Address { get; set; }
  9.         public string Mobile { get; set; }
  10.         public virtual ICollection<Leave> Leaves { get; set; }
  12.     }
  13.     public class Leave
  14.     {
  15.         [Key]
  16.         public int Id { get; set; }
  17.         public string Reason { get; set; }
  18.         [ForeignKey("LeaveType")]
  19.         public int LeaveTypeId { get; set; }
  20.         public LeaveType Leavetype { get; set; }
  21.     }
  23.     public class LeaveType
  24.     {
  25.         [Key]
  26.         public int Id { get; set; }
  27.         public string Name { get; set; }
  29.     }



Suppose you want to get employee details with all the leave records for each employee then you can use expand query as below.


In case if you want to get the Leave type details you can add the the related entities you want to expand comma separated in the query as below.


By default Web API support expansion to 2 levels. You can override it as below.

  1. [Queryable(MaxExpansionDepth = 4)]
  2.      public IQueryable<Employee> GetEmployees()
  3.      {
  4.          return db.Employees;
  5.      }



If you need to select only the Name and Age of the employee or a subset of properties of an entity you can use $select for that as below.


You can also use this to select properties of an expanded entity as below. This selects name and age from Employee and Name from leaves



This will return raw value of property without JSON



You can use Boolean expressions to filter your API results using this query type.

Return all employees with gender equal to “Male”. http://localhost/Employees?$filter=Gender eq 'Male'
Return all employees who’s age is below 30. http://localhost/Employees?$filter=Age lt 30
Logical operators: employees who’s age is between 20-30 http://localhost/Employees?$filter=Age ge 20 and Age le 20
String functions: Return all employees with name “john” http://localhost/Employees?$filter=substringof('john',Name)




This is used to sort the result set.

Sort by Age. http://localhost/Employees?$orderby=Age
Sort by Age in descending order (highest to lowest). http://localhost/Employees?$orderby=Age desc


$skip & $top

This is used when you have millions of records in your database and when you need paging in client side.


The $top option gives the maximum number of entries to return, and the $skip option gives the number of entries to skip. The previous example fetches entries 21 through 30.

Hope this helps you.


Happy Coding !!!!!

No comments :

Post a Comment