CQRS is a breath of fresh air after years of dealing with ORM overreach and inflexibility.
I happen to be of the opinion that ORMs are good for prototyping and very small data models and one-off type projects but not for big leagues, production ready code. For that I would do as you describe and build my own data connector.
But on every project I'd spend a lot of time fighting with it, trying to bend it to my will. It's invariably difficult to create mappings with any complexity, such as composite keys. And the queries they generate are usually inefficient monstrosities.
I discovered CQRS and micro-ORMs a few years ago, and with few exceptions, I haven't looked back!
Some languages - particularly languages with overly rigid and static type systems - are ill suited for building ORMs, so the ORMs in those languages will probably always suck.
I think attachment to those languages is what drives some people to think that "ORMs always suck".
So...yeah?
I occasionally dip a toe outside to other frameworks and setups and always end up having to keep trying to pull more and more pieces together that I end up getting frustrated and just using Rails.
I imagine that everyone's experience is vastly different given the scopes and problems that you're trying to address. I write a lot of little one-off business productivity apps, SAAS backends, etc. YMMV.
I've found the right ORM a joy to use on small-medium size projects, with a simple data layer using a database driven approach (ie not code-first).
The key has been to be able to blow away the ORM code and recreate it at will with no more than a few seconds work (an application of the Factory pattern..).
With this approach the ORM got out of the way and didn't cause friction. Then combine the generated classes with functional LINQ/map reduce to enable complex, expressive querying code in the first class language of choice.
As this is a controversial topic for some, I want to make a few disclaimers .. - This has worked very well when I've kept the database simple, with logic in the application not the db. (Constraints in Db though). - Extending the DAL domain can be done through partial classes, that are kept when the ORM code is blown away and recreated. - As a bonus of this style, the database is done with standard sql, and could be swapped for another relational one easily. - Upgrade scripts need to be created for database migrations, tooling is available to autogenerate these from snapshots of development databases, and manually extended where needed. - I treated the Db as a Db, not some magic object oriented thing, and handled the (fairly minor) impedance mismatch in application code. - You can always hand optimise some sql if needed, or even call direct bulk update APIs if you need top performance in specific parts of the application .. I use the ORM was used as a unit of work pattern so it's possible to do other units of work in other data access technologies, when optimising for something other than developer ease.
On the other hand I've seen configuration heavy ORMs suck a lot of time and cause heartache ..
Of course, ymmv, but using the approach outline above it has literally been a breeze, and a heartwarming experience for me and other devs when they get used to it, on dozens of projects done this way.