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

1.  
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; }
11.  
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.     }
22.  
23.     public class LeaveType
24.     {
25.         [Key]
26.         public int Id { get; set; }
27.         public string Name { get; set; }
28.  
29.     }

 

$expand

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

http://localhost/odata/Employees?$expand=Leaves

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.

http://localhost/odata/Employees?$expand=Leaves,LeaveType

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.      }

 

$select

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.

http://localhost/odata/Employees?$select=Name,Age

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

http://localhost/odata/Employees?$expand=Leaves&$select=Name,Age,Leaves/Name

$value

This will return raw value of property without JSON

http://localhost/odata/Employees(1)/Name/$value

$filter

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)

 

 

$orderby

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.

http://localhost/Employees?$top=10&$skip=20

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 !!!!!