If one can update the underlying Db, I've developed an ORM pattern that I use in my projects that works very well.

They keys is to encapsulate most of the (possibly complex) CRUD logic in Db functions (for retrieval these would be table-valued functions) and access these from the application side as virtual tables.

I also have capable but easy to use filtering/sorting/paging operators and combinators in the ORM that are translated into SQL where/sort/limit clauses.

Because the heavy lifting is already done by the db functions (which you have full control of to use whatever SQL you need), the pattern is actually quite powerful but easy to use.

You can define virtual read only tables, several virtual tables that access the same actual table in different way, custom operators that transcend SQL, etc

▲

webstrand 15 hours ago | parent [-]

Doesn't this break the object-relation mapping the ORM would be trying to do? Since the views would be producing resultsets that do not map to one single object? Or do you just define a new object type with virtual relations?

	▲	prmph 14 hours ago \| parent [-]
		Exactly. I define a pair of DTOs for each virtual table: an input type (the shape of a record that can be inserted) and an output one (the shape of each record returned when you select from the virtual table, which might include generated, computed, and foreign columns, etc). These types do not necessarily have to map to the underlying table types.