Part 14b – OData v4 in ASP.NET WebApi – Query Options – FILTER

This is a series of blogs detailing my introduction to using OData v4 with C# and ASP.NET WebApi.

The source code for the project can be found here.

Up to now we have been creating individual controller actions for our entities either for selecting all entities or selecting entities by key. One of the powers of OData is the query like syntax we can apply within the Url which does not need additional controller actions. In this blog we will look at the FILTER query option.

Creating the Project

The Project is called Part14.

Query All Employees

Following on from the last blog. We can view all Employees as follows.

From Postman issue a GET request with http://localhost:40000/OData/Employees.

Filter All Employees that Live in London

Before we issue the query we need to enable the filter option in our model. This is the same as what we did for select and in fact, what we need to do for all query options.

private static IEdmModel GetEdmModel()
   ODataConventionModelBuilder builder = new ODataConventionModelBuilder();


   builder.EntityType().Ignore(p => p.Photo);
   builder.EntityType().Ignore(p => p.PhotoPath);

   return builder.GetEdmModel();

From Postman issue a GET request with http://localhost:40000/odata/Employees?$filter=City eq ‘London’

Entity Framework will turn the filtering into a SQL WHERE clause. We can even include SELECT as well just make sure you separate the two by an &.

From Postman issue a GET request with http://localhost:40000/odata/Employees?$filter=City eq ‘London’&$select=LastName,FirstName,City

If we look at the backend query we see the select and filter being turned into a SQL query and issued on the server. Exactly what we want from a performance aspect.

Additional Filter Configuration

The filter method we added to our model has some overloads.

  • filter() will enable filtering on every property.
  • filter(QueryOptionSetting.Allowed, , , etc) will allow filtering on those properties only
  • filter(QueryOptionSetting.Disabled, , , etc) will not allow filtering on those properties only.

You still need to include filter() if you include Filter(QueryOptionSetting.Disabled…)

Filter Operators

There are a huge number of filter operators to use.

  • We have the usual equals, not equals, less than, greater than, etc
  • We can use and and or to link different expressions.
  • Arithmetic operators
  • String operators
  • Date and time operators

See section 5 of the standard to see what is available. Just bear in mind that some may not be implemented in WebApi.

In the next blog I’ll take a look at the $expand query option.