if you knew how to cook it, you could use make older EF Core (and original EF) go brrr. you sound like you do know. my problem is that it is hard to find out what exactly is the best without dropping down several abstraction layers, sometimes, as you noted, to raw SQL.

why not just construct SQL queries in a type-safe DSL, like, say, JetBrains Exposed does? you are writing what's basically SQL that your compiler understands and your existing tooling checks for free. (granted, C# may need an additional Roslyn analyzer, but it's still simpler than either guessing what transaction LINQ will make or writing SQL in strings.)