21–30 of 40 posts
I think it's crazy that standard SQL has no clean way of handling nested data. It's might not fit elegantly into the relational model, but it's still a common business problem that should be addressed. At the bare minimum something like `array_agg` should be standardized. Alternative query languages like EdgeQL show what first class support for nested data (and navigations) could look like, while the data model is st…
Earlier quoted context omitted.
Assume you fetch a single customer entity with their 100 order entities as includes. With single query this will join both tables and produce 100 rows that contain the order data but also each one contains the customer data redundantly. Now Imagine you had two includes there, that will multiply the number of rows again. AsSingleQuery is as dangerous as this makes it sound. This works surprisingly well if you know tha…
Databases are incredibly smart when it comes to fetching related data, a single select is indeed better than splitting queries and doing multiple roundtrips. The problem however is in how results are returned over the wire . Duplicating rows is needless, but seems to be still the standard.
I wish EF Core had first-class support for raw SQL, like Dapper.
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.
Earlier quoted context omitted.
If you join multiple/many tables, you could end up with a large volume of data. And yes, this is bit-for-bit duplication—on the network. The query result is (typically) a single table. This table will get serialized as-is, with all duplicate data.
Couldn't we make references as `byte offsets in the result set` work to handle duplication? Real memory pointers wouldn't work over the network of course, but if the database driver would return results like this, the client could easily stitch these together. My hunch is that even if we implement references on a higher level than raw byte offsets it would still be more performant than just returning R1*R2 bytes for…
I think it's crazy that standard SQL has no clean way of handling nested data. It's might not fit elegantly into the relational model, but it's still a common business problem that should be addressed. At the bare minimum something like `array_agg` should be standardized. Alternative query languages like EdgeQL show what first class support for nested data (and navigations) could look like, while the data model is st…
Is this not what you want? Seems like it's part of the SQL standard. https://www.postgresql.org/docs/19/ddl-property-graphs.html
For example how would you use it to return a list of events and, for each event, the list of attendees in that event?
Earlier quoted context omitted.
Couldn't we make references as `byte offsets in the result set` work to handle duplication? Real memory pointers wouldn't work over the network of course, but if the database driver would return results like this, the client could easily stitch these together. My hunch is that even if we implement references on a higher level than raw byte offsets it would still be more performant than just returning R1*R2 bytes for…
You'd still return a multiplicative amount of rows, even if those rows contained only a reference. `array_agg` in postgres avoids this, but EF does not support using it for collection navigations. 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
Earlier quoted context omitted.
You'd still return a multiplicative amount of rows, even if those rows contained only a reference. `array_agg` in postgres avoids this, but EF does not support using it for collection navigations. 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 Sure, but a pointer is still a massive win over records, and I think a further cartesian product wire protocol extension would not be worth the hassle. > `array_agg` in postgres avoids this That one is tracked here: https://github.com/npgsql/efcore.pg/issues/2633
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.
I think it's crazy that standard SQL has no clean way of handling nested data. It's might not fit elegantly into the relational model, but it's still a common business problem that should be addressed. At the bare minimum something like `array_agg` should be standardized. Alternative query languages like EdgeQL show what first class support for nested data (and navigations) could look like, while the data model is st…
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...
I don't understand the argument why `AsSplitQuery` could be more performant than a single round trip involving a multi join query. People mention data duplication and increased memory usage, but I would assume that `duplication` is just a matter of an extra pointer, not a bit-for-bit duplication of every reference to a single row. Please enlighten me.
Assume you fetch a single customer entity with their 100 order entities as includes. With single query this will join both tables and produce 100 rows that contain the order data but also each one contains the customer data redundantly. Now Imagine you had two includes there, that will multiply the number of rows again. AsSingleQuery is as dangerous as this makes it sound. This works surprisingly well if you know tha…
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.
Earlier quoted context omitted.
Assume you fetch a single customer entity with their 100 order entities as includes. With single query this will join both tables and produce 100 rows that contain the order data but also each one contains the customer data redundantly. Now Imagine you had two includes there, that will multiply the number of rows again. AsSingleQuery is as dangerous as this makes it sound. This works surprisingly well if you know tha…
Databases are incredibly smart when it comes to fetching related data, a single select is indeed better than splitting queries and doing multiple roundtrips. The problem however is in how results are returned over the wire . Duplicating rows is needless, but seems to be still the standard.