[fabian2k]

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 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.

[exceptione]

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.

[moomin]

My experience suggests that they _can_ be good, but this particular pattern they can be remarkably bad at. Source: I keep having to optimise this pattern.

[skeeter2020]

Relational DBs have been around a very long time, and I suspect the processing/transfer time was not the bottleneck, while data access was, so redundant data with the standard relational model wasn't the primary concern.

[kenniskrag]

I think compression would reduce the problem not? I think if you swap the wire format to something like jsonb you would need to parse it again anyway and pay the cpu time.

[magicalhippo]

Out main screen has at head level over 200 fields, most of them filled in for typical orders. The main table has several child tables, some which have several child tables again. The order line table has several, and especially one of them can typically have 5-7 rows per order line.

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.