Using OData Query Options In ASP.NET Web API

Here is how we can use the OData query Option in ASP.Net Web API.

What are OData Query options? There can be a requirement wherein a client sends some parameters in the request URI and those parameters are applied on the server side to perform the desired actions while getting data from API service interface.

Here are the various parameters that can be used together with API URI in request.

$expand
$filter
$inlinecount
$orderby
$select
$skip
$top

Here are the examples of some the most used parameter with web API URI.

http://localhost/api/Employees?$expand=DeptId
http://localhost/api/Employees?$filter=Id ge 5 and Price le 15
http://localhost/api/Employees?$inlinecount=allpages
http://localhost/api/Employees?$orderby=Id desc
http://localhost/api/Employees?$select=EmployeeName, Salary
http://localhost/api/Employees?$skip=10
http://localhost/api/Employees?$top=5

Now to implement support for OData Query Options we have to do a few things listed down here.

Install package 'Microsoft.AspNet.WebApi.OData.5.7.0'

Enable support for OData query options in Web API Configuration class.
1. public static class WebApiConfig
2. {
3. public static void Register(HttpConfiguration config)
4. {
6. config.EnableQuerySupport();
8. // Web API routes
9. //config.MapHttpAttributeRoutes();
11. config.Routes.MapHttpRoute(
12. name: "DefaultApi",
13. routeTemplate: "Api/{controller}/{id}",
14. defaults: new { id = RouteParameter.Optional }
15. );
16. }
17. }

This setting will enable OData Query Support at global level.

But If we want to enable OData query support for only one controller method then we call apply [Queryable] attribute to the controller action.

[Queryable]
public IEnumerable<string> Get()
{
return new string[] { "value1", "value2" };
}

Once that’s done then we start using query option in our API calls.

Limiting Query Options

With OData query options come to a feature called “limiting query option” with that we can put a limit on what type of query option can be used while calling a certain API action.

Here are some of the examples of how we can use limitation on query options.

[Queryable (AllowedQueryOptions=AllowedQueryOptions.Top | AllowedQueryOptions.Skip)] - This will limit usage to only top and skip.
[Queryable (AllowedOrderByProperties="Id")] - we can use comma separated property name like Id, EmployeeName etc
[Queryable (AllowedLogicalOperators=AllowedLogicalOperators.Equal)] - This will limit usage to only equal.

If we want to apply these limitations at the global level, then we can do that also. By adding query attribute to the enable query support method in WebApiConfig.cs as shown below.

var queryAttribute = new QueryableAttribute()
{
AllowedQueryOptions = AllowedQueryOptions.Skip | AllowedQueryOptions.OrderBy,
MaxTop = 40
};

Query Validation

[Queryable] attribute performs query validation before executing it. It performs the validation by using the following method.

QueryableAttribute.ValidateQuery

We have the liberty of customizing this feature also. For that, we need to write a custom class which needs to inherit from EnableQueryAttribute class and write custom query attribute class which will be inherited from OrderByQueryValidator class. As shown below

public class MyQueryableAttribute : EnableQueryAttribute
{
public override void ValidateQuery(HttpRequestMessage request, ODataQueryOptions queryOptions)
{
if (queryOptions.OrderBy != null)
{
queryOptions.OrderBy.Validator = new MyOrderByValidator();
}
base.ValidateQuery(request, queryOptions);
}
}
public class MyOrderByValidator : OrderByQueryValidator
{
public override void Validate(OrderByQueryOption orderByOption, ODataValidationSettings validationSettings)
{
if (orderByOption.OrderByNodes.Any(node => node.Direction == OrderByDirection.Descending))
{
throw new ODataException("you can not use 'desc' with this method call");
}
base.Validate(orderByOption, validationSettings);
}
}

Once that is done do the build and if there are errors, add the required references and NuGet packages.

When the project is built, then we can use [MyQueryable] attribute on the controller method to use.

The custom query validator looks as shown below and you should be all set.

[MyQueryable]
public IQueryable<Employee> GetAllEmployees(ODataQueryOptions opts)
{
if (opts.OrderBy != null)
{
opts.OrderBy.Validator = new MyOrderByValidator();
}
var settings = new ODataValidationSettings()
{
};
var Employeelist = iEmployeeBL.GetEMPLOYEEList();
opts.Validate(settings);
IQueryable results = opts.ApplyTo(Employeelist.AsQueryable());
return results as IQueryable<Employee>;
}

3 Comments

Join the conversation! Your thoughts help the community grow.

Seemo Ali6y ago
I attempted to create validation on filter, but I can not do validation on all properties, the validation ( ValidateSingleValuePropertyAccessNode ) is per each property, any idea how solve this ?
Hiten Pandya7y ago
Thank you so much sir for sharing your knowledge.
Rushi Mehta7y ago
Thanks for sharing the article