Optimize Validator and Filter Performance

4 files changed, 144 insertions, 49 deletions

diff --git a/Jellyfin.Server.Implementations/Item/BaseItemRepository.cs b/Jellyfin.Server.Implementations/Item/BaseItemRepository.cs
index 22178e57f7..55ef5972d4 100644
--- a/Jellyfin.Server.Implementations/Item/BaseItemRepository.cs
+++ b/Jellyfin.Server.Implementations/Item/BaseItemRepository.cs
@@ -993,7 +993,7 @@ public sealed class BaseItemRepository
             dbQuery = dbQuery.Include(e => e.Extras);
         }
 
-        return dbQuery;
+        return dbQuery.AsSingleQuery();
     }
 
     private IQueryable<BaseItemEntity> ApplyQueryPaging(IQueryable<BaseItemEntity> dbQuery, InternalItemsQuery filter)
@@ -1047,6 +1047,62 @@ public sealed class BaseItemRepository
     }
 
     /// <inheritdoc />
+    public QueryFiltersLegacy GetQueryFiltersLegacy(InternalItemsQuery filter)
+    {
+        ArgumentNullException.ThrowIfNull(filter);
+        PrepareFilterQuery(filter);
+
+        using var context = _dbProvider.CreateDbContext();
+        var baseQuery = PrepareItemQuery(context, filter);
+        baseQuery = TranslateQuery(baseQuery, context, filter);
+
+        // Get matching item IDs as a subquery (not materialized)
+        var matchingItemIds = baseQuery.Select(e => e.Id);
+
+        // Query distinct years directly from the database
+        var years = baseQuery
+            .Where(e => e.ProductionYear != null && e.ProductionYear > 0)
+            .Select(e => e.ProductionYear!.Value)
+            .Distinct()
+            .OrderBy(y => y)
+            .ToArray();
+
+        // Query distinct official ratings directly from the database
+        var officialRatings = baseQuery
+            .Where(e => e.OfficialRating != null && e.OfficialRating != string.Empty)
+            .Select(e => e.OfficialRating!)
+            .Distinct()
+            .OrderBy(r => r)
+            .ToArray();
+
+        // Tags via ItemValuesMap JOIN - uses subquery for matching items
+        var tags = context.ItemValuesMap
+            .Where(ivm => ivm.ItemValue.Type == ItemValueType.Tags)
+            .Where(ivm => matchingItemIds.Contains(ivm.ItemId))
+            .Select(ivm => ivm.ItemValue.CleanValue)
+            .Distinct()
+            .OrderBy(t => t)
+            .ToArray();
+
+        // Genres via ItemValuesMap JOIN - uses subquery for matching items
+        var genres = context.ItemValuesMap
+            .Where(ivm => ivm.ItemValue.Type == ItemValueType.Genre)
+            .Where(ivm => matchingItemIds.Contains(ivm.ItemId))
+            .Select(ivm => ivm.ItemValue.CleanValue)
+            .Distinct()
+            .OrderBy(g => g)
+            .ToArray();
+
+        return new QueryFiltersLegacy
+        {
+            Years = years,
+            OfficialRatings = officialRatings,
+            Tags = tags,
+            Genres = genres
+        };
+    }
+
+    /// <inheritdoc />
     public ItemCounts GetItemCounts(InternalItemsQuery filter)
     {
         ArgumentNullException.ThrowIfNull(filter);
@@ -1229,8 +1285,6 @@ public sealed class BaseItemRepository
             }
         }
 
-        context.SaveChanges();
-
         var itemValueMaps = tuples
             .Select(e => (e.Item, Values: GetItemValuesToSave(e.Item, e.InheritedTags)))
             .ToArray();
@@ -1255,7 +1309,6 @@ public sealed class BaseItemRepository
             Value = f.Value
         }).ToArray();
         context.ItemValues.AddRange(missingItemValues);
-        context.SaveChanges();
 
         var itemValuesStore = existingValues.Concat(missingItemValues).ToArray();
         var valueMap = itemValueMaps
@@ -1291,8 +1344,6 @@ public sealed class BaseItemRepository
             context.ItemValuesMap.RemoveRange(itemMappedValues);
         }
 
-        context.SaveChanges();
-
         var itemsWithAncestors = tuples
             .Where(t => t.Item.SupportsAncestors && t.AncestorIds != null)
             .Select(t => t.Item.Id)
@@ -1619,7 +1670,8 @@ public sealed class BaseItemRepository
             .Include(e => e.LockedFields)
             .Include(e => e.UserData)
             .Include(e => e.Images)
-            .Include(e => e.LinkedChildEntities);
+            .Include(e => e.LinkedChildEntities)
+            .AsSingleQuery();
 
         var item = dbQuery.FirstOrDefault(e => e.Id == id);
         if (item is null)
@@ -2780,7 +2832,7 @@ public sealed class BaseItemRepository
         if (filter.TrailerTypes.Length > 0)
         {
             var trailerTypes = filter.TrailerTypes.Select(e => (int)e).ToArray();
-            baseQuery = baseQuery.Where(e => trailerTypes.Any(f => e.TrailerTypes!.Any(w => w.Id == f)));
+            baseQuery = baseQuery.Where(e => e.TrailerTypes!.Any(w => trailerTypes.Contains(w.Id)));
         }
 
         if (filter.IsAiring.HasValue)
@@ -2896,29 +2948,31 @@ public sealed class BaseItemRepository
 
         if (filter.IsFavoriteOrLiked.HasValue)
         {
+            var favoriteItemIds = context.UserData
+                .Where(ud => ud.UserId == filter.User!.Id && ud.IsFavorite)
+                .Select(ud => ud.ItemId);
             if (filter.IsFavoriteOrLiked.Value)
             {
-                baseQuery = baseQuery
-                    .Where(e => e.UserData!.Any(f => f.UserId == filter.User!.Id && f.IsFavorite));
+                baseQuery = baseQuery.Where(e => favoriteItemIds.Contains(e.Id));
             }
             else
             {
-                baseQuery = baseQuery
-                    .Where(e => !e.UserData!.Any(f => f.UserId == filter.User!.Id && f.IsFavorite));
+                baseQuery = baseQuery.Where(e => !favoriteItemIds.Contains(e.Id));
             }
         }
 
         if (filter.IsFavorite.HasValue)
         {
+            var favoriteItemIds = context.UserData
+                .Where(ud => ud.UserId == filter.User!.Id && ud.IsFavorite)
+                .Select(ud => ud.ItemId);
             if (filter.IsFavorite.Value)
             {
-                baseQuery = baseQuery
-                    .Where(e => e.UserData!.Any(f => f.UserId == filter.User!.Id && f.IsFavorite));
+                baseQuery = baseQuery.Where(e => favoriteItemIds.Contains(e.Id));
             }
             else
             {
-                baseQuery = baseQuery
-                    .Where(e => !e.UserData!.Any(f => f.UserId == filter.User!.Id && f.IsFavorite));
+                baseQuery = baseQuery.Where(e => !favoriteItemIds.Contains(e.Id));
             }
         }
 
@@ -2927,44 +2981,56 @@ public sealed class BaseItemRepository
             // We should probably figure this out for all folders, but for right now, this is the only place where we need it
             if (filter.IncludeItemTypes.Length == 1 && filter.IncludeItemTypes[0] == BaseItemKind.Series)
             {
-                // Use subquery to find series with played episodes - stays in SQL instead of materializing to HashSet
-                var playedSeriesKeysSubquery = context.BaseItems
-                    .Where(e => !e.IsFolder && !e.IsVirtualItem && e.SeriesPresentationUniqueKey != null)
-                    .Where(e => e.UserData!.Any(ud => ud.UserId == filter.User!.Id && ud.Played))
-                    .Select(e => e.SeriesPresentationUniqueKey!);
+                // Get played series IDs by joining episodes to UserData via SeriesId (Guid foreign key).
+                // Don't filter episodes by TopParentIds here - the series will be filtered by baseQuery anyway.
+                // This allows the materialized list to be reused across library-scoped queries.
+                var playedSeriesIdList = context.BaseItems
+                    .Where(e => !e.IsFolder && !e.IsVirtualItem && e.SeriesId.HasValue)
+                    .Join(
+                        context.UserData.Where(ud => ud.UserId == filter.User!.Id && ud.Played),
+                        episode => episode.Id,
+                        ud => ud.ItemId,
+                        (episode, ud) => episode.SeriesId!.Value)
+                    .Distinct()
+                    .ToList();
 
                 if (filter.IsPlayed.Value)
                 {
-                    baseQuery = baseQuery.Where(e => playedSeriesKeysSubquery.Contains(e.PresentationUniqueKey!));
+                    baseQuery = baseQuery.Where(s => playedSeriesIdList.Contains(s.Id));
                 }
                 else
                 {
-                    baseQuery = baseQuery.Where(e => !playedSeriesKeysSubquery.Contains(e.PresentationUniqueKey!));
+                    baseQuery = baseQuery.Where(s => !playedSeriesIdList.Contains(s.Id));
                 }
             }
-            else if (filter.IsPlayed.Value)
-            {
-                baseQuery = baseQuery
-                    .Where(e => e.UserData!.Any(f => f.UserId == filter.User!.Id && f.Played));
-            }
             else
             {
-                baseQuery = baseQuery
-                    .Where(e => !e.UserData!.Any(f => f.UserId == filter.User!.Id && f.Played));
+                var playedItemIds = context.UserData
+                    .Where(ud => ud.UserId == filter.User!.Id && ud.Played)
+                    .Select(ud => ud.ItemId);
+                if (filter.IsPlayed.Value)
+                {
+                    baseQuery = baseQuery.Where(e => playedItemIds.Contains(e.Id));
+                }
+                else
+                {
+                    baseQuery = baseQuery.Where(e => !playedItemIds.Contains(e.Id));
+                }
             }
         }
 
         if (filter.IsResumable.HasValue)
         {
+            var resumableItemIds = context.UserData
+                .Where(ud => ud.UserId == filter.User!.Id && ud.PlaybackPositionTicks > 0)
+                .Select(ud => ud.ItemId);
             if (filter.IsResumable.Value)
             {
-                baseQuery = baseQuery
-                       .Where(e => e.UserData!.Any(f => f.UserId == filter.User!.Id && f.PlaybackPositionTicks > 0));
+                baseQuery = baseQuery.Where(e => resumableItemIds.Contains(e.Id));
             }
             else
             {
-                baseQuery = baseQuery
-                       .Where(e => !e.UserData!.Any(f => f.UserId == filter.User!.Id && f.PlaybackPositionTicks > 0));
+                baseQuery = baseQuery.Where(e => !resumableItemIds.Contains(e.Id));
             }
         }
 
@@ -3681,9 +3747,12 @@ public sealed class BaseItemRepository
 
     private static (int Played, int Total) GetPlayedAndTotalCountFromQuery(IQueryable<BaseItemEntity> query, Guid userId)
     {
+        // GroupBy with a constant key aggregates all rows into a single group for server-side counting.
+        // OrderBy is required before FirstOrDefault to avoid EF Core warnings about unpredictable results.
         var result = query
             .Select(b => b.UserData!.Any(u => u.UserId == userId && u.Played))
-            .GroupBy(_ => 1) // Hack to aggregate over entire set
+            .GroupBy(_ => 1)
+            .OrderBy(g => g.Key)
             .Select(g => new
             {
                 Total = g.Count(),
diff --git a/Jellyfin.Server.Implementations/Item/FolderAwareFilterExtensions.cs b/Jellyfin.Server.Implementations/Item/FolderAwareFilterExtensions.cs
index d7b2567f37..888dacd16b 100644
--- a/Jellyfin.Server.Implementations/Item/FolderAwareFilterExtensions.cs
+++ b/Jellyfin.Server.Implementations/Item/FolderAwareFilterExtensions.cs
@@ -26,13 +26,25 @@ internal static class FolderAwareFilterExtensions
         JellyfinDbContext context,
         Expression<Func<BaseItemEntity, bool>> condition)
     {
-        // Use correlated Any() subqueries instead of UNION + Contains for better index utilization
-        var matchingIds = context.BaseItems.Where(condition).Select(b => b.Id);
+        // Get IDs of items that directly match the condition
+        var directMatchIds = context.BaseItems.Where(condition).Select(b => b.Id);
 
-        return query.Where(e =>
-            matchingIds.Contains(e.Id)
-            || context.AncestorIds.Any(a => a.ParentItemId == e.Id && matchingIds.Contains(a.ItemId))
-            || context.LinkedChildren.Any(lc => lc.ParentId == e.Id && matchingIds.Contains(lc.ChildId)));
+        // Get parent IDs where a descendant (via AncestorIds) matches
+        var ancestorMatchIds = context.AncestorIds
+            .Where(a => directMatchIds.Contains(a.ItemId))
+            .Select(a => a.ParentItemId);
+
+        // Get parent IDs where a linked child matches
+        var linkedMatchIds = context.LinkedChildren
+            .Where(lc => directMatchIds.Contains(lc.ChildId))
+            .Select(lc => lc.ParentId);
+
+        var allMatchingIds = directMatchIds
+            .Concat(ancestorMatchIds)
+            .Concat(linkedMatchIds)
+            .Distinct();
+
+        return query.Where(e => allMatchingIds.Contains(e.Id));
     }
 
     /// <summary>
@@ -48,11 +60,24 @@ internal static class FolderAwareFilterExtensions
         JellyfinDbContext context,
         Expression<Func<BaseItemEntity, bool>> condition)
     {
-        var matchingIds = context.BaseItems.Where(condition).Select(b => b.Id);
+        // Get IDs of items that directly match the condition
+        var directMatchIds = context.BaseItems.Where(condition).Select(b => b.Id);
+
+        // Get parent IDs where a descendant (via AncestorIds) matches
+        var ancestorMatchIds = context.AncestorIds
+            .Where(a => directMatchIds.Contains(a.ItemId))
+            .Select(a => a.ParentItemId);
+
+        // Get parent IDs where a linked child matches
+        var linkedMatchIds = context.LinkedChildren
+            .Where(lc => directMatchIds.Contains(lc.ChildId))
+            .Select(lc => lc.ParentId);
+
+        var allMatchingIds = directMatchIds
+            .Concat(ancestorMatchIds)
+            .Concat(linkedMatchIds)
+            .Distinct();
 
-        return query.Where(e =>
-            !matchingIds.Contains(e.Id)
-            && !context.AncestorIds.Any(a => a.ParentItemId == e.Id && matchingIds.Contains(a.ItemId))
-            && !context.LinkedChildren.Any(lc => lc.ParentId == e.Id && matchingIds.Contains(lc.ChildId)));
+        return query.Where(e => !allMatchingIds.Contains(e.Id));
     }
 }
diff --git a/Jellyfin.Server.Implementations/Item/PeopleRepository.cs b/Jellyfin.Server.Implementations/Item/PeopleRepository.cs
index 355ed64797..022b452e85 100644
--- a/Jellyfin.Server.Implementations/Item/PeopleRepository.cs
+++ b/Jellyfin.Server.Implementations/Item/PeopleRepository.cs
@@ -62,10 +62,9 @@ public class PeopleRepository(IDbContextFactory<JellyfinDbContext> dbProvider, I
         using var context = _dbProvider.CreateDbContext();
         var dbQuery = TranslateQuery(context.Peoples.AsNoTracking(), context, filter).Select(e => e.Name).Distinct();
 
-        // dbQuery = dbQuery.OrderBy(e => e.ListOrder);
         if (filter.Limit > 0)
         {
-            dbQuery = dbQuery.Take(filter.Limit);
+            dbQuery = dbQuery.OrderBy(e => e).Take(filter.Limit);
         }
 
         return dbQuery.ToArray();
diff --git a/Jellyfin.Server.Implementations/Users/UserManager.cs b/Jellyfin.Server.Implementations/Users/UserManager.cs
index 501cb4fbe8..7292e9c7a9 100644
--- a/Jellyfin.Server.Implementations/Users/UserManager.cs
+++ b/Jellyfin.Server.Implementations/Users/UserManager.cs
@@ -93,7 +93,7 @@ namespace Jellyfin.Server.Implementations.Users
             _users = new ConcurrentDictionary<Guid, User>();
             using var dbContext = _dbProvider.CreateDbContext();
             foreach (var user in dbContext.Users
-                .AsSplitQuery()
+                .AsSingleQuery()
                 .Include(user => user.Permissions)
                 .Include(user => user.Preferences)
                 .Include(user => user.AccessSchedules)
@@ -607,6 +607,7 @@ namespace Jellyfin.Server.Implementations.Users
                                .Include(u => u.Preferences)
                                .Include(u => u.AccessSchedules)
                                .Include(u => u.ProfileImage)
+                               .AsSingleQuery()
                                .FirstOrDefault(u => u.Id.Equals(userId))
                            ?? throw new ArgumentException("No user exists with given Id!");
 
@@ -651,6 +652,7 @@ namespace Jellyfin.Server.Implementations.Users
                                .Include(u => u.Preferences)
                                .Include(u => u.AccessSchedules)
                                .Include(u => u.ProfileImage)
+                               .AsSingleQuery()
                                .FirstOrDefault(u => u.Id.Equals(userId))
                            ?? throw new ArgumentException("No user exists with given Id!");