Consider a related entity loading strategy that aggregates dependent collections to a JSON array

[roji]

In EF Core 7.0, we're adding type mapping APIs to precisely control how values are serialized/deserialized from JSON; this should allow us to represent any (primitive) value in JSON and reliably serialize/deserialize it to stored documents in the database.

Databases also allow aggregating multiple rows into a single JSON document. On most databases, this is done using an aggregate method (jsonb_agg on PostgreSQL, json_group_array and json_object on SQLite, JSON_ARRAYAGG on MySQL); we added support for custom provider aggregate methods in EF 7.0 as well (#22957). On SQL Server this is done via the special FOR JSON construct.

Combining these features, we could load a related collection navigation by aggregating it to a single JSON text, and deserialize it back on the client. This works around the cartesian explosion and data duplication problems inherent in single queries with JOINs, but also doesn't suffer from the problems of split queries (increased memory needs due to buffering of the principal results, loss of transactional consistency).

On PostgreSQL we'd aggregate to an array instead (no JSON needed; tracked by npgsql/efcore.pg#1691).

[roji]

Note: the same concept can be used for translating final GroupBy without Select: the groups can be aggregated to JSON arrays, transferred to the client and the JSON parsed by EF.