Achieving more with less – part 2 – OData and .Net Core
In the first part of this article we talked about the OData protocol in general when it should be used, what are its benefits and drawbacks, etc. In this part, we will cover one implementation of this protocol in Microsoft’s .Net Core framework.
In this implementation, a standard ASP .Net Core 3.1 Web API will be used. The database that is used is Microsoft’s sample Adventure Works 2019 that can be downloaded from here. Entity Framework Core is used as the standard Microsoft ORM. Since an existing database is used, the “database-first” approach was used to scaffold the model and the context class. The only thing left to add for the model to be complete is the “Key” attribute, a standard Data Annotation. The unique Id for entities in this database is not called “Id” but “BusinessEntityId”, so we need to mark them for OData.
The ASP .Net Core implementation of OData is found in a NuGet package with the title “Microsoft.AspNetCore.OData”, so that package is added to the solution. After the package is installed, we can start configuring OData for our API.
The configuration is rather simple, first inside our Configure Services method we need to add all necessary OData services to our services collection:
After that inside the method for configuring the HTTP request pipeline, we need to configure the OData endpoints. After mapping the controllers, we will configure the “OData route” which is the root of all our OData routes. In the end, we configure the Global OData query options and these options will be available for any OData route.
The route name is the first argument of the MapODataServiceRoute method, the prefix is the second and this prefix will differentiate this OData route from other routes in the API. The last argument is the Edm model which represents all the entities on this OData service that can be used in queries. The entities configured there will be shown when querying the $metadata endpoint.
After this, we should configure the controller. For an OData controller base class, we can use the regular BaseController. An attribute that specifies the prefix for the Person entity is added before the class declaration. The DbContext is injected directly into the controller to keep it simple. The “EnableQuery” attribute needs to be added to all controller actions where we want to use the OData query options.
Inside the Enable Query attribute, we can define which options or operators are allowed for this action and more. The “ODataRoute” attribute specifies that this action will be called for the specified route. In the case of the “Get by Id” action, it is necessary to specify that the “Key” parameter will be in parenthesis.
So When everything is set up, we can start the API and call the Person OData endpoint. Postman is used for testing this API.
Since the Person table in the AdventureWorks database contains almost 20 000 rows, EntityFramework Core throws an exception when trying to retrieve all rows from the database because of performance reasons. That is why only the first 100 rows are taken just as an illustrative example and that parameter will be seen in the code snippets below.
First of all, if we just query the Person endpoint, the result will be all Persons from the database. The URL would look like this http://localhost:5000/odata/Person, and the result would be:
If we would want to know how many records exist in total we could add the count option to our query so it would look like this http://localhost:5000/odata/Person?$count=true, and the result would be:
After that if we are just interested in the Person’s first and last name we could use the select option and tell OData that we want only those 2 properties like this: http://localhost:5000/odata/Person?$select=FirstName,LastName, and the result would be:
What is interesting to show here is how this translates to the database. If we would turn on SQL Server Profiler and see which query is executed on the database when this request is sent we could see this:
As we can see from the trace, only 2 columns were retrieved from the database apart from the obligatory entity Id and model Id. On the other hand, the profiler trace for a request without the select option would look like this:
In this example, we can see that all the columns from the Person table are retrieved.
We can also filter the result we get, so for example, if we want to get all Persons which are called “Michael” we would send this request http://localhost:5000/odata/Person?$filter=FirstName eq ‘Michael’. The profiler trace for this request is:
The argument of the filter option is passed all the way to the database and it’s being used in the SQL query.
If we want to get information about a Person’s password, we could use the expand option and retrieve the password entity from the database along with the Person. The request would then look like this http://localhost:5000/odata/Person?$expand=Password and the result would be:
The profiler trace for this request looks like this:
As it can be seen from the trace, the expand option is producing a left join SQL query which retrieves the navigation property entity. Furthermore, navigation properties can also be expanded to the depth specified inside the Enable Query attribute.
The Person result we query from the API can also be ordered through OData syntax. Using the orderby we can specify by which property the result will be ordered. That option is passed all the way down to SQL and for example this request http://localhost:5000/odata/Person?$orderby=FirstName&select=FirstName gives this result:
As we can see, the resulting Persons are ordered by the first name.
Implementation of OData protocol in an API is beneficial to the developer and the consumer of the API. In both cases, it really does give a lot of value with less code. From the developer’s perspective, it is just another layer on top of the existing endpoint and all the logic is contained inside a NuGet package so the implementation does not take a lot of time. From the consumer perspective, the endpoint provides all current requirements and possible new ones. It really does provide more with less. The complete code for this example can be found here.
Senior Software Developer at Northprim