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.EntitySet("Employees")
.EntityType
.Select()
.Filter()
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.
Denham Coder 