Asp.net Core OData - Query Database Over Url
One day, I was checking my youtube notifications and I encountered a video from Microsoft Developer page.
I watched the video and loved the feature of OData and wanted to write a blog post about it. I guess you will love it too!
When I first searched OData on Google, I found very very different examples and posts.
First of all, there is a really big difference between Asp.net OData and Asp.net Core OData. Also, there are differences in usages. Shortly, I will write this blog using Asp.net Core 2.0 and OData 7.1
What is OData ?
OData (Open Data Protocol) is an ISO/IEC approved, OASIS standard that defines a set of best practices for building and consuming RESTful APIs.
OData(Open Data Protocol) protocol is created to provide us filtering, expanding, sorting, paging abilities over URL. While coding your front-end or mobile app, you can do whatever you want over URL. Pagination, Response only contains “test” names or taking category in your production object. You don’t need to ask a back-end developer to do these tasks. This means you don’t need to wait for a back-end developer, but this also means you must be careful about what you query. You can make queries that have thousands of objects that throws a timeout exception :)
What is the Advantage of Asp.net Core OData
That’s all good so far. As a developer who worked mostly as a back-end developer, a question came to my mind: If you want to filter or order the result, you must return all the resultset first and do whatever we want on it. If you are working on a small dataset, this can work and it may not cause a problem. But, imagine you are working for an E-Commerce company and returning a list of products for your listing page! There might be thousands or millions record. It will most probably take too much time and throw a timeout exception while you are waiting for the response. Or even you accept that timing, it may cause a network problem later or a lot of network cost.
After watching the video, of course, I asked this question on youtube in case someone sees and answers. It was Hasan Habib who answered the question, presenter of the video :)
It depends on the return type, if your controller method is returning an IEnumerable it will perform differently than if it was returning IQueryable. if it’s IQueryable OData will perform the search on the database via EF, if it’s IEnumerable it will perform the query on the returned results. hope this helps.
Excellent feature!
After reading this, I had a big reason to examine OData :) I immediately created a text project and inserted only 3 lines of code to struct OData as told in video. Adding OData to an existing project or a new one is very simple. Just add 3 lines of code and get all the features that OData provided us. Let’s continue to examine the test project.
Add Microsoft.AspNetCore.OData library to your project over Nuget. I will try to tell you the lines I enumerated below.
1- I’m defining the DbContext on this line. I defined 2 options. InMemory and local-DB. We can’t see SQL outputs while using InMemory DbContext. Because of that, I ran the project on local-DB, just to have shiny SQL outputs and show you the magic.
2- This short line injects OData and all features to our project.
3- For seeing DbContext SQL logs, I’m adding debug-log.
4- I’m selecting the features of OData to use on this line. You can add other features or delete some of them. If you delete for example filter and try to filter over URL, it will throw 500 exception.
1- Injecting log to DbContext to write SQL logs to output windows.
1- With EnableQuery, I’m giving this endpoint permission to use all OData features. If you don’t add this attribute on an action, OData features won’t be enabled for that action.
This is the whole code that I wrote, this is the only 3 lines of code! If you wonder about the whole project, you can visit it on GitHub.
Results
Select only Id field of products
Select Id and Name fields of products
Select Products that has Name=Test condition
Order the Products table descending by name field
Select category info with products(category join)
Generating the URL
If you have a question like “how do you generate your url” you can check the table below. You will find all you need.
In this post, I tried to tell you a bit about Asp.net Core OData, how to use in a project and how to query over Db. If you liked, please remember to press the like button and share, thanks:)
References
https://www.odata.org/documentation/odata-version-2-0/uri-conventions
