Web API: Querying using OData protocol

facebooktwittergoogle_plusredditpinterestlinkedinmail

Introduction to data querying using OData


In ASP.NET Web API we pass any parameter to an action and we can do any filtration there. It is pretty simple. So why we need a protocol like OData here ? . OData provides an easy way to do filtering, ordering, paging that helps us to build a RESTful APIs in a very efficient way . This is basically a way that you can encode query into the URL, that executes on the server side. This article explains the querying using OData protocol in ASP.NET Web API.

What is OData Protocol ?

OData (Open Data protocol) is an open standard protocol. It was initiated and is led by Microsoft . However, It is supported by a number of different platform vendors. You can find the full specification and other details about the protocol at OData.org. OData is also supports formatting, There are two formats , one is an XML based format that is the ATOM publishing protocol. There is also a JSON formatting.

Querying In API
Create an ASP.NET Web API project and add an API controller. Here I have one action to return all the employees.

 
 [HttpGet]
        public IEnumerable<Employee> AllEmployees()
        {
            IEnumerable<Employee> Employees = GetEmployees();
 
            return Employees;
        }
  private static IEnumerable<Employee> GetEmployees()
       {
           IEnumerable<Employee> Employees = new List<Employee>() 
            { 
                new Employee{ EmployeeId = 1 , EmployeeName = "Joe" , Experiance = 10 , Age = 32 }, 
                new Employee{ EmployeeId = 2 , EmployeeName = "Bob" , Experiance = 12 , Age = 33 }, 
                new Employee{ EmployeeId = 3 , EmployeeName = "Sai" , Experiance = 4 , Age = 27 }, 
                new Employee{ EmployeeId = 4 , EmployeeName = "John" , Experiance = 6 , Age = 29 }, 
                new Employee{ EmployeeId = 5 , EmployeeName = "Kathi" , Experiance = 15 , Age = 38 }, 
                new Employee{ EmployeeId = 6 , EmployeeName = "Ryan" , Experiance = 20 , Age = 40 }, 
                new Employee{ EmployeeId = 7 , EmployeeName = "Malik" , Experiance = 10 , Age = 32 }, 
                new Employee{ EmployeeId = 8 , EmployeeName = "James" , Experiance = 5 , Age = 27 }, 
                new Employee{ EmployeeId = 9 , EmployeeName = "Tom" , Experiance = 10 , Age = 34 }, 
                new Employee{ EmployeeId = 10 , EmployeeName = "Raj" , Experiance = 6 , Age = 27 }, 
            };
           return Employees;
       }

Here AllEmployees action returns a collection of employees. You can see the Json result in the following screen.

Querying using OData - first

Add the NewGet package: Microsoft.Asp.net.Webapi.OData

Querying using OData - Add OData

Add the attribute [EnableQuery] over the action and make the return type as IQueryable. That’s it, go ahead and run the application.

[HttpGet]
        [EnableQuery]
        public IQueryable<Employee> AllEmployees()
        {
            IEnumerable<Employee> Employees = GetEmployees();
 
            return Employees.AsQueryable();
        }

Filtering
Type http://localhost:51697/api/Company/allemployees/Employees?$filter=Experiance gt 10 , it will return employees with with experience greater than 10 .

Querying using OData - filter

For less than 10 year of experience type http://localhost:51697/api/Company/allemployees/Employees?$filter=Experiance lt 10

Ordering
We can sort the data using $orderby . Syntax: /Employees?$orderby=Age

Querying using OData - Order by

In the preceding screen you can see the sorted data.

The following two tabs change content below.

Tom Mohan

Tom Mohan is a technologist who loves to code and build. He enjoys working on Microsoft Technologies. Tom specializes in ASP.NET MVC, Web API , Azure, C# ,WPF, SQL etc and holds a Bachelor engineering degree in Computer Science. Certification : MCSD , MCTS