OData in Dotnet Web Api Interview Notes

1. 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).

2. 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:

3. http://localhost/Products?$orderby=Name

4. 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 desc*

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

*$top=5&$skip=3*

*$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 lt 3*

*$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()