See https://vladmihalcea.com/

He has a lot of info on high performance Hibernate and particularly the n + 1 issue.

It should be mostly avoidable.

I haven't found that to be the case. In particular I think expecting the ORM to always do the best thing no matter how you use it is folly, ORMs are a tool that you have to take the time to understand in much of their full complexity.

Some would say this makes them a bad abstraction, but to me, data mapping is going to have to happen somewhere, and I would rather be building on someone else's work to write the data mapping for my applications than do it from scratch. You have to know when the ORM is the right tool to use, and when to drop into plain SQL for your querying, because they do not eliminate the need to write SQL, just reduce it significantly.

Many ORMs are going to have trouble at some point when you start making more complex queries. I believe using an ORM well is largely in understanding the balance between when its tradeoffs are acceptable and when they are not. Per the other reply, I would probably start with EF Core in new applications, but would not hesitate to add Dapper or just plain SQL with ADO if I saw the need.

Is there any reason why it needs to be either/or, or are you suffering from OCD light like so many others of us? ;-)