The Open Data Protocol (OData) is a data access protocol for the web. OData provides a uniform way to query and manipulate data sets through CRUD operations (create, read, update, and delete).
OData defines parameters that can be used to modify an OData query. The client sends these parameters in the query string of the request URI. For example, to sort the results, a client uses the $orderby parameter:
The OData specification calls these parameters query options. You can enable OData query options for any Web API controller in your project the controller does not need to be an OData endpoint. This gives you a convenient way to add features such as filtering and sorting to any Web API application.
Web API 5.2
OData v4
Visual Studio 2017 (download Visual Studio 2017 here)
Entity Framework 6
.NET 4.7.2
a. Configure the OData endpoint
Open the file App_Start/WebApiConfig.cs. Add the following using statements:
using ProductService.Models;
using Microsoft.AspNet.OData.Builder;
using Microsoft.AspNet.OData.Extensions;
Then add the following code to the Register method:
public static class WebApiConfig
{
public static void Register(HttpConfiguration config)
{
// New code:
ODataModelBuilder builder = new ODataConventionModelBuilder();
builder.EntitySet<Product>("Products");
config.MapODataServiceRoute(
routeName: "ODataRoute",
routePrefix: null,
model: builder.GetEdmModel());
}
}
Inherits odatacontroller:
public class ProductsController : ODataController
public async Task Patch([FromODataUri] int key, Delta product)
OData supports two different semantics for updating an entity, PATCH and PUT.
PATCH performs a partial update. The client specifies just the properties to update.
PUT replaces the entire entity.
b. Query Options
The following are the OData query options that ASP.NET WebAPI supports:
$orderby: Sorts the fetched record in particular order like ascending or descending.
$orderby=ProductName asc
$select: Selects the columns or properties in the result set. Specifies which all attributes or properties to include in the fetched result.
$skip: Used to skip the number of records or results. For example, I want to skip first 100 records from the database while fetching complete table data, then I can make use of $skip.
*$orderby=ProductName asc &$skip=6*
$top: Fetches only top n records. For e.g. I want to fetch top 10 records from the database, then my particular service should be OData enabled to support $top query option. ? $top=2
$orderby=ProductId asc&$top=5
$expand: Expands the related domain entities of the fetched entities.
$filter: Filters the result set based on certain conditions, it is like where clause of LINQ. For e.g. I want to fetch the records of 50 students who have scored more than 90% marks, and then I can make use of this query option.
$filter=ProductName eq computer
*$filter=ProductId ge 3 and ProductId le 5*
*$filter=substringof(IPhone,ProductName)*
$inlinecount: This query option is mostly used for pagination at client side. It tells the count of total entities fetched from the server to the client.
[Queryable] [GET(allproducts)] [GET(all)] public HttpResponseMessage Get() { var products = _productServices.GetAllProducts().AsQueryable(); var productEntities = products as List ?? products.ToList(); if (productEntities.Any()) return Request.CreateResponse(HttpStatusCode.OK, productEntities.AsQueryable()); throw new ApiDataException(1000, Products not found, HttpStatusCode.NotFound); }
c. Standard filter operators
The Web API supports the standard OData filter operators listed in the following table.
Operator
Description
Example
Comparison Operators
Eq
Equal
$filter=revenue eq 100000
Ne
Not Equal
$filter=revenue ne 100000
Gt
Greater than
$filter=revenue gt 100000
Ge
Greater than or equal
$filter=revenue ge 100000
Lt
Less than
$filter=revenue lt 100000
Le
Less than or equal
$filter=revenue le 100000
Logical Operators
And
Logical and
$filter=revenue lt 100000 and revenue gt 2000
Or
Logical or
$filter=contains(name,(sample)) or contains(name,test)
Not
Logical negation
$filter=not contains(name,sample)
Grouping Operators
( )
Precedence grouping
(contains(name,sample) or contains(name,test)) and revenue gt 5000
d. Standard query functions
The web API supports these standard OData string query functions.
Function
Example
contains
$filter=contains(name,(sample))
endswith
$filter=endswith(name,Inc.)
startswith
$filter=startswith(name,a)
Paging:
[Queryable(PageSize = 10)]
e. Query Constraints:
[Queryable(AllowedQueryOptions =AllowedQueryOptions.Filter | AllowedQueryOptions.OrderBy)]
[GET(allproducts)]
[GET(all)]
public HttpResponseMessage Get()
Top comments (0)