One dark place some .NET devs got caught in the early days was some of the 3rd party ORMs. Nightmare of weird code, proprietary mapping tools and headaches.
I am still not sold on linq2sql either. Debugging that is never fun.
ORM-lites, such as Dapper, which only bind query results to objects, are an obvious exception thoug.
Every time I’ve seen a project use an ORM past the proof of concept phase, it ends up with some garbage layer to bypass or “trick” the ORM into doing something acceptable. (e.g. Complex materialized views presented as tables to the ORM so the ORM can just do straight selects without joins).
When I thought I needed better control and performance I wrote my first ORM. Which wasn't really all that ORM-y. It would use reflection to map to an object, but queries definitely weren't linq-able or using IQueryable and walking the predicates to dynamically generate queries. It was more like dataBase.TableName.First("id=@id", new {id = 1}); Which would generate "select * from TableName where id=@id" nothing earth shattering. Joins were done through dataBase.Join(new [] {"table1", "table2" }, new [] {"on table1.id=table2.table1_id"}, "table1.id=@id", new {id=1});
It was ugly and I eventually just replaced it with straight ADO.net.
I tried various other ORMs over the years, I eventually just settled on EF since it was not bad, and generates almost sane SQL most of the time.
It always made more sense to me to have a sanely-designed database first and then generate the mapping code from it, rather than the code-first style that seems to be popular with EF. You can do the database-first mode with entity framework, but it is less the happy path that the tooling and documentation tries to nudge you into.
I don't deny the value of the active record pattern where it works, but in almost every case I've used SQL over the past 5 years, I don't want a complex abstraction layer. I just want something to make it easy and safe to talk to the database for the 90% of straight forward queries we write.
With something like that, I feel as though I'd rather write my own active record system than use the configuration based systems we see with EF
I'm looking at you LLBLGen