ASP.NET Core: A powerful API Design with OData, EF and SQLKata
Rather than reinventing the wheel, you may need a fast and reliable solution in a short time span. In this case, your framework should be able to support all your requirements. ASP.NET Core is doing that very well.
I will cover everything under REST API design.
API Design
Select
Let’s first speak about what we know about the performance of an API design. Assume that we have entities over 1M+ rows in a database system. You can’t show that much data directly with an UI, you have the split into pages. In other words, we can say pagination. I may apply a “skip and take” methodology and show paged data.
This would be enough for basics. The things are starting to get more complex when you need more functionality. The given example could expanded with search, order, select and group functions, etc. So you have to implement each one of them and consider all possibilities. It would be a waste of time and increases the likelihood of errors. OData answers your call here. It presents a good API support for such needs and works with Entity Framework for query builder.
Patch/Put/Post/Delete
REST & Entity Framework & OData can handle these actions painlessly.
I didn’t include the validations. You can see a full example from Microsoft’s site. So far so good, we handled CRUD operations and exposed an API.
Projection
The whole scenario is based on an entity on a database system which means it is can be a table, a document, etc. However, you may want to use OData features with a projection. In other words, you have a View on SQL that joins multiple tables and collects bunch of data.
You might say, each entity should be separate and live apart. In this case, you might think like your database system is old, big and growth uncontrolled. Thus, SQL View could help with your needs.
Therefore, CRUD operations couldn’t work and you have to handle the operations manually with respect to the columns.
Scenario
Assume that, you have a song database. It has a restriction system for countries. And, you want to design a webpage that allows you to control the songs status over the countries with a checkbox.
CountryStatus will present the collective status of the countries. If all countries are true then CountryStatus will ‘true’ otherwise it is ‘false’.
The structure is completed for the selection of the songs. Assume that, we are using an advanced data grid which supports OData such as DevExtreme, Telerik, etc. The user want to change the status of a song on all countries. And, the grid shows the songs with a checkbox. Hereafter, you have to handle the editing operations manually. Since Entity Framework does not support bulk changes, we need to use an extension package like EntityFramework Plus.
Patch
The patch method provides a Delta<T> object which stores the changes. We can handle the request;
The plus package produces the following sql.
I don’t think this one is a good update statement. The join statement is not necessary because it updates all rows with respect to an ID. My computer runs it in around 1.5 seconds. It works but it’s slow. In a future they could fix it. I will not go into Entity Framework Plus package details.
SqlKata
Since, the selection part is customized I would like to go with my own queries for customization and performance matters. SqlKata is a good query builder that allows you to compile your queries according to different database systems.
SqlKata produces the following sql
UPDATE [Country_Songs] SET [Status] = @p0 WHERE [SongId] = @p1This was what I expected. My computer runs the patch action between 30ms~100ms.
Conclusion
OData presents powerful features depending on your needs. The standard procedure is not always available for you and you have to improvise for each case. As a result, you have a powerful, fast and RESTful API concept.
See you on the next story !
