Fix too many SQL variables error on large libraries

4 files changed, 12 insertions, 17 deletions

diff --git a/Emby.Server.Implementations/Library/UserDataManager.cs b/Emby.Server.Implementations/Library/UserDataManager.cs
index 9e138dbdaa..1281f1587f 100644
--- a/Emby.Server.Implementations/Library/UserDataManager.cs
+++ b/Emby.Server.Implementations/Library/UserDataManager.cs
@@ -219,7 +219,9 @@ namespace Emby.Server.Implementations.Library
                 using var context = _repository.CreateDbContext();
                 var userDataArray = context.UserData
                     .AsNoTracking()
-                    .Where(e => allItemIds.Contains(e.ItemId) && allKeys.Contains(e.CustomDataKey) && e.UserId.Equals(user.Id))
+                    .Where(e => e.UserId.Equals(user.Id))
+                    .WhereOneOrMany(allItemIds, e => e.ItemId)
+                    .WhereOneOrMany(allKeys, e => e.CustomDataKey)
                     .ToArray();
 
                 var userDataByItem = userDataArray.GroupBy(e => e.ItemId).ToDictionary(g => g.Key, g => g.ToArray());
diff --git a/Jellyfin.Server.Implementations/Item/BaseItemRepository.QueryBuilding.cs b/Jellyfin.Server.Implementations/Item/BaseItemRepository.QueryBuilding.cs
index 812b6ab59c..12bb1e95d4 100644
--- a/Jellyfin.Server.Implementations/Item/BaseItemRepository.QueryBuilding.cs
+++ b/Jellyfin.Server.Implementations/Item/BaseItemRepository.QueryBuilding.cs
@@ -62,29 +62,23 @@ public sealed partial class BaseItemRepository
 
     private IQueryable<BaseItemEntity> ApplyGroupingFilter(JellyfinDbContext context, IQueryable<BaseItemEntity> dbQuery, InternalItemsQuery filter)
     {
-        // This whole block is needed to filter duplicate entries on request
-        // for the time being it cannot be used because it would destroy the ordering
-        // this results in "duplicate" responses for queries that try to lookup individual series or multiple versions but
-        // for that case the invoker has to run a DistinctBy(e => e.PresentationUniqueKey) on their own
-
+        // Collapse duplicates sharing a presentation key (e.g. alternate versions) by picking
+        // the min Id per group. Keep the grouped ids as an IQueryable sub-select; materializing
+        // to a List would inline one bound parameter per id and hit SQLite's variable cap.
         var enableGroupByPresentationUniqueKey = EnableGroupByPresentationUniqueKey(filter);
-        // Materialize GroupBy IDs first to split the complex expression tree.
-        // This runs the filter+GroupBy+Min as one simple SQL query, then the downstream
-        // Order/Paging/Navigations work on a flat WHERE Id IN (...) list, avoiding
-        // EF Core having to compile a deeply nested expression tree.
         if (enableGroupByPresentationUniqueKey && filter.GroupBySeriesPresentationUniqueKey)
         {
-            var groupedIds = dbQuery.GroupBy(e => new { e.PresentationUniqueKey, e.SeriesPresentationUniqueKey }).Select(e => e.Min(x => x.Id)).ToList();
+            var groupedIds = dbQuery.GroupBy(e => new { e.PresentationUniqueKey, e.SeriesPresentationUniqueKey }).Select(e => e.Min(x => x.Id));
             dbQuery = context.BaseItems.Where(e => groupedIds.Contains(e.Id));
         }
         else if (enableGroupByPresentationUniqueKey)
         {
-            var groupedIds = dbQuery.GroupBy(e => e.PresentationUniqueKey).Select(e => e.Min(x => x.Id)).ToList();
+            var groupedIds = dbQuery.GroupBy(e => e.PresentationUniqueKey).Select(e => e.Min(x => x.Id));
             dbQuery = context.BaseItems.Where(e => groupedIds.Contains(e.Id));
         }
         else if (filter.GroupBySeriesPresentationUniqueKey)
         {
-            var groupedIds = dbQuery.GroupBy(e => e.SeriesPresentationUniqueKey).Select(e => e.Min(x => x.Id)).ToList();
+            var groupedIds = dbQuery.GroupBy(e => e.SeriesPresentationUniqueKey).Select(e => e.Min(x => x.Id));
             dbQuery = context.BaseItems.Where(e => groupedIds.Contains(e.Id));
         }
         else
@@ -96,8 +90,7 @@ public sealed partial class BaseItemRepository
         {
             dbQuery = ApplyBoxSetCollapsing(context, dbQuery, filter.CollapseBoxSetItemTypes);
 
-            // Apply name-range filters after collapse so BoxSets are filtered by their own name,
-            // not by their children's names.
+            // Name filters run after collapse so BoxSets match by their own name, not a child's.
             dbQuery = ApplyNameFilters(dbQuery, filter);
         }
 
diff --git a/Jellyfin.Server/Migrations/Routines/FixIncorrectOwnerIdRelationships.cs b/Jellyfin.Server/Migrations/Routines/FixIncorrectOwnerIdRelationships.cs
index 8538f5d7dc..c743590e43 100644
--- a/Jellyfin.Server/Migrations/Routines/FixIncorrectOwnerIdRelationships.cs
+++ b/Jellyfin.Server/Migrations/Routines/FixIncorrectOwnerIdRelationships.cs
@@ -294,7 +294,7 @@ public class FixIncorrectOwnerIdRelationships : IAsyncMigrationRoutine
         // Batch-load all child items in a single query
         var childIds = alternateVersionLinks.Select(l => l.ChildId).Distinct().ToList();
         var childItems = await context.BaseItems
-            .Where(b => childIds.Contains(b.Id))
+            .WhereOneOrMany(childIds, b => b.Id)
             .ToDictionaryAsync(b => b.Id, cancellationToken)
             .ConfigureAwait(false);
 
diff --git a/Jellyfin.Server/Migrations/Routines/MigrateLinkedChildren.cs b/Jellyfin.Server/Migrations/Routines/MigrateLinkedChildren.cs
index f0e4803f18..129d443ca5 100644
--- a/Jellyfin.Server/Migrations/Routines/MigrateLinkedChildren.cs
+++ b/Jellyfin.Server/Migrations/Routines/MigrateLinkedChildren.cs
@@ -209,7 +209,7 @@ internal class MigrateLinkedChildren : IDatabaseMigrationRoutine
 
                 var childIds = toInsert.Select(lc => lc.ChildId).Distinct().ToList();
                 var existingChildIds = context.BaseItems
-                    .Where(b => childIds.Contains(b.Id))
+                    .WhereOneOrMany(childIds, b => b.Id)
                     .Select(b => b.Id)
                     .ToHashSet();