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.
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.
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.