I am going to play the devil's advocate in this sea of ORM-haters.
First of I am an avid EF-Core developer. But I feel like I have a pretty good grasp on when I should let the datbase do the stuff the database is good at (cascading deletes). And I have experienced times where I had to use an SP because EF-Core was slow and would lock up a table or two.
This article shows an example of an ORM system that makes use of raw sql to construct its final queries. I can't be the only one that thinks that that is weird. Why use an ORM that doesn't abstract from the database - that's half the point of an ORM. And I think that is the key point of ORM... Abstraction.
Some of the advantages I can think of when it comes to ORM.
* ORM speeds up the development time. Now you don't have to write long queries. In EF-Core for example we just use LINQ either with method or query syntax. Query syntax already looks a lot like sql.*
* More tools. EF-Core Migration is great. Now I can spin up an mssql container. Quickly create a bunch of tables. Then go ahead and run integration tests.*
* Because the database is abstracted. I can now run unit test on an in-memory database where I can have whatever data I want and I am not depended on queries being only compatible with MsSql.*
* If anyone has ever worked with ASP. Not ASP.NET. Just ASP. Legacy technologies appears irregardless of age. When writing queries you might just try to convert a bool to a text string. Works fine on your computer. Shit's the bed on the server because the server's language is something other than English. Argueably this could have been prevented if ASP weren't just so ASP. But also an ORM could have prevented it.*
* I don't have to worry about the dialect of the database I communicate with. Because the ORM takes care of that. This also incldues the formatting of my DateTime variables.*
* Error handling next. I can specify in EF-Core how big many characters can be in my column. And do error handling accordingly if the amount of characters are exceeded. This is the power of code-first approach - something I believe is a strong approach to designing your database scheme.*
* Last small advantage is that I can combine it with GraphQL and then I wont have to make a new endpoint for every new "edge-case" request.*
Disadvantages I can think of.
* Slow. Naturally when you add an abstraction layer it becomes a bit slower than if you were to use raw sql. So if you work with a a lot of data at once you have to rethink your approach. And adding GraphQL into the mix is another abstraction layer that is just going to make it slower.*
* Implementing the database scheme is definitely faster using db management system rather than a code first approach.*
* Merging in EF-Core is for me still a lost cause. You first have to grab all the rows to see which matches and then update this whilst inserting the rest. You can probably pay your way out of this one using BulkExtensions at the fantastic price of $1000/year. Currently I solve this using a temp table and a stored procedure.*
* Duplicate and unnecessary operations has been apparent until EF-Core 7.0. Before when I had to delete or update an entity I would have to first fetch it up the database in order to track it before I can do either. This is two db-transactions that can be reduced to one in EF-Core 7.0 now. But before that was an absolutely pain.*
* Similarly to previous point. Adding an entity to a database is slow and can result in deadlocks if you have a lot of inserts happening rapidly. I've cirumvented this using EFCore.BulkExtensions which does an optimized insert for each dataprovider. But using a third party library even when is free is a pain... Especially since the repo owner now have gone the way of paid licensing of the library.*
Complex queries such as partion by and pivots is not possible in EF. Probably never will be. But in regards to partion by I think it should be implemented at some point mostly because I have experienced I can make a query many times faster by using it.
Summary
ORMs are great. Especially in REST services when your queries are small. But don't use ORMs when you have to handle a lot of big data.
First of I am an avid EF-Core developer. But I feel like I have a pretty good grasp on when I should let the datbase do the stuff the database is good at (cascading deletes). And I have experienced times where I had to use an SP because EF-Core was slow and would lock up a table or two.
This article shows an example of an ORM system that makes use of raw sql to construct its final queries. I can't be the only one that thinks that that is weird. Why use an ORM that doesn't abstract from the database - that's half the point of an ORM. And I think that is the key point of ORM... Abstraction.
Some of the advantages I can think of when it comes to ORM.
* ORM speeds up the development time. Now you don't have to write long queries. In EF-Core for example we just use LINQ either with method or query syntax. Query syntax already looks a lot like sql.*
* More tools. EF-Core Migration is great. Now I can spin up an mssql container. Quickly create a bunch of tables. Then go ahead and run integration tests.*
* Because the database is abstracted. I can now run unit test on an in-memory database where I can have whatever data I want and I am not depended on queries being only compatible with MsSql.*
* If anyone has ever worked with ASP. Not ASP.NET. Just ASP. Legacy technologies appears irregardless of age. When writing queries you might just try to convert a bool to a text string. Works fine on your computer. Shit's the bed on the server because the server's language is something other than English. Argueably this could have been prevented if ASP weren't just so ASP. But also an ORM could have prevented it.*
* I don't have to worry about the dialect of the database I communicate with. Because the ORM takes care of that. This also incldues the formatting of my DateTime variables.*
* Error handling next. I can specify in EF-Core how big many characters can be in my column. And do error handling accordingly if the amount of characters are exceeded. This is the power of code-first approach - something I believe is a strong approach to designing your database scheme.*
* Last small advantage is that I can combine it with GraphQL and then I wont have to make a new endpoint for every new "edge-case" request.*
Disadvantages I can think of.
* Slow. Naturally when you add an abstraction layer it becomes a bit slower than if you were to use raw sql. So if you work with a a lot of data at once you have to rethink your approach. And adding GraphQL into the mix is another abstraction layer that is just going to make it slower.*
* Implementing the database scheme is definitely faster using db management system rather than a code first approach.*
* Merging in EF-Core is for me still a lost cause. You first have to grab all the rows to see which matches and then update this whilst inserting the rest. You can probably pay your way out of this one using BulkExtensions at the fantastic price of $1000/year. Currently I solve this using a temp table and a stored procedure.*
* Duplicate and unnecessary operations has been apparent until EF-Core 7.0. Before when I had to delete or update an entity I would have to first fetch it up the database in order to track it before I can do either. This is two db-transactions that can be reduced to one in EF-Core 7.0 now. But before that was an absolutely pain.*
* Similarly to previous point. Adding an entity to a database is slow and can result in deadlocks if you have a lot of inserts happening rapidly. I've cirumvented this using EFCore.BulkExtensions which does an optimized insert for each dataprovider. But using a third party library even when is free is a pain... Especially since the repo owner now have gone the way of paid licensing of the library.*
Complex queries such as partion by and pivots is not possible in EF. Probably never will be. But in regards to partion by I think it should be implemented at some point mostly because I have experienced I can make a query many times faster by using it.
Summary
ORMs are great. Especially in REST services when your queries are small. But don't use ORMs when you have to handle a lot of big data.