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 APPLY query option.
The APPLY query option is a really powerful option that actually has its own standard and is classified as an OData extension. You can read the specification here. APPLY is about providing aggregation functionality without cluttering the OData language with all the possible combinations of aggregation that would be required. A word of caution. I don’t believe much of the standard has been implemented in WebApi. There is very little documentation and most queries when tried just fail. That said I have provided a couple of examples however; as your specific requirement comes up I would suggest searching forums for solutions.
Creating the Project
The Project is called Part14.
Query All Employees and Return their Country Location
From Postman issue a GET request with http://localhost:40000/odata/Employees?$select=Country.
Query All Employees and Return a Distinct List of Countries
Unlike other query options, apply is one of those options that does not need switching on.
From Postman issue a GET request with http://localhost:40000/odata/Employees?$apply=groupby((Country)).
You can see the output with the query that was sent to SQL Server. Note the double parenthesis are intentional.
Query All Employees and Return a Count of Each Distinct County
From Postman issue a GET request with http://localhost:40000/odata/Employees?$apply=groupby((Country),aggregate($count as Total)).
Hopefully you get the idea. Hopefully as more of the standard is supported, I can come back and update this blog.
In the next blog I’ll take a look at paging results.
Denham Coder 