I feel the same way about most ORMs - except for LINQ. C# treats queries as first class citizens. There is something very powerful about being able to have a method in your repo...
public List<Customer> Find(Expression<Func<Customer,T>> expression) {...}
and then your client can use any random expression...
Find(c => c.age > 18 && c.age < 65 && c.gender == “M”)
and have that expression translated to either SQL, MongoQuery, or anything else that has a LINQ provider.
If done correctly, not only can you switch between RDMSs, you can switch between an RDMS and a Nosql data store without changing any client code.
When you are writing your unit tests, you can use in memory Lists to mock your data store and still test all of your queries without using a database.
Of course you also get type safety, autocomplete, and in the case of NoSql data stores, you enforce a schema over a schemaless data store and get to work with strongly typed objects.
I’ve never seen any purpose of an ORM outside of LINQ. They just add another level of complexity and still don’t integrate with the host language naturally.