Omit BoxSet related materialization

3 files changed, 37 insertions, 38 deletions

diff --git a/Jellyfin.Server.Implementations/Item/BaseItemRepository.QueryBuilding.cs b/Jellyfin.Server.Implementations/Item/BaseItemRepository.QueryBuilding.cs
index 7570421e78..d6ddf8f5c8 100644
--- a/Jellyfin.Server.Implementations/Item/BaseItemRepository.QueryBuilding.cs
+++ b/Jellyfin.Server.Implementations/Item/BaseItemRepository.QueryBuilding.cs
@@ -495,53 +495,49 @@ public sealed partial class BaseItemRepository
                                 && (lc.Child.InheritedParentalRatingSubValue ?? 0) <= maxSubScore)))));
     }
 
-    private Dictionary<Guid, (int Played, int Total)> GetPlayedAndTotalCountBatch(IReadOnlyList<Guid> folderIds, User user)
+    /// <inheritdoc />
+    public IQueryable<Guid> GetFullyPlayedFolderIdsQuery(JellyfinDbContext context, IQueryable<Guid> folderIds, User user)
     {
+        ArgumentNullException.ThrowIfNull(context);
         ArgumentNullException.ThrowIfNull(folderIds);
         ArgumentNullException.ThrowIfNull(user);
 
-        if (folderIds.Count == 0)
-        {
-            return new Dictionary<Guid, (int Played, int Total)>();
-        }
-
-        using var dbContext = _dbProvider.CreateDbContext();
-        var folderIdsArray = folderIds.ToArray();
         var filter = new InternalItemsQuery(user);
         var userId = user.Id;
 
-        var leafItems = dbContext.BaseItems
+        var leafItems = context.BaseItems
+            .AsNoTracking()
             .Where(b => !b.IsFolder && !b.IsVirtualItem);
-        leafItems = ApplyAccessFiltering(dbContext, leafItems, filter);
+        leafItems = ApplyAccessFiltering(context, leafItems, filter);
 
         var playedLeafItems = leafItems
             .Select(b => new { b.Id, Played = b.UserData!.Any(ud => ud.UserId == userId && ud.Played) });
 
-        var ancestorLeaves = dbContext.AncestorIds
-            .WhereOneOrMany(folderIdsArray, a => a.ParentItemId)
+        var ancestorLeaves = context.AncestorIds
+            .Where(a => folderIds.Contains(a.ParentItemId))
             .Join(
                 playedLeafItems,
                 a => a.ItemId,
                 b => b.Id,
                 (a, b) => new { FolderId = a.ParentItemId, b.Id, b.Played });
 
-        var linkedLeaves = dbContext.LinkedChildren
-            .WhereOneOrMany(folderIdsArray, lc => lc.ParentId)
+        var linkedLeaves = context.LinkedChildren
+            .Where(lc => folderIds.Contains(lc.ParentId))
             .Join(
                 playedLeafItems,
                 lc => lc.ChildId,
                 b => b.Id,
                 (lc, b) => new { FolderId = lc.ParentId, b.Id, b.Played });
 
-        var linkedFolderLeaves = dbContext.LinkedChildren
-            .WhereOneOrMany(folderIdsArray, lc => lc.ParentId)
+        var linkedFolderLeaves = context.LinkedChildren
+            .Where(lc => folderIds.Contains(lc.ParentId))
             .Join(
-                dbContext.BaseItems.Where(b => b.IsFolder),
+                context.BaseItems.Where(b => b.IsFolder),
                 lc => lc.ChildId,
                 b => b.Id,
                 (lc, b) => new { lc.ParentId, FolderChildId = b.Id })
             .Join(
-                dbContext.AncestorIds,
+                context.AncestorIds,
                 x => x.FolderChildId,
                 a => a.ParentItemId,
                 (x, a) => new { x.ParentId, DescendantId = a.ItemId })
@@ -551,18 +547,11 @@ public sealed partial class BaseItemRepository
                 b => b.Id,
                 (x, b) => new { FolderId = x.ParentId, b.Id, b.Played });
 
-        var results = ancestorLeaves
+        return ancestorLeaves
             .Union(linkedLeaves)
             .Union(linkedFolderLeaves)
             .GroupBy(x => x.FolderId)
-            .Select(g => new
-            {
-                FolderId = g.Key,
-                Total = g.Select(x => x.Id).Distinct().Count(),
-                Played = g.Where(x => x.Played).Select(x => x.Id).Distinct().Count()
-            })
-            .ToDictionary(x => x.FolderId, x => (x.Played, x.Total));
-
-        return results;
+            .Where(g => g.Select(x => x.Id).Distinct().Count() == g.Where(x => x.Played).Select(x => x.Id).Distinct().Count())
+            .Select(g => g.Key);
     }
 }
diff --git a/Jellyfin.Server.Implementations/Item/BaseItemRepository.TranslateQuery.cs b/Jellyfin.Server.Implementations/Item/BaseItemRepository.TranslateQuery.cs
index 9a57691fbd..0abe981af8 100644
--- a/Jellyfin.Server.Implementations/Item/BaseItemRepository.TranslateQuery.cs
+++ b/Jellyfin.Server.Implementations/Item/BaseItemRepository.TranslateQuery.cs
@@ -471,16 +471,13 @@ public sealed partial class BaseItemRepository
                         .Select(g => g.Key)
                     : Enumerable.Empty<Guid>().AsQueryable();
 
-                // BoxSet: played = all children played
-                IEnumerable<Guid> playedBoxSetIds = [];
-                if (hasBoxSet)
-                {
-                    var boxSetIds = baseQuery.Where(e => e.Type == boxSetTypeName).Select(e => e.Id).ToList();
-                    var playedCounts = GetPlayedAndTotalCountBatch(boxSetIds, filter.User!);
-                    playedBoxSetIds = playedCounts
-                        .Where(kvp => kvp.Value.Total > 0 && kvp.Value.Played == kvp.Value.Total)
-                        .Select(kvp => kvp.Key);
-                }
+                // BoxSet: played = all children played.
+                IQueryable<Guid> playedBoxSetIds = hasBoxSet
+                    ? GetFullyPlayedFolderIdsQuery(
+                        context,
+                        baseQuery.Where(e => e.Type == boxSetTypeName).Select(e => e.Id),
+                        filter.User!)
+                    : Enumerable.Empty<Guid>().AsQueryable();
 
                 // Non-folder items: check UserData directly
                 var playedItemIds = context.UserData
diff --git a/MediaBrowser.Controller/Persistence/IItemQueryHelpers.cs b/MediaBrowser.Controller/Persistence/IItemQueryHelpers.cs
index 45fa92c90b..2e29cbdbba 100644
--- a/MediaBrowser.Controller/Persistence/IItemQueryHelpers.cs
+++ b/MediaBrowser.Controller/Persistence/IItemQueryHelpers.cs
@@ -79,6 +79,19 @@ public interface IItemQueryHelpers
         Guid ancestorId);
 
     /// <summary>
+    /// Builds an <see cref="IQueryable{Guid}"/> of folder IDs whose descendants are all played
+    /// for the given user. Composable into outer queries to avoid an extra DB roundtrip.
+    /// </summary>
+    /// <param name="context">The database context the resulting query is bound to.</param>
+    /// <param name="folderIds">A query yielding candidate folder IDs.</param>
+    /// <param name="user">The user for access filtering and played status.</param>
+    /// <returns>An <see cref="IQueryable{Guid}"/> of fully-played folder IDs.</returns>
+    IQueryable<Guid> GetFullyPlayedFolderIdsQuery(
+        JellyfinDbContext context,
+        IQueryable<Guid> folderIds,
+        User user);
+
+    /// <summary>
     /// Deserializes a <see cref="BaseItemEntity"/> into a <see cref="BaseItem"/>.
     /// </summary>
     /// <param name="entity">The database entity.</param>