Complete LinkedChildren integration and batch DTO optimizations

This commit integrates remaining performance changes:

- Add batch user data fetching in DtoService to reduce N+1 queries
- Add GetNextUpEpisodesBatch in TVSeriesManager for efficient batch retrieval
- Update Video/Movie/BoxSet to use LibraryManager for alternate versions
- Transition LinkedChild to use ItemId instead of Path (obsolete Path/LibraryItemId)
- Update providers and controllers for LinkedChildren-based references
- Add NextUpEpisodeBatchResult for batched episode queries
- Integrate IDescendantQueryProvider in SqliteDatabaseProvider

1 files changed, 61 insertions, 140 deletions

diff --git a/MediaBrowser.Controller/Entities/Folder.cs b/MediaBrowser.Controller/Entities/Folder.cs
index d2a3290c47..6338e54292 100644
--- a/MediaBrowser.Controller/Entities/Folder.cs
+++ b/MediaBrowser.Controller/Entities/Folder.cs
@@ -59,6 +59,10 @@ namespace MediaBrowser.Controller.Entities
         /// <value><c>true</c> if this instance is root; otherwise, <c>false</c>.</value>
         public bool IsRoot { get; set; }
 
+        /// <summary>
+        /// Gets or sets the linked children.
+        /// </summary>
+        [JsonIgnore]
         public LinkedChild[] LinkedChildren { get; set; }
 
         [JsonIgnore]
@@ -455,6 +459,14 @@ namespace MediaBrowser.Controller.Entities
                 // If it's an AggregateFolder, don't remove
                 if (shouldRemove && itemsRemoved.Count > 0)
                 {
+                    // Build a set of paths that are alternate versions of valid children
+                    // These items should not be deleted - they're managed by their primary video
+                    var alternateVersionPaths = validChildren
+                        .OfType<Video>()
+                        .SelectMany(v => v.LocalAlternateVersions ?? [])
+                        .Where(p => !string.IsNullOrEmpty(p))
+                        .ToHashSet(StringComparer.OrdinalIgnoreCase);
+
                     foreach (var item in itemsRemoved)
                     {
                         if (!item.CanDelete())
@@ -463,6 +475,24 @@ namespace MediaBrowser.Controller.Entities
                             continue;
                         }
 
+                        // Skip items that are alternate versions of another video
+                        if (item is Video video)
+                        {
+                            // Check via PrimaryVersionId
+                            if (!string.IsNullOrEmpty(video.PrimaryVersionId))
+                            {
+                                Logger.LogDebug("Item is an alternate version (via PrimaryVersionId), skipping deletion: {Path}", item.Path ?? item.Name);
+                                continue;
+                            }
+
+                            // Check if path is in LocalAlternateVersions of any valid child
+                            if (!string.IsNullOrEmpty(item.Path) && alternateVersionPaths.Contains(item.Path))
+                            {
+                                Logger.LogDebug("Item path matches an alternate version, skipping deletion: {Path}", item.Path);
+                                continue;
+                            }
+                        }
+
                         if (item.IsFileProtocol)
                         {
                             Logger.LogDebug("Removed item: {Path}", item.Path);
@@ -806,104 +836,12 @@ namespace MediaBrowser.Controller.Entities
 
         private bool RequiresPostFiltering(InternalItemsQuery query)
         {
-            if (LinkedChildren.Length > 0)
-            {
-                if (this is not ICollectionFolder)
-                {
-                    Logger.LogDebug("{Type}: Query requires post-filtering due to LinkedChildren.", GetType().Name);
-                    return true;
-                }
-            }
-
-            // Filter by Video3DFormat
-            if (query.Is3D.HasValue)
-            {
-                Logger.LogDebug("Query requires post-filtering due to Is3D");
-                return true;
-            }
-
-            if (query.HasOfficialRating.HasValue)
-            {
-                Logger.LogDebug("Query requires post-filtering due to HasOfficialRating");
-                return true;
-            }
-
-            if (query.IsPlaceHolder.HasValue)
-            {
-                Logger.LogDebug("Query requires post-filtering due to IsPlaceHolder");
-                return true;
-            }
-
-            if (query.HasSpecialFeature.HasValue)
-            {
-                Logger.LogDebug("Query requires post-filtering due to HasSpecialFeature");
-                return true;
-            }
-
-            if (query.HasSubtitles.HasValue)
-            {
-                Logger.LogDebug("Query requires post-filtering due to HasSubtitles");
-                return true;
-            }
-
-            if (query.HasTrailer.HasValue)
-            {
-                Logger.LogDebug("Query requires post-filtering due to HasTrailer");
-                return true;
-            }
-
-            if (query.HasThemeSong.HasValue)
-            {
-                Logger.LogDebug("Query requires post-filtering due to HasThemeSong");
-                return true;
-            }
-
-            if (query.HasThemeVideo.HasValue)
-            {
-                Logger.LogDebug("Query requires post-filtering due to HasThemeVideo");
-                return true;
-            }
-
-            // Filter by VideoType
-            if (query.VideoTypes.Length > 0)
-            {
-                Logger.LogDebug("Query requires post-filtering due to VideoTypes");
-                return true;
-            }
-
             if (CollapseBoxSetItems(query, this, query.User, ConfigurationManager))
             {
                 Logger.LogDebug("Query requires post-filtering due to CollapseBoxSetItems");
                 return true;
             }
 
-            if (!query.AdjacentTo.IsNullOrEmpty())
-            {
-                Logger.LogDebug("Query requires post-filtering due to AdjacentTo");
-                return true;
-            }
-
-            if (query.SeriesStatuses.Length > 0)
-            {
-                Logger.LogDebug("Query requires post-filtering due to SeriesStatuses");
-                return true;
-            }
-
-            if (query.AiredDuringSeason.HasValue)
-            {
-                Logger.LogDebug("Query requires post-filtering due to AiredDuringSeason");
-                return true;
-            }
-
-            if (query.IsPlayed.HasValue)
-            {
-                if (query.IncludeItemTypes.Length == 1 && query.IncludeItemTypes.Contains(BaseItemKind.Series))
-                {
-                    Logger.LogDebug("Query requires post-filtering due to IsPlayed");
-                    return true;
-                }
-            }
-
             return false;
         }
 
@@ -1012,29 +950,6 @@ namespace MediaBrowser.Controller.Entities
                 items = CollapseBoxSetItemsIfNeeded(items, query, this, user, ConfigurationManager, CollectionManager);
             }
 
-#pragma warning disable CA1309
-            if (!string.IsNullOrEmpty(query.NameStartsWithOrGreater))
-            {
-                items = items.Where(i => string.Compare(query.NameStartsWithOrGreater, i.SortName, StringComparison.InvariantCultureIgnoreCase) < 1);
-            }
-
-            if (!string.IsNullOrEmpty(query.NameStartsWith))
-            {
-                items = items.Where(i => i.SortName.StartsWith(query.NameStartsWith, StringComparison.InvariantCultureIgnoreCase));
-            }
-
-            if (!string.IsNullOrEmpty(query.NameLessThan))
-            {
-                items = items.Where(i => string.Compare(query.NameLessThan, i.SortName, StringComparison.InvariantCultureIgnoreCase) == 1);
-            }
-#pragma warning restore CA1309
-
-            // This must be the last filter
-            if (!query.AdjacentTo.IsNullOrEmpty())
-            {
-                items = UserViewBuilder.FilterForAdjacency(items.ToList(), query.AdjacentTo.Value);
-            }
-
             var filteredItems = items as IReadOnlyList<BaseItem> ?? items.ToList();
             var result = UserViewBuilder.SortAndPage(filteredItems, null, query, LibraryManager);
 
@@ -1664,11 +1579,13 @@ namespace MediaBrowser.Controller.Entities
 
                             if (!string.IsNullOrEmpty(resolvedPath))
                             {
+#pragma warning disable CS0618 // Type or member is obsolete - shortcuts require Path for lazy ItemId resolution
                                 return new LinkedChild
                                 {
                                     Path = resolvedPath,
                                     Type = LinkedChildType.Shortcut
                                 };
+#pragma warning restore CS0618
                             }
 
                             Logger.LogError("Error resolving shortcut {0}", i.FullName);
@@ -1786,38 +1703,42 @@ namespace MediaBrowser.Controller.Entities
                 return;
             }
 
-            if (itemDto is not null && fields.ContainsField(ItemFields.RecursiveItemCount))
-            {
-                itemDto.RecursiveItemCount = GetRecursiveChildCount(user);
-            }
-
-            if (SupportsPlayedStatus)
+            if (SupportsPlayedStatus || (itemDto is not null && fields.ContainsField(ItemFields.RecursiveItemCount)))
             {
-                var unplayedQueryResult = GetItems(new InternalItemsQuery(user)
-                {
-                    Recursive = true,
-                    IsFolder = false,
-                    IsVirtualItem = false,
-                    EnableTotalRecordCount = true,
-                    Limit = 0,
-                    IsPlayed = false,
-                    DtoOptions = new DtoOptions(false)
-                    {
-                        EnableImages = false
-                    }
-                }).TotalRecordCount;
+                var query = new InternalItemsQuery(user);
+                LibraryManager.ConfigureUserAccess(query, user);
 
-                dto.UnplayedItemCount = unplayedQueryResult;
+                int playedCount;
+                int totalCount;
 
-                if (itemDto?.RecursiveItemCount > 0)
+                if (LinkedChildren.Length > 0)
                 {
-                    var unplayedPercentage = ((double)unplayedQueryResult / itemDto.RecursiveItemCount.Value) * 100;
-                    dto.PlayedPercentage = 100 - unplayedPercentage;
-                    dto.Played = dto.PlayedPercentage.Value >= 100;
+                    (playedCount, totalCount) = ItemRepository.GetPlayedAndTotalCountFromLinkedChildren(query, Id);
                 }
                 else
                 {
-                    dto.Played = (dto.UnplayedItemCount ?? 0) == 0;
+                    (playedCount, totalCount) = ItemRepository.GetPlayedAndTotalCount(query, Id);
+                }
+
+                if (itemDto is not null && fields.ContainsField(ItemFields.RecursiveItemCount))
+                {
+                    itemDto.RecursiveItemCount = totalCount;
+                }
+
+                if (SupportsPlayedStatus)
+                {
+                    var unplayedCount = totalCount - playedCount;
+                    dto.UnplayedItemCount = unplayedCount;
+
+                    if (totalCount > 0)
+                    {
+                        dto.PlayedPercentage = playedCount / (double)totalCount * 100;
+                        dto.Played = playedCount >= totalCount;
+                    }
+                    else
+                    {
+                        dto.Played = true;
+                    }
                 }
             }
         }