September 28, 2019

Introducing OData With ASP.Net Core

Overview

OData shortfor Open Data Protocol allows developer to create a simple query able RESTFULAPIs. With OData the caller can freely filter, select and expand the data inserver side with a standard query and the implementation is very simple andstraight forward.  OData builds on HTTPand 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 dopagination, we need to include more detail information into certain depth anddetail, we need to filter the data that send, it will be better if the requestcan do query just like we have in Linq or Sql right? Well case like this isperfect with OData, with just one endpoint the client can do various query thatsuit for their needs.

Getting Started

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 startwe will need this:

  1. Northwind Database, it can bedownloaded from https://docs.microsoft.com/en-us/sql/samples/adventureworks-install-configure?view=sql-server-2017
  2. Microsoft.EntityFrameworkCore.SqlServernuget Package
  3. Microsoft.AspNetCore.OData nugetPackage
  4. Scrutor, this is a helper forDependency 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/
  5. ValueInjecter nuget Package

First, weneed to restore the Database, create a new Asp.Net Core Project and ensure theconnection 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 areusing interfaces to make our life in Dependency Injection easier, we can useScrutor to do assembly scan and register all the classes in the DI

Now we canstart to implement the OData Part. First of all, we need to register the ODatawithin the Startup class.

OData Implementation

Add thisline to ConfigureServices method:

And thefollowing lines to Configure Method:

It willregister 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.NetCore controller not ODataController.

After allset up we are ready to implement the OData endpoint. Create a new controllerand put these lines:

It’s justordinary controller and in the action method we just need to return our DbSetwhich is an access to the DB Table and put attribute EnableQuery. Now, lets tryour work using Postman.

query: https://localhost:44354/api/person-odata

result:

Perhaps youwill notice that its very slow and heavy, because its return all of the dataand the size is almost 7.71 MB, now let’s try another call

Query: https://localhost:44354/api/person-odata?$top=20

Result:

Now it onlytakes 20 records, and we are doing this without any extra implementation in theserver side. Let’s call another query

Query: https://localhost:44354/api/person-odata?$filter=titleeq 'Mr.' and personType eq 'SP'

Result:

Query: https://localhost:44354/api/person-odata?$filter=titleeq 'Mr.' and personType eq'SP'&$select=businessEntityId,personType,title,firstName

Result:

OData isqueryable, as we can see we can define our own query and we could see that theimplementation is very straight forward.

OData with Data TransferObject (DTO)

In theimplementation above we return the whole Person entity into real world, forinternal system perhaps it won’t be much problem but still if the data containsensitive information such as “password” it will cause many problems. Usuallyto handle this issue the best practise is to use Data Transfer Object as thereturned object.

The problemwith OData is because it uses do “Direct” query to our DBSet it will alwaysreturn the entity type, so we need to somehow do the query with Entity type butmodify the result into DTO.

Create anew controller and do some modification from the code above

In theexample we receive the ODataQueryOptions with the type of Person, and returnvalue of PersonDto, it allows the query to be received and used later. In thelogic we can see that the query applied into the DBSet and then mapped intoDTO.

Let’s do aquery and see the result

Query: https://localhost:44354/api/person-vm?$filter=titleeq 'Mr.' and personType eq 'SP'

Result:

OData and Pagination

Paginationis a common practise to present data, it helps the performance of retrievingdata and avoid big chunk of data to be sent to caller. Let’s see how ODatahandle the server-side paging.

In order thepagination 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.

Query: https://localhost:44354/api/person-paging?$count=true

Result:

Theresponse is limited into 15 records and we are given a link to next page.

Conclusion

We haveseen OData in action, we can see how efficient the logic in server-side and howflexible to the caller to build their query to get data. Its really helpfulwhere we can just expose 1 Get endpoint without much logic to filter, select orpagination.

Ability toconvert to DTO also useful so we can modify the data that returned to thecaller, but we need to be smart on defining it, because with the DTO someability like “expand” can be limited. We need to choose when it is secure tosent the entity class or dto class. Beside DTO we can hide field from caller byusing IgnoreDataMember attribute the only problem is sometime we need moreflexibility 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