> Microsoft at one stage had Linq to SQL which was quite good..... but they killed it :)
I think that might be like when rock stars die at 26 instead of hanging around long enough to become the subject of a VH1 "Where are they Now?" episode.
I was on a project that used LINQ to SQL long after Microsoft stopped development on it, and consequently fell out of love with it. The caching mechanism was flaky, and could cause really weird problems that manifested in non-obvious ways. Also, changing the database schema in a LINQ to SQL project could be a chore.
I ended up migrating us away from it and toward Dapper. We ended up bypassing it way too often to be able to straight-facedly claim it was saving us from having to understand SQL (if that was ever possible), It was the warts, but even more than that, it was the many situations where, to get the query right, we needed to use a recursive CTE, or a temp table, or a table-valued parameter, or any of the oodles of other things that are awkward or impossible with an ORM.
Dapper is great IMHO. However, the Microsoft-recommended solution after Linq to SQL ended up being Entity Framework... which was an even heavier ORM.
My personal opinion is akin to the parent: the more you abstract away from thinking like your data store, the easier it is to write poorly performing code, because some of the fundamentals get lost in the process.
For instance, in EF and other ORMs, one classic pitfall is "N+1" queries. Even though it's a well known issue to avoid, I still run into poorly performing loops quite often. Here, I feel the abstraction is partially responsible. Most for loops run in code (in memory at least) very quickly, even against a lot of records. However, those who develop against SQL know that you avoid cursors etc. as much as possible, and that for loops / cursors against a SQL database will bite you the instance you are dealing with thousands of records to loop through. I don't think it's always "obvious" to, say, a relatively new C# developer with less experience writing SQL, that this for loop against a SQL database is going to literally translate into thousands of select statements that will perform poorly.
I think it's possible to use a heavy ORM like Entity Framework and write well performing code, but there has to be some awareness of when the task is too complex for the ORM to perform well, and you need to switch to a stored procedure. And there has to be at least a little bit of understanding on how to write well performing interactions with the DB.
From a performance perspective, N+1 queries are spectacular, but the much more pervasive problem is simply that they encourage poor discipline about hitting the database.
Most ORM code bases I've looked at do a pretty decent job with avoiding N+1 queries. But they've all been guilty of doing things like incurring orders of magnitude more network transfer than is necessary by pulling down a collection of objects just to calculate the sum over a single field.
I think that stuff ends up being so common because it's exactly what heavyweight ORMs are encouraging you to do. To an approximation, their entire point is to make it easy to forget that you're talking to a remote resource.
And, for that matter, by hiding the subtlety of the relational model, they also hide the power of the relational model.
This just comes down to caring about the work you do and putting in the effort to ensure you are delivering quality/performant code. If your devs can't handle that then you have a lot more problems than a heavyweight ORM. No developer worth their salt would be "encouraged" by a library to write bad code.
I think that might be like when rock stars die at 26 instead of hanging around long enough to become the subject of a VH1 "Where are they Now?" episode.
I was on a project that used LINQ to SQL long after Microsoft stopped development on it, and consequently fell out of love with it. The caching mechanism was flaky, and could cause really weird problems that manifested in non-obvious ways. Also, changing the database schema in a LINQ to SQL project could be a chore.
I ended up migrating us away from it and toward Dapper. We ended up bypassing it way too often to be able to straight-facedly claim it was saving us from having to understand SQL (if that was ever possible), It was the warts, but even more than that, it was the many situations where, to get the query right, we needed to use a recursive CTE, or a temp table, or a table-valued parameter, or any of the oodles of other things that are awkward or impossible with an ORM.