OData short for Open Data Protocol allows developer to create a simple query able RESTFUL APIs. With OData the caller can freely filter, select and expand the data in server side with a standard query and the implementation is very simple and straight forward. OData builds on HTTP and JSON using URIs to address and access data feed resources, it makes OData interoperable and easy to implement.
Imagine in real case, we need to do pagination, we need to include more detail information into certain depth and detail, we need to filter the data that send, it will be better if the request can do query just like we have in Linq or Sql right? Well case like this is perfect with OData, with just one endpoint the client can do various query that suit for their needs.
In this article lets discus how to implement OData in Asp.Net Core, we will try to build the solution using Entity Framework Core, using basic Dependency Injection and finally how to use OData in possible real case in real world.
As a start we will need this:
- Northwind Database, it can be downloaded from https://docs.microsoft.com/en-us/sql/samples/adventureworks-install-configure?view=sql-server-2017
- Microsoft.EntityFrameworkCore.SqlServer nuget Package
- Microsoft.AspNetCore.OData nuget Package
- Scrutor, this is a helper for Dependency Injection, more information about scrutor can be found in https://andrewlock.net/using-scrutor-to-automatically-register-your-services-with-the-asp-net-core-di-container/
- ValueInjecter nuget Package
First, we need to restore the Database, create a new Asp.Net Core Project and ensure the connection string setup properly.
At the data level, OData will need to have a “Direct” access to our database, so we will not to worry about query or any linq, we can just give it our DbSet at lets OData do the job. The code in data level will look like this:
We are using interfaces to make our life in Dependency Injection easier, we can use Scrutor to do assembly scan and register all the classes in the DI
Now we can start to implement the OData Part. First of all, we need to register the OData within the Startup class.
Add this line to ConfigureServices method:
And the following lines to Configure Method:
It will register our OData and tell that we support “select”,”expand”,”filter”,”orderby”, “top” and “count” query for OData. EnableDependencyInjection is needed because we want to use standard ASP.Net Core controller not ODataController.
After all set up we are ready to implement the OData endpoint. Create a new controller and put these lines:
It’s just ordinary controller and in the action method we just need to return our DbSet which is an access to the DB Table and put attribute EnableQuery. Now, lets try our work using Postman.
Perhaps you will notice that its very slow and heavy, because its return all of the data and the size is almost 7.71 MB, now let’s try another call
Now it only takes 20 records, and we are doing this without any extra implementation in the server side. Let’s call another query
Query: https://localhost:44354/api/person-odata?$filter=title eq ‘Mr.’ and personType eq ‘SP’
Query: https://localhost:44354/api/person-odata?$filter=title eq ‘Mr.’ and personType eq ‘SP’&$select=businessEntityId,personType,title,firstName
OData is queryable, as we can see we can define our own query and we could see that the implementation is very straight forward.
OData with Data Transfer Object (DTO)
In the implementation above we return the whole Person entity into real world, for internal system perhaps it won’t be much problem but still if the data contain sensitive information such as “password” it will cause many problems. Usually to handle this issue the best practise is to use Data Transfer Object as the returned object.
The problem with OData is because it uses do “Direct” query to our DBSet it will always return the entity type, so we need to somehow do the query with Entity type but modify the result into DTO.
Create a new controller and do some modification from the code above
In the example we receive the ODataQueryOptions with the type of Person, and return value of PersonDto, it allows the query to be received and used later. In the logic we can see that the query applied into the DBSet and then mapped into DTO.
Let’s do a query and see the result
Query: https://localhost:44354/api/person-vm?$filter=title eq ‘Mr.’ and personType eq ‘SP’
OData and Pagination
Pagination is a common practise to present data, it helps the performance of retrieving data and avoid big chunk of data to be sent to caller. Let’s see how OData handle the server-side paging.
In order the pagination to work we needs to define the PageSize and ensure the caller define “count=true” so we can get the total count of records. Let’s see it in action.
The response is limited into 15 records and we are given a link to next page.
We have seen OData in action, we can see how efficient the logic in server-side and how flexible to the caller to build their query to get data. Its really helpful where we can just expose 1 Get endpoint without much logic to filter, select or pagination.
Ability to convert to DTO also useful so we can modify the data that returned to the caller, but we need to be smart on defining it, because with the DTO some ability like “expand” can be limited. We need to choose when it is secure to sent the entity class or dto class. Beside DTO we can hide field from caller by using IgnoreDataMember attribute the only problem is sometime we need more flexibility and do some calculation when populating DTO.
But in the end OData will help us to speed up our development time because it’s straight forward and save us from many Get Methods that perhaps serve very specific needs of data.
*Any questions get in touch with us via the contact form