The only criticism I have of Linq is that it is a more general language targeting different back-ends including plain old objects. If Linq did not allow things that do not make sense in SQL this would be a little less jarring.

I also wish it supported full outer joins, unions, and window functions.

I was able to hire a four or five contractors who never used Mongo just by requiring Entity Framework/Linq experience and less than 4 hours of training.

WITH statements alleviate some of this issue by allowing you to write subqueries in any order.

[1] https://thoughtbot.com/blog/advanced-postgres-performance-ti...

Exceptions:

1. If the results of the CTE are used more than once then it is materialized by default, though you can override this by adding "NOT MATERIALIZED" to the call.

2. Recursive and INSERT/UPDATE/DELETE CTEs are always materialized.

https://paquier.xyz/postgresql-2/postgres-12-with-materializ...

This may be true on archaic versions of MySQL and Postgres but is not the case today, barring some esoteric edge cases (bugs) where the optimizer gets thrown out of whack. Once while doing data science consulting I rewrote a ~1000 line query in Aurora (MySQL flavored) which had a ~2.5s runtime, which was far too slow for the client's use-case.

After rewriting all the CTEs (there were many) into subqueries, there was a 2-3% increase in query speed, barely (on the order of under a tenth of a second). There was a very tiny improvement far smaller than the normal variance of the runtime.

Then I rebuilt the query and the joins, and was able to get the query to consistently run in the range of 0.8 - 1.2s. For my own purposes I then duplicated the query, re-implemented the CTEs, and did validate that indeed there is only a negligible increase in query time when using CTE.

What is beautiful and consistent is the relational algebra. The relation model relies on this formalism to model data by making some rather strong assumptions about how tuples and relations represent things and how relational operations are used to process them. And these assumptions are precisely what propagates to SQL and what some authors (see references in the article) consider controversial and messy. Then the question is whether and how these controversies can be fixed (or whether they are bugs or features).

A radically different approach to fix these controversies is to introduce a different formalism and different data model (as opposed to fixing only syntax) which is based on using functions. In other words, instead of using sets and set operations, we use in addition functions and function operations [1]. Here you can find an implementation of this approach:

https://github.com/prostodata/prosto Prosto is a data processing toolkit radically changing how data is processed by using both sets and functions and being a major alternative to map-reduce, join-groupby and other set-oriented approaches

[1] Concept-oriented model: Modeling and processing data using functions: https://www.researchgate.net/publication/337336089_Concept-o... -> Read introduction (two pages) for why having only sets is not enough and why functions are important

FWIW, I find the relational model to be much nicer than typical object models. Even in Java, which I write every day at work, I've found it to be much clearer to use immutable objects representing records. Not just for interacting with the database, but just for handling information in general.

Of course, it would be extremely painful to do without Lombok's @Value, @Builder, and @With annotations.

I mean, many RDBMS's have support for JSON and/or XML, so you can sort of get it.

However, I wonder if we'd be better of as an industry if we dealt with our data in code in a more relation-y way than an object-y way.

I do hate most ORMs. The only oneS I like are Linq based ORMs. The “ORM” is actually well integrated into the language.

You might as well use something like Dapper.

But yes agreed about ActiveRecord.