Querying Using OData on Web API

Introduction

 

In this article I am going to explain how to query a Web API.

 

The Open Data Protocol (OData) is a web protocol for querying and updating data that provides a way to unlock your data and free it from silos that exist in applications today. OData does this by applying and building upon web technologies such as HTTP, Atom Publishing Protocol (AtomPub) and JSON to provide access to information from a variety of applications, services, and stores. The protocol emerged from experiences implementing AtomPub clients and servers in a variety of products over the past several years. OData is being used to expose and access information from a variety of sources including, but not limited to, relational databases, file systems, content management systems and traditional web sites.

 

OData is consistent with the way the web works - it makes a deep commitment to URIs for resource identification and commits to an HTTP-based, uniform interface for interacting with those resources (just like the web).  This commitment to core web principles allows OData to enable a new level of data integration and interoperability across a broad range of clients, servers, services, and tools. more

 

In this article I am using the same application as in the https://www.c-sharpcorner.com/UploadFile/amit12345/mvc-4-web-api-net-4-5/ article.

 

Let's see how OData is working on Web API.

 

Now here is our URL https://localhost:40683/api/customer.  It's a MVC 4 Web API application. You can download it from the above specified article. You need to change only one thing in the CustomerController class. In the sample Get method the return type is IEnumerable<CustomerModel> so you have to change it to use IQueryable<CustomerModel>.

 

See the following code. 

public IQueryable<CustomerModel> Get()
{
    IQueryable<CustomerModel> list = null;
    list = (from c in context.Customers
            select new CustomerModel { Id = c.Id, Name = c.Name, Salary = (long)c.Salary }).AsQueryable<CustomerModel>();

    return list;
}

You can see in the following image that there is data being shown for a Customer in the form of a list; now we fire a query on the list.

 

 

Let's see what the OData Uri Conventions are.

 

Query Options
$top	We can get top records - https://localhost:40683/api/customer?$top=2
$filter	We can filter data by using this $filter query - https://localhost:40683/api/customer?$filter=Name%20eq%20'Amit'
$skip	If we use $skip only we will get following error. The method 'Skip' is only supported for sorted input in LINQ to Entities. The method 'OrderBy' must be called before the method 'Skip'.   We can $skip with $orderby query only. - https://localhost:40683/api/customer?$skip=2&$orderby=Name
$orderby	Simple like SQL query -  https://localhost:40683/api/customer?$orderby=Name
$format)	We can define output format like JSON, XML etc https://localhost:40683/api/customer?$format=xml
$select	You can specify the fields https://localhost:40683/api/customer?$select=Id,Name

 

There is much more about OData; you can find more in https://www.odata.org/documentation/uri-conventions#ExpandSystemQueryOption.

 

Happy Coding.