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.
|$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.
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.
Enable Controller Level
Enable Action Level
lets see examples of each option one by one. We will consider the following model for explaining the queries.
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.
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 !!!!!