[12.1] BoxSet grid, opening a collection, and BoxSet images still OOM on a large collections library (LinkedChildren cartesian; #18039 fixed only /UserViews)

First off, thanks @Shadowghost for the /UserViews fix in #18039. I build-tested it before it merged and re-tested the 12.1 release image against a snapshot of my library, and /UserViews is solid now (HTTP 200, flat memory, survives restart). This is a follow-up for three other BoxSet paths that go through the same LinkedChildren cartesian and still OOM the server on 12.1, which #18039 did not touch.

What still OOMs on 12.1

On my library, opening the Collections view or a single collection from an Android TV client crash-loops the server. The three requests that reproduce it:

1. The collections grid: GET /Items?ParentId=<collections library>&SortBy=SortName (enumerating the BoxSets)
2. Opening a collection: GET /Items?ParentId=<boxset> (the per-type row queries a client fires for a collection)
3. A BoxSet poster: GET /Items/<boxset>/Images/Primary (serving the image loads the item)

Each one allocates until the process is OOM-killed. Serving the same poster twice is fine once it is cached, so it is the first (uncached) load of each distinct BoxSet that does it.

Library shape

This is a big, collection-heavy library: about 330k BaseItems, ~15,800 movies, ~64k episodes, 6 users (4 active), and 150 collections (BoxSets), the largest with ~500 members. The migrated LinkedChildren table is ~15,100 rows. So it is scale (150 collections x up to 500 members, and the UserData leg of the join is per-user), not anything unusual about the data. The collections are created through the normal API (POST /Collections + /Collections/{id}/Items), no direct DB writes.

Root cause

#18039's HasVisibleChild change fixed the visibility probe path (it queries children with StoredColumnsOnly, which drops the extra includes). But the item enumeration path is separate: BaseItemRepository.PrepareItemQuery still calls dbQuery.AsSingleQuery(), so any BoxSet-parented query materializes the 5-way LEFT JOIN over BaseItemProviders / BaseItemMetadataFields / UserData / BaseItemImageInfos / LinkedChildren as one cartesian. For 150 collections fanned out by up to 500 linked children each, that is hundreds of millions of rows, sorted, before anything is returned.

No query parameter avoids it (I tried IncludeItemTypes=BoxSet, minimal Fields, and Limit, all still OOM), and fetching the boxsets as authentic items via Ids= is no escape either: even an 8-boxset fetch costs ~1 GiB and the memory does not return to baseline between requests, so a sequence pins the server at multi-GB and eventually OOMs.

Scope, and what I did on my side

I went digging through the source because I had to take defensive action first. Given the three paths above, I did not want an unattended scheduled scan running, so I disabled the Scan Media Library task and put a memory limit on my Jellyfin container, so a runaway query OOM-kills only the container instead of taking down the host and everything else on it. While I was in there I traced how far the same query builder reaches, and it is wider than three client endpoints:

• The internal CollectionPostScanTask runs GetItemList([BoxSet]) with full options as a post-scan task, so a library scan runs this same query server-side, with no client in the path and no client-side workaround. That is the one that pushed me to turn my scheduled scan off.
• LinkedChildren also backs Playlists, and every /Playlists/{id}/... call enumerates all playlists through the same builder. I have no playlists so I have not reproduced that leg, but from the code I would expect one large playlist to do the same for the whole server.

So the same builder feeds the client paths, the image path, and the internal scan and image-precache tasks. That is why I think this wants a fix in the query construction (scope the split query, or drop the collection includes for these paths) rather than per-endpoint.

Repro + fix, build-tested

Method: from-source build, run against an online snapshot of my library in a container capped at 12 GB (prod untouched).

• Baseline at f64cb2a0 (the merged #18039, i.e. 12.1): GET /Items?ParentId=<collections> OOM-kills the container in ~25 s.
• Patched: flip the one line in PrepareItemQuery (BaseItemRepository.QueryBuilding.cs) from AsSingleQuery() to AsSplitQuery(), rebuild, same DB:

path	baseline (12.1)	+ AsSplitQuery
/Items?ParentId=<collections> (grid)	OOM ~25 s, killed	200, 2.1 s, 504 MiB, 100 items
/Items?ParentId=<boxset> (open)	(server already dead)	200, 0.75 s
/Items/<boxset>/Images/Primary	(server already dead)	200, 0.02 s

Server stays alive at ~615 MiB flat through all three.

Flipping the global flag is obviously blunt (it splits every item query, so it adds round-trips everywhere). The shippable shape is probably to scope the split to the collection-navigation queries, the same way you scoped the probe in #18039, or to push the LinkedChildren include out of the enumeration entirely. I have the build rig set up and a reproducing snapshot, so I am happy to build-test a candidate patch or put up a PR if that is useful, whichever you prefer.