[to11mtm]

The happiest middle ground I've found in .NET has been LinqToDb.

It's more of a Micro ORM, -but- has a Linq DSL, as well as DSLs for lots of DB bits. CTEs, Window functions, Bulk copy, 'treat this in memory collection as an input rowset', certain DB Specific bits... and if you need some special sauce to deal with brownfield jank [0] it's very easy to wire-up custom SQL bits into your queries via attributes if needed.

If you use method syntax rather than linq query syntax, you will have minimal surprises with the SQL generated. Typically if it does generate something I didn't expect, I dig in and what it did was indeed both correct and better than what I was trying to do anyway.

[0] - Fun nasty case I ran into on a brownfield project; 'If this number has a decimal point, it is a direct percentage rate. If the number does NOT have a decimal point, it is the FK to a lookup table that has the percentage rate'