Most people who complain about ORMs complain about the input - the querying aspect of them. And it is true, particularly for complex queries ORMs can be pretty hideous. If a query goes beyond a certain level of complexity I would much rather replace them with a series of views or stored procedures. ORMs are not good for complex queries.
For me, where ORMs do shine is with their output. If you have two tables, A and B with one to many relationships between them - with pure SQL running a join on these tables will return a single result set. Table As data will be duplicated for each row of B. With an ORM you can get back a single object A containing a collection of B's rows.
This is enough reason for me to reach for an ORM for anything but the simplest of problems.
For me, where ORMs do shine is with their output. If you have two tables, A and B with one to many relationships between them - with pure SQL running a join on these tables will return a single result set. Table As data will be duplicated for each row of B. With an ORM you can get back a single object A containing a collection of B's rows.
This is enough reason for me to reach for an ORM for anything but the simplest of problems.
Lazy loading generally also comes for free.