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, 84 insertions, 12 deletions

diff --git a/Emby.Server.Implementations/Dto/DtoService.cs b/Emby.Server.Implementations/Dto/DtoService.cs
index c5dc3b054c..236b3fabe4 100644
--- a/Emby.Server.Implementations/Dto/DtoService.cs
+++ b/Emby.Server.Implementations/Dto/DtoService.cs
@@ -153,17 +153,42 @@ namespace Emby.Server.Implementations.Dto
         private ILiveTvManager LivetvManager => _livetvManagerFactory.Value;
 
         /// <inheritdoc />
-        public IReadOnlyList<BaseItemDto> GetBaseItemDtos(IReadOnlyList<BaseItem> items, DtoOptions options, User? user = null, BaseItem? owner = null)
+        public IReadOnlyList<BaseItemDto> GetBaseItemDtos(IReadOnlyList<BaseItem> items, DtoOptions options, User? user = null, BaseItem? owner = null, bool skipVisibilityCheck = false)
         {
-            var accessibleItems = user is null ? items : items.Where(x => x.IsVisible(user)).ToList();
+            var accessibleItems = skipVisibilityCheck || user is null ? items : items.Where(x => x.IsVisible(user)).ToList();
             var returnItems = new BaseItemDto[accessibleItems.Count];
             List<(BaseItem, BaseItemDto)>? programTuples = null;
             List<(BaseItemDto, LiveTvChannel)>? channelTuples = null;
 
+            // Batch-fetch user data for all items
+            Dictionary<Guid, UserItemData>? userDataBatch = null;
+            if (user is not null && options.EnableUserData)
+            {
+                userDataBatch = _userDataRepository.GetUserDataBatch(accessibleItems, user);
+            }
+
+            // Pre-compute collection folders once to avoid N+1 queries in CanDelete
+            List<Folder>? allCollectionFolders = null;
+            if (user is not null && options.ContainsField(ItemFields.CanDelete))
+            {
+                allCollectionFolders = _libraryManager.GetUserRootFolder().Children.OfType<Folder>().ToList();
+            }
+
+            // Batch-fetch child counts for all folders to avoid N+1 queries
+            Dictionary<Guid, int>? childCountBatch = null;
+            if (options.ContainsField(ItemFields.ChildCount))
+            {
+                var folderIds = accessibleItems.OfType<Folder>().Select(f => f.Id).ToList();
+                if (folderIds.Count > 0)
+                {
+                    childCountBatch = _libraryManager.GetChildCountBatch(folderIds, user?.Id);
+                }
+            }
+
             for (int index = 0; index < accessibleItems.Count; index++)
             {
                 var item = accessibleItems[index];
-                var dto = GetBaseItemDtoInternal(item, options, user, owner);
+                var dto = GetBaseItemDtoInternal(item, options, user, owner, userDataBatch?.GetValueOrDefault(item.Id), allCollectionFolders, childCountBatch);
 
                 if (item is LiveTvChannel tvChannel)
                 {
@@ -197,7 +222,7 @@ namespace Emby.Server.Implementations.Dto
 
         public BaseItemDto GetBaseItemDto(BaseItem item, DtoOptions options, User? user = null, BaseItem? owner = null)
         {
-            var dto = GetBaseItemDtoInternal(item, options, user, owner);
+            var dto = GetBaseItemDtoInternal(item, options, user, owner, null);
             if (item is LiveTvChannel tvChannel)
             {
                 LivetvManager.AddChannelInfo(new[] { (dto, tvChannel) }, options, user);
@@ -215,7 +240,7 @@ namespace Emby.Server.Implementations.Dto
             return dto;
         }
 
-        private BaseItemDto GetBaseItemDtoInternal(BaseItem item, DtoOptions options, User? user = null, BaseItem? owner = null)
+        private BaseItemDto GetBaseItemDtoInternal(BaseItem item, DtoOptions options, User? user = null, BaseItem? owner = null, UserItemData? userData = null, List<Folder>? allCollectionFolders = null, Dictionary<Guid, int>? childCountBatch = null)
         {
             var dto = new BaseItemDto
             {
@@ -252,7 +277,7 @@ namespace Emby.Server.Implementations.Dto
 
             if (user is not null)
             {
-                AttachUserSpecificInfo(dto, item, user, options);
+                AttachUserSpecificInfo(dto, item, user, options, userData, childCountBatch);
             }
 
             if (item is IHasMediaSources
@@ -274,7 +299,9 @@ namespace Emby.Server.Implementations.Dto
             {
                 dto.CanDelete = user is null
                     ? item.CanDelete()
-                    : item.CanDelete(user);
+                    : allCollectionFolders is not null
+                        ? item.CanDelete(user, allCollectionFolders)
+                        : item.CanDelete(user);
             }
 
             if (options.ContainsField(ItemFields.CanDownload))
@@ -458,7 +485,7 @@ namespace Emby.Server.Implementations.Dto
         /// <summary>
         /// Attaches the user specific info.
         /// </summary>
-        private void AttachUserSpecificInfo(BaseItemDto dto, BaseItem item, User user, DtoOptions options)
+        private void AttachUserSpecificInfo(BaseItemDto dto, BaseItem item, User user, DtoOptions options, UserItemData? userData = null, Dictionary<Guid, int>? childCountBatch = null)
         {
             if (item.IsFolder)
             {
@@ -466,7 +493,17 @@ namespace Emby.Server.Implementations.Dto
 
                 if (options.EnableUserData)
                 {
-                    dto.UserData = _userDataRepository.GetUserDataDto(item, dto, user, options);
+                    if (userData is not null)
+                    {
+                        // Use pre-fetched user data
+                        dto.UserData = GetUserItemDataDto(userData, item.Id);
+                        item.FillUserDataDtoValues(dto.UserData, userData, dto, user, options);
+                    }
+                    else
+                    {
+                        // Fall back to individual fetch
+                        dto.UserData = _userDataRepository.GetUserDataDto(item, dto, user, options);
+                    }
                 }
 
                 if (!dto.ChildCount.HasValue && item.SourceType == SourceType.Library)
@@ -485,7 +522,7 @@ namespace Emby.Server.Implementations.Dto
 
                     if (options.ContainsField(ItemFields.ChildCount))
                     {
-                        dto.ChildCount ??= GetChildCount(folder, user);
+                        dto.ChildCount ??= GetChildCount(folder, user, childCountBatch);
                     }
                 }
 
@@ -503,7 +540,17 @@ namespace Emby.Server.Implementations.Dto
             {
                 if (options.EnableUserData)
                 {
-                    dto.UserData = _userDataRepository.GetUserDataDto(item, user);
+                    if (userData is not null)
+                    {
+                        // Use pre-fetched user data
+                        dto.UserData = GetUserItemDataDto(userData, item.Id);
+                        item.FillUserDataDtoValues(dto.UserData, userData, dto, user, options);
+                    }
+                    else
+                    {
+                        // Fall back to individual fetch
+                        dto.UserData = _userDataRepository.GetUserDataDto(item, user);
+                    }
                 }
             }
 
@@ -513,7 +560,25 @@ namespace Emby.Server.Implementations.Dto
             }
         }
 
-        private static int GetChildCount(Folder folder, User user)
+        private static UserItemDataDto GetUserItemDataDto(UserItemData data, Guid itemId)
+        {
+            ArgumentNullException.ThrowIfNull(data);
+
+            return new UserItemDataDto
+            {
+                IsFavorite = data.IsFavorite,
+                Likes = data.Likes,
+                PlaybackPositionTicks = data.PlaybackPositionTicks,
+                PlayCount = data.PlayCount,
+                Rating = data.Rating,
+                Played = data.Played,
+                LastPlayedDate = data.LastPlayedDate,
+                ItemId = itemId,
+                Key = data.Key
+            };
+        }
+
+        private static int GetChildCount(Folder folder, User user, Dictionary<Guid, int>? childCountBatch)
         {
             // Right now this is too slow to calculate for top level folders on a per-user basis
             // Just return something so that apps that are expecting a value won't think the folders are empty
@@ -522,6 +587,13 @@ namespace Emby.Server.Implementations.Dto
                 return Random.Shared.Next(1, 10);
             }
 
+            // Use pre-fetched batch data if available
+            if (childCountBatch is not null && childCountBatch.TryGetValue(folder.Id, out var count))
+            {
+                return count;
+            }
+
+            // Fall back to individual query for special cases (Series, Season, etc.)
             return folder.GetChildCount(user);
         }