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, 96 insertions, 109 deletions

diff --git a/Emby.Server.Implementations/TV/TVSeriesManager.cs b/Emby.Server.Implementations/TV/TVSeriesManager.cs
index cd98dbe86e..ebabb4ca2f 100644
--- a/Emby.Server.Implementations/TV/TVSeriesManager.cs
+++ b/Emby.Server.Implementations/TV/TVSeriesManager.cs
@@ -47,7 +47,7 @@ namespace Emby.Server.Implementations.TV
 
             if (!string.IsNullOrEmpty(presentationUniqueKey))
             {
-                return GetResult(GetNextUpEpisodes(query, user, new[] { presentationUniqueKey }, options), query);
+                return GetNextUpBatched(query, user, [presentationUniqueKey], options);
             }
 
             BaseItem[] parents;
@@ -58,11 +58,11 @@ namespace Emby.Server.Implementations.TV
 
                 if (parent is not null)
                 {
-                    parents = new[] { parent };
+                    parents = [parent];
                 }
                 else
                 {
-                    parents = Array.Empty<BaseItem>();
+                    parents = [];
                 }
             }
             else
@@ -93,7 +93,7 @@ namespace Emby.Server.Implementations.TV
 
             if (!string.IsNullOrEmpty(presentationUniqueKey))
             {
-                return GetResult(GetNextUpEpisodes(request, user, [presentationUniqueKey], options), request);
+                return GetNextUpBatched(request, user, [presentationUniqueKey], options);
             }
 
             if (limit.HasValue)
@@ -103,151 +103,138 @@ namespace Emby.Server.Implementations.TV
 
             var nextUpSeriesKeys = _libraryManager.GetNextUpSeriesKeys(new InternalItemsQuery(user) { Limit = limit }, parentsFolders, request.NextUpDateCutoff);
 
-            var episodes = GetNextUpEpisodes(request, user, nextUpSeriesKeys, options);
-
-            return GetResult(episodes, request);
+            return GetNextUpBatched(request, user, nextUpSeriesKeys, options);
         }
 
-        private IEnumerable<Episode> GetNextUpEpisodes(NextUpQuery request, User user, IReadOnlyList<string> seriesKeys, DtoOptions dtoOptions)
+        private QueryResult<BaseItem> GetNextUpBatched(NextUpQuery request, User user, IReadOnlyList<string> seriesKeys, DtoOptions dtoOptions)
         {
-            var allNextUp = seriesKeys.Select(i => GetNextUp(i, user, dtoOptions, request.EnableResumable, false));
-
-            if (request.EnableRewatching)
+            if (seriesKeys.Count == 0)
             {
-                allNextUp = allNextUp
-                    .Concat(seriesKeys.Select(i => GetNextUp(i, user, dtoOptions, false, true)))
-                    .OrderByDescending(i => i.LastWatchedDate);
+                return new QueryResult<BaseItem>();
             }
 
-            return allNextUp
-                .Select(i => i.GetEpisodeFunction())
-                .Where(i => i is not null)!;
-        }
-
-        private static string GetUniqueSeriesKey(Series series)
-        {
-            return series.GetPresentationUniqueKey();
-        }
+            var includeSpecials = _configurationManager.Configuration.DisplaySpecialsWithinSeasons;
+            var includeRewatching = request.EnableRewatching;
 
-        /// <summary>
-        /// Gets the next up.
-        /// </summary>
-        /// <returns>Task{Episode}.</returns>
-        private (DateTime LastWatchedDate, Func<Episode?> GetEpisodeFunction) GetNextUp(string seriesKey, User user, DtoOptions dtoOptions, bool includeResumable, bool includePlayed)
-        {
-            var lastQuery = new InternalItemsQuery(user)
+            var query = new InternalItemsQuery(user)
             {
-                AncestorWithPresentationUniqueKey = null,
-                SeriesPresentationUniqueKey = seriesKey,
-                IncludeItemTypes = [BaseItemKind.Episode],
-                IsPlayed = true,
-                Limit = 1,
-                ParentIndexNumberNotEquals = 0,
-                DtoOptions = new DtoOptions
-                {
-                    Fields = [ItemFields.SortName],
-                    EnableImages = false
-                }
+                DtoOptions = dtoOptions
             };
 
-            // If including played results, sort first by date played and then by season and episode numbers
-            lastQuery.OrderBy = includePlayed
-                ? new[] { (ItemSortBy.DatePlayed, SortOrder.Descending), (ItemSortBy.ParentIndexNumber, SortOrder.Descending), (ItemSortBy.IndexNumber, SortOrder.Descending) }
-                : new[] { (ItemSortBy.ParentIndexNumber, SortOrder.Descending), (ItemSortBy.IndexNumber, SortOrder.Descending) };
+            var batchResult = _libraryManager.GetNextUpEpisodesBatch(query, seriesKeys, includeSpecials, includeRewatching);
 
-            var lastWatchedEpisode = _libraryManager.GetItemList(lastQuery).Cast<Episode>().FirstOrDefault();
+            var nextUpList = new List<(DateTime LastWatchedDate, Episode Episode)>();
 
-            Episode? GetEpisode()
+            foreach (var seriesKey in seriesKeys)
             {
-                var nextQuery = new InternalItemsQuery(user)
+                if (!batchResult.TryGetValue(seriesKey, out var result))
                 {
-                    AncestorWithPresentationUniqueKey = null,
-                    SeriesPresentationUniqueKey = seriesKey,
-                    IncludeItemTypes = [BaseItemKind.Episode],
-                    OrderBy = [(ItemSortBy.ParentIndexNumber, SortOrder.Ascending), (ItemSortBy.IndexNumber, SortOrder.Ascending)],
-                    Limit = 1,
-                    IsPlayed = includePlayed,
-                    IsVirtualItem = false,
-                    ParentIndexNumberNotEquals = 0,
-                    DtoOptions = dtoOptions
-                };
-
-                // Locate the next up episode based on the last watched episode's season and episode number
-                var lastWatchedParentIndexNumber = lastWatchedEpisode?.ParentIndexNumber;
-                var lastWatchedIndexNumber = lastWatchedEpisode?.IndexNumberEnd ?? lastWatchedEpisode?.IndexNumber;
-                if (lastWatchedParentIndexNumber.HasValue && lastWatchedIndexNumber.HasValue)
-                {
-                    nextQuery.MinParentAndIndexNumber = (lastWatchedParentIndexNumber.Value, lastWatchedIndexNumber.Value + 1);
+                    continue;
                 }
 
-                var nextEpisode = _libraryManager.GetItemList(nextQuery).Cast<Episode>().FirstOrDefault();
+                var nextEpisode = DetermineNextEpisode(result, user, includeSpecials, request.EnableResumable, false);
 
-                if (_configurationManager.Configuration.DisplaySpecialsWithinSeasons)
+                if (nextEpisode is not null)
                 {
-                    var consideredEpisodes = _libraryManager.GetItemList(new InternalItemsQuery(user)
+                    DateTime lastWatchedDate = DateTime.MinValue;
+                    if (result.LastWatched is not null)
                     {
-                        AncestorWithPresentationUniqueKey = null,
-                        SeriesPresentationUniqueKey = seriesKey,
-                        ParentIndexNumber = 0,
-                        IncludeItemTypes = [BaseItemKind.Episode],
-                        IsPlayed = includePlayed,
-                        IsVirtualItem = false,
-                        DtoOptions = dtoOptions
-                    })
-                    .Cast<Episode>()
-                    .Where(episode => episode.AirsBeforeSeasonNumber is not null || episode.AirsAfterSeasonNumber is not null)
-                    .ToList();
-
-                    if (lastWatchedEpisode is not null)
-                    {
-                        // Last watched episode is added, because there could be specials that aired before the last watched episode
-                        consideredEpisodes.Add(lastWatchedEpisode);
+                        var userData = _userDataManager.GetUserData(user, result.LastWatched);
+                        lastWatchedDate = userData?.LastPlayedDate ?? DateTime.MinValue.AddDays(1);
                     }
 
-                    if (nextEpisode is not null)
-                    {
-                        consideredEpisodes.Add(nextEpisode);
-                    }
+                    nextUpList.Add((lastWatchedDate, nextEpisode));
+                }
 
-                    var sortedConsideredEpisodes = _libraryManager.Sort(consideredEpisodes, user, [(ItemSortBy.AiredEpisodeOrder, SortOrder.Ascending)])
-                        .Cast<Episode>();
-                    if (lastWatchedEpisode is not null)
+                if (includeRewatching)
+                {
+                    var nextPlayedEpisode = DetermineNextEpisodeForRewatching(result, user, includeSpecials);
+
+                    if (nextPlayedEpisode is not null)
                     {
-                        sortedConsideredEpisodes = sortedConsideredEpisodes.SkipWhile(episode => !episode.Id.Equals(lastWatchedEpisode.Id)).Skip(1);
+                        DateTime rewatchLastWatchedDate = DateTime.MinValue;
+                        if (result.LastWatchedForRewatching is not null)
+                        {
+                            var userData = _userDataManager.GetUserData(user, result.LastWatchedForRewatching);
+                            rewatchLastWatchedDate = userData?.LastPlayedDate ?? DateTime.MinValue.AddDays(1);
+                        }
+
+                        nextUpList.Add((rewatchLastWatchedDate, nextPlayedEpisode));
                     }
+                }
+            }
 
-                    nextEpisode = sortedConsideredEpisodes.FirstOrDefault();
+            var sortedEpisodes = nextUpList
+                .OrderByDescending(x => x.LastWatchedDate)
+                .Select(x => (BaseItem)x.Episode);
+
+            return GetResult(sortedEpisodes, request);
+        }
+
+        private Episode? DetermineNextEpisode(
+            MediaBrowser.Controller.Persistence.NextUpEpisodeBatchResult result,
+            User user,
+            bool includeSpecials,
+            bool includeResumable,
+            bool includePlayed)
+        {
+            var nextEpisode = (includePlayed ? result.NextPlayedForRewatching : result.NextUp) as Episode;
+            var lastWatchedEpisode = (includePlayed ? result.LastWatchedForRewatching : result.LastWatched) as Episode;
+
+            if (includeSpecials && result.Specials?.Count > 0)
+            {
+                var consideredEpisodes = result.Specials
+                    .Cast<Episode>()
+                    .Where(episode => episode.AirsBeforeSeasonNumber is not null || episode.AirsAfterSeasonNumber is not null)
+                    .ToList();
+
+                if (lastWatchedEpisode is not null)
+                {
+                    consideredEpisodes.Add(lastWatchedEpisode);
                 }
 
-                if (nextEpisode is not null && !includeResumable)
+                if (nextEpisode is not null)
                 {
-                    var userData = _userDataManager.GetUserData(user, nextEpisode);
+                    consideredEpisodes.Add(nextEpisode);
+                }
 
-                    if (userData?.PlaybackPositionTicks > 0)
+                if (consideredEpisodes.Count > 0)
+                {
+                    var sortedEpisodes = _libraryManager.Sort(consideredEpisodes, user, [(ItemSortBy.AiredEpisodeOrder, SortOrder.Ascending)])
+                        .Cast<Episode>();
+
+                    if (lastWatchedEpisode is not null)
                     {
-                        return null;
+                        sortedEpisodes = sortedEpisodes.SkipWhile(episode => !episode.Id.Equals(lastWatchedEpisode.Id)).Skip(1);
                     }
-                }
 
-                return nextEpisode;
+                    nextEpisode = sortedEpisodes.FirstOrDefault();
+                }
             }
 
-            if (lastWatchedEpisode is not null)
+            if (nextEpisode is not null && !includeResumable)
             {
-                var userData = _userDataManager.GetUserData(user, lastWatchedEpisode);
-
-                if (userData is null)
+                var userData = _userDataManager.GetUserData(user, nextEpisode);
+                if (userData?.PlaybackPositionTicks > 0)
                 {
-                    return (DateTime.MinValue, GetEpisode);
+                    return null;
                 }
+            }
 
-                var lastWatchedDate = userData.LastPlayedDate ?? DateTime.MinValue.AddDays(1);
+            return nextEpisode;
+        }
 
-                return (lastWatchedDate, GetEpisode);
-            }
+        private Episode? DetermineNextEpisodeForRewatching(
+            MediaBrowser.Controller.Persistence.NextUpEpisodeBatchResult result,
+            User user,
+            bool includeSpecials)
+        {
+            return DetermineNextEpisode(result, user, includeSpecials, includeResumable: false, includePlayed: true);
+        }
 
-            // Return the first episode
-            return (DateTime.MinValue, GetEpisode);
+        private static string GetUniqueSeriesKey(Series series)
+        {
+            return series.GetPresentationUniqueKey();
         }
 
         private static QueryResult<BaseItem> GetResult(IEnumerable<BaseItem> items, NextUpQuery query)