Supercharging ASP.NET Core API with OData

Avatar

Hassan

Summary

In this article, I’m going to show you how you can supercharge your existing ASP.NET Core APIs with OData to provide better experience for your API consumers with only 4 lines of code.

For the purpose of this tutorial please clone our demo project WashingtonSchools so you can follow up and try the different features we are going to talk about in this article.

You can clone the demo project from here:

https://github.com/hassanhabib/ODataDemo

 

What is OData?

Let’s talk about OData …

OData is an open source, open protocol technology that provides the ability to API developers to develop Queryable APIs, one of the most common things API developers need is pagination for instance.

With OData you can enable pagination, ordering of data, reshaping and restructuring of the data and much more with only 4 lines of code.

One of the most common scenarios today is when developers call an endpoint and pull out data that they are going to filter, reshape and order later on the client side.
This seems a bit wasteful. especially if the data is large enough that could cause latency and require further optimizations for a better user experience.

 

Getting Started

To get this started, this tutorial assumes you already have an API that provides some list of objects to your end users, for the purpose of this tutorial, we are going to use a sample WashingtonSchools API project that we built to demonstrate this feature.

Our API endpoint api/students returns all the students available in our database.

To enable OData on that endpoint, we are going to need to install a nuget package for all OData binaries that’ll enable us to turn our endpoint into a Queryable endpoint.

The nuget package we are targeting here is:

https://www.nuget.org/packages/Microsoft.AspNetCore.OData

 

Once that’s installed, let’s go ahead and add OData services to our API.

To do that, go to: Startup.cs file and add in this line of code in your ConfigureServices Function:

Now we need to enable the dependency injection support for ALL HTTP routes.

To do that, in the same file Startup.cs let’s go to the Configure function and add these two lines of code:

 

The first line of code enables the dependency injection to inject OData services into your existing API controller.

The second line of code determines which OData functionality you would like to enable your API consumers to use, we will talk about Expand, Select and OrderBy in details shortly after we complete the setup of OData on your project.

The last line of code we would want to add is an annotation on top of your API controller method, in our example here, we have a StudentsController class that has a GetStudents method to server all the available students in our database.

Now all we need to do is to add this annotation on top of your GetStudents endpoint as follows:

 

So your controller method code should look like this:

Now that the setup is done, let’s test OData select, OrderBy and expand functionality.

 

Select

On your favorite API testing software or simply in the browser since this is a GET endpoint, let’s try to select only the properties that we care about from the students objects.

If the students object consists of an ID, Name and we care only about the Name, we could call our endpoint like this:

The results will be a json list of students that only has the Name property displayed. try different combinations such as:

The select functionality enables you to control and reshape the data to fit just your need, it makes a big difference when your objects hold large amounts of data, like image data for instance that you don’t really need for a specific API call, or rich text that is contained within the same object, using select could be a great optimization technique to expedite API calls and response time.

 

OrderBy

The next functionality here is the OrderBy, which allows you to order your students based on their names alphabetically or their scores, try to hit your endpoint as follows:

You can also do:

 

Expand

One other functionality we want to test here is the expand. The expand functionality comes with a great advantage since it allows navigation across multiple entities.

In our example here, Students and Schools are in a many-to-one relationship, we can pull the school every student belongs to by making an expand call like this:

Now we get to see both students and their schools nested within the same list of objects.

 

Filter

The last functionality here is the Filter. Filtering enables you to query for a specific data with a specific value, for instance, If I’m looking for a student with the name Todd, all I have to do is to make an API call as follows:

The call will return all students that have the name Todd, you can be more specific with certain properties such as scores. For instance: if I want all students with scores greater than 100, I could make the following API call:

There are more features in OData that enables even more powerful API functionality that I urge you to explore such as MaxTop for pagination.

 

Final Notes

Here are few things to understand about OData:

  1. OData has no dependency whatsoever on the entity framework, it can come in handy with EF but it doesn’t depend on it, here’s a project that runs an OData API without EF: https://github.com/hassanhabib/ODataWithoutEF
  2. OData can give you more optimization with EF if you use IQueryable as a data return type that IEnumerable since IQueryable only evaluates after the query is built and ready to be executed.
  3. OData is much older than any other similar technology out there, it was released officially in 2007 and it has been around since then being used in large scale applications such as Microsoft Graph which feeds most of Microsoft Office products and XBOX in addition to Microsoft Windows.

 

The Future of OData

OData team continues to improve the feature and make it even easier and simpler to use and consume on existing and new APIs with both ASP.NET Web API (classic) or ASP.NET Core and we are working on adding even more powerful features in the near future.

Avatar
Hassan Habib

Sr. Software Engineer, Microsoft

Follow Hassan   

48 comments

Comments are closed.

  • Hector Mota
    Hector Mota

    Hey I didnt know that this actually exists O.o so this is similiar to graphQL. ? Hey thanks for info man

  • Avatar
    Jordan King

    Thanks, Hassan, great article(loved the video too on the MS developer channel).  I am going to use .net core and Angular for my next major project.  I would love to see how you would implement api endpoint security. For example, if an admin can select and filter a list of clients vs the same client endpoint secured for a single client so they can only filter/get their own client data.  Do you recommend exposing different endpoints based on security access or can you handle this with the same endpoint?  For context, I am planning on using OpenApi with Swagger/nSwag studio to auto-generate endpoints and typescript classes and possibly fluent Validation as well.

  • Avatar
    Peter St Angelo

    I like this technology.  Can I use Odata with CQRS (with MediatR) -if so, are there any examples of it?

  • Avatar
    zephania Eliah

    how can to make odata work with swagger as after implementing Odata over my api swagger specification breaked up

    • Avatar
      Daniel Smith

      I’d like to know this too.  I get the following in .NET Core 3.0 Preview 4:
      “Using ‘UseMvc’ to configure MVC is not supported while using Endpoint Routing.”

      • Saurabh Madan
        Saurabh Madan

        This is a known issue with OData libraries and we are currently investigating the work that needs to be done. We hope to provide more updates on this work in the coming months. 

  • Avatar
    华 范

    Great Article~!  I was writing hundreds of endpoint methods in my ASP.NET Core project until I saw this article.

    I tried, but it doesn’t work. No matter what I have tried e.g. in PostMan
    {{server}}/api/posts/odata?select=Title
    the endpoint always returns back the full data rather than a list of titles.

    Btw, I have Swagger in my project. Is it the cause of my problem??

    Please help. Thanks~!
    Winston

  • Avatar
    兴杰 严

    odata is a very powerful feature, I have been using it for 3 years.I like it very much, but it always disappoints me.There are many issues on github that nobody cares. As more and more people pay attention to graphql, I think there will be many people paying attention to odata at the same time.I hope that odata can catch up with it.

  • Avatar
    David Shukvani

    Hello Hassan,
    Thank you for your blog post! it looks promissing, but could you review aspects of connecting the url to Excel OData add-in? I tried to use it there, but got error: “OData: The given URL neither points to an OData service or a feed”.

    Best regards,
    David

  • Avatar
    Cameron Stillion

    Great article Hassan!
    I’m struggling to get this to work with the async web app model, however. async Task<ActionResult<…>> seems to wreak havoc with the OData libraries. Can this not be used with the async model? Even your sample ODataWithoutEF seems to break as soon as I add an ODataQueryOptions to any of the operations… am I missing something?

  • Fabio Ferreira
    Fabio Ferreira

    Wow. This seems too good to be true. Is there anything else I should consider? Does this affect perfomance?

  • Marcos Paulo Honorato
    Marcos Paulo Honorato

    Dear Hassan Habib, congratulations for the post. I am doing some OData testing using ASP.NET core 2.2, however count (http: //localhost: 5021/api /students /$count=true) does not work. I would like to know if you have already performed any tests using count. I’ve been searching, but I have not found a solution.

  • Avatar
    Dave Smith

    I understand how to use MaxTop, $skip, $top to page the data but how does one get the total count to know the number of pages? $count=true does not return the count in the result.

  • Sai Rachana Gopanaboina
    Sai Rachana Gopanaboina

    Hi Hassan, 

    I have tried all the querying attributes with OData v4.0 in asp.net core 2.2, but $search is not working.
    Could you please suggest anything on this?
    Exception:
    {"message":"The query parameter 'Specified argument was out of the range of valid values.\r\nParameter name: $search' is not supported.","exceptionMessage":"Specified argument was out of the range of valid values.\r\nParameter name: $search","exceptionType":"System.ArgumentOutOfRangeException","stackTrace":" at Microsoft.AspNet.OData.EnableQueryAttribute.ValidateQuery(HttpRequest request, ODataQueryOptions queryOptions)\r\n at Microsoft.AspNet.OData.EnableQueryAttribute.<>c__DisplayClass1_0.<OnActionExecuted>b__3(ODataQueryContext queryContext)\r\n at Microsoft.AspNet.OData.EnableQueryAttribute.ExecuteQuery(Object responseValue, IQueryable singleResultCollection, IWebApiActionDescriptor actionDescriptor, Func2 modelFunction, IWebApiRequestMessage request, Func2 createQueryOptionFunction)\r\n at Microsoft.AspNet.OData.EnableQueryAttribute.OnActionExecuted(Object responseValue, IQueryable singleResultCollection, IWebApiActionDescriptor actionDescriptor, IWebApiRequestMessage request, Func2 modelFunction, Func2 createQueryOptionFunction, Action1 createResponseAction, Action3 createErrorAction)"}

  • Norbert Tóth
    Norbert Tóth

    Hi Hassan!

    Thanks for the arcticle. We currently building an ASP.NET Core + angular application on Azure App Service and Azure SQL. The app can access using azure ad auth. The app has an ODATA endpoint which should be accessed by Excel to allow data reporting using organizational account. However we can only make it work when the ODATA endpint is allowed anonymous access. We are unable to find any working sample regarding ASP.NET Core OData + Azure AD Auth + Excel access. I wonder if you might have come accross this scenario? We would expect it to pretty common, but we unable to find any resource… Any help would be appreciated.

  • Avatar
    Serhiy Bondar

    Great article, very easy to connect to existing REST API.
    Lookls like $count doesn’t work even after I have turned it on in Startup.cs -> ConfigureServices -> routeBuilder.Expand().Select().OrderBy().Filter().Count();
    How to make it work?

  • Arkesh Kumar
    Arkesh Kumar

    I am using OData and I observed one thing in response, It’s returning metadata and that metadata including server address(URL), So I want to remove same, If anyone can help me to achieve this I will be thankful.

  • Avatar
    Shimmy Weitzhandler

    Can you please elaborate this article on how to make the OData service return OData with ASP.NET Core 2.2?

  • Avatar
    Raj Kumar Pandiarajan

    Hi,
    I have tried the example of Odata without EntityFramework.
    It works fine. I am new to Odata so i have few doubts that may be very basic.
    1. Filter query in odata without EF : while returning all data as ‘IEnumerable’ list, Odata query run on the returned data set and returns filtered data(for example 5 records out of 10). Is my understanding is correct ?
    2. Odata without EF : in real production application, there may be millions of record residing inside DB. In this case bring all data from repository layer to controller and applying odata query like in example will bring performance down right? If so how to make odata query directly run on db sql query in repository layer?
    3. When we use EF, while using db context, will the odata query run directly on db and brought only particular data(5 out 10 directly from DB ?

  • Avatar
    Avijit Samanta

    Hi Hassan, I have a query related to underlying data provider. For example if I have a underlying data provider as Azure Table storage or Azure Cosmos Db SQL API, will it transform the OData query to underlying Db query with OData and EF together, given that I have provided correct connection string?