I agree about the limitations of ORMs.

However I have had great luck with using an ORM to load up the database and data, and then having a unit test that calls the function which does raw SQL in the middle. And now the raw database tests are integrated with the unit tests for the rest of the environment in a way that keeps them synchronized with the application code that also interacts with the same database.

And, of course, the limits of ORMs do not change the fact that they have use cases.

The problem with writing raw SQL (which I do personally prefer myself, too) is now you need to generate types and/or mappings for each distinct query’s resultset schema - doing that by-hand is tedious and error-prone (or use untyped dict objects for every row, ew) - so what you really need is a project build-step that finds every query in your project and runs it against a prototype database instance in order to get schema result typing info, then generates the strong-types/mappings code for you before everything else gets compiled…

…and it works - but now you have possibly thousands of classes/structs that are all-so-similar but also subtly different - namely disjoint members (so they can’t exist in an inheritance hierarchy, e.g. NewUser won’t have a UserId value, result-types would be immutable, unless they need to be mutable, etc…). It’s all such a huge pain. In a C# project of mine that does something like this, it means that every business-entity typically has at least ~5 actual class/struct/interface types associated with it: e.g. NewUser, IReadOnlyUser, IWritableUserValues, struct UserKey, MutableUser, UpdateUserValues, etc.

…surely there’s a better way?