Alternative query languages like EdgeQL show what first class support for nested data (and navigations) could look like, while the data model is still relational.
jOOQ emulates this feature for arbitrary databases and has the best explanatory article on the web about MULTISET imo
https://www.jooq.org/doc/latest/manual/sql-building/column-e...
For example how would you use it to return a list of events and, for each event, the list of attendees in that event?
(I always upgrade projects by first upgrading the runtime and checking everything then separately (and maybe much later!) upgrading the framework.)
Please enlighten me.
AsSingleQuery is as dangerous as this makes it sound. This works surprisingly well if you know that the number of included entities is low, but only then.
You can get much better queries here if you write a Select() and let EF Core translate that into SQL. That will probably do roughly want you are imagining here, usually with subqueries fetching the data from related entities.
Many of our customers routinely dealt with orders that have 10k+ lines. So if you did it all in one go, you've now turned 200 head fields into 10 million that needs to be transmitted to the client and deduplicated there.
We have the root primary key on all child tables, so we fire off a handful of queries to load the complete data set for an order. Very fast as it's all indexed of course.
The problem however is in how results are returned over the wire. Duplicating rows is needless, but seems to be still the standard.
---
EDIT: According to LLM friends you could achieve before wire de-duplication by using FOR JSON AUTO in SQL Server or jsonb_agg in PostgreSQL. Not sure how much overhead that incurs though.
One could envision a "Cartesian product" operation in the wire protocol, but I'm not convinced that's a good approach.
> You'd still return a multiplicative amount of rows, even if those rows contained only a reference
> `array_agg` in postgres avoids this
Some kind of "product" operator on the other hand reduces the cost to additive (just like `array_agg`).
> That one is tracked here: https://github.com/npgsql/efcore.pg/issues/2633
That issue is only about supporting `array_agg` as a function on tuples, not as an implementation strategy for `Include`s of collections.
Collect the data in the stored procedure with temp/in-memory tables and return minimal, non-duplicated, related result sets.
Single round trip, still accumulates results in efficient bulk batches, and allows results to be processed by the client as they stream in.
Joking (but not really) aside, EF seems to be the easiest way to shoot yourself in the foot, and write code which you think is transactional, but is not actually transactional, and would be transactional if expressed in pure SQL.
One of the challenges for their team I'm assuming is making sure the release notes also get copied to the actual documentation. This was one case where I thought the same thing and only until reading the release notes realized it was already a feature.