Remove unnecessary materializations

6 files changed, 13 insertions, 35 deletions

diff --git a/Jellyfin.Server.Implementations/Item/BaseItemMapper.cs b/Jellyfin.Server.Implementations/Item/BaseItemMapper.cs
index 831e7c3354..67a233c41d 100644
--- a/Jellyfin.Server.Implementations/Item/BaseItemMapper.cs
+++ b/Jellyfin.Server.Implementations/Item/BaseItemMapper.cs
@@ -168,9 +168,6 @@ internal static class BaseItemMapper
             dto.ImageInfos = entity.Images.Select(e => MapImageFromEntity(e, appHost)).ToArray();
         }
 
-        // dto.Type = entity.Type;
-        // dto.Data = entity.Data;
-        // dto.MediaType = Enum.TryParse<MediaType>(entity.MediaType);
         if (dto is IHasStartDate hasStartDate)
         {
             hasStartDate.StartDate = entity.StartDate.GetValueOrDefault();
@@ -354,8 +351,6 @@ internal static class BaseItemMapper
             }).ToArray() ?? [];
         }
 
-        // dto.Type = entity.Type;
-        // dto.Data = entity.Data;
         entity.MediaType = dto.MediaType.ToString();
         if (dto is IHasStartDate hasStartDate)
         {
diff --git a/Jellyfin.Server.Implementations/Item/BaseItemRepository.ByName.cs b/Jellyfin.Server.Implementations/Item/BaseItemRepository.ByName.cs
index 907d8527aa..c4464008d4 100644
--- a/Jellyfin.Server.Implementations/Item/BaseItemRepository.ByName.cs
+++ b/Jellyfin.Server.Implementations/Item/BaseItemRepository.ByName.cs
@@ -133,8 +133,9 @@ public sealed partial class BaseItemRepository
             IsSeries = filter.IsSeries
         });
 
-        // Materialize the matching CleanValues early. This splits one massive expression tree
-        // into two simpler queries, dramatically reducing EF Core expression compilation time.
+        // Keep this as an IQueryable sub-select. Materializing to a list would inline one
+        // bound parameter per CleanValue and hit SQLite's variable cap on libraries with
+        // high-cardinality value types (e.g. tens of thousands of artists).
         var matchingCleanValues = context.ItemValuesMap
             .Where(ivm => itemValueTypes.Contains(ivm.ItemValue.Type))
             .Join(
@@ -142,8 +143,7 @@ public sealed partial class BaseItemRepository
                 ivm => ivm.ItemId,
                 g => g.Id,
                 (ivm, g) => ivm.ItemValue.CleanValue)
-            .Distinct()
-            .ToList();
+            .Distinct();
 
         var innerQuery = PrepareItemQuery(context, filter)
             .Where(e => e.Type == returnType)
@@ -170,10 +170,8 @@ public sealed partial class BaseItemRepository
             ExcludeItemIds = filter.ExcludeItemIds
         };
 
-        // Materialize the matching IDs first. This keeps the complex nested subquery
-        // (inner filter + ItemValues join + search + GroupBy) as a single simple SQL statement,
-        // and then the entity load with Includes uses a flat WHERE Id IN (...) list.
-        // This avoids EF having to compile the entire nested expression tree into the final query.
+        // Build the master query and collapse rows that share a PresentationUniqueKey
+        // (e.g. alternate versions) by picking the lowest Id per group.
         var masterQuery = TranslateQuery(innerQuery, context, outerQueryFilter);
 
         var orderedMasterQuery = ApplyOrder(masterQuery, filter, context)
@@ -229,9 +227,7 @@ public sealed partial class BaseItemRepository
             var musicArtistTypeName = _itemTypeLookup.BaseItemKindNames[BaseItemKind.MusicArtist];
             var audioTypeName = _itemTypeLookup.BaseItemKindNames[BaseItemKind.Audio];
             var trailerTypeName = _itemTypeLookup.BaseItemKindNames[BaseItemKind.Trailer];
-
-            // Materialize the matching IDs to avoid nested subquery in the counts expression tree.
-            var itemIds = itemCountQuery.Select(e => e.Id).ToList();
+            var itemIds = itemCountQuery.Select(e => e.Id);
 
             // Rewrite query to avoid SelectMany on navigation properties (which requires SQL APPLY, not supported on SQLite)
             // Instead, start from ItemValueMaps and join with BaseItems
diff --git a/Jellyfin.Server.Implementations/Item/BaseItemRepository.QueryBuilding.cs b/Jellyfin.Server.Implementations/Item/BaseItemRepository.QueryBuilding.cs
index 12bb1e95d4..02664621d4 100644
--- a/Jellyfin.Server.Implementations/Item/BaseItemRepository.QueryBuilding.cs
+++ b/Jellyfin.Server.Implementations/Item/BaseItemRepository.QueryBuilding.cs
@@ -432,8 +432,8 @@ public sealed partial class BaseItemRepository
                         || (e.TopParentId.HasValue && f.ItemId == e.TopParentId.Value))));
         }
 
-        // Exclude alternate versions and owned non-extra items from counts.
-        // Alternate versions have PrimaryVersionId set (pointing to their primary).
+        // Exclude alternate versions (have PrimaryVersionId set) and owned non-extra items.
+        // Extras (trailers, etc.) have OwnerId set but also have ExtraType set — keep those.
         if (!filter.IncludeOwnedItems)
         {
             baseQuery = baseQuery.Where(e => e.PrimaryVersionId == null && (e.OwnerId == null || e.ExtraType != null));
diff --git a/Jellyfin.Server.Implementations/Item/BaseItemRepository.TranslateQuery.cs b/Jellyfin.Server.Implementations/Item/BaseItemRepository.TranslateQuery.cs
index 664befc2ef..d14b62c3a0 100644
--- a/Jellyfin.Server.Implementations/Item/BaseItemRepository.TranslateQuery.cs
+++ b/Jellyfin.Server.Implementations/Item/BaseItemRepository.TranslateQuery.cs
@@ -376,14 +376,6 @@ public sealed partial class BaseItemRepository
             baseQuery = baseQuery.Where(e => e.Peoples!.Any(f => f.People.Name == filter.Person));
         }
 
-        if (!string.IsNullOrWhiteSpace(filter.MinSortName))
-        {
-            // this does not makes sense.
-            // baseQuery = baseQuery.Where(e => e.SortName >= query.MinSortName);
-            // whereClauses.Add("SortName>=@MinSortName");
-            // statement?.TryBind("@MinSortName", query.MinSortName);
-        }
-
         if (!string.IsNullOrWhiteSpace(filter.ExternalSeriesId))
         {
             baseQuery = baseQuery.Where(e => e.ExternalSeriesId == filter.ExternalSeriesId);
@@ -407,7 +399,6 @@ public sealed partial class BaseItemRepository
             }
         }
 
-        // These are the same, for now
         var nameContains = filter.NameContains;
         if (!string.IsNullOrWhiteSpace(nameContains))
         {
diff --git a/Jellyfin.Server.Implementations/Item/NextUpService.cs b/Jellyfin.Server.Implementations/Item/NextUpService.cs
index b25b347868..d78e246691 100644
--- a/Jellyfin.Server.Implementations/Item/NextUpService.cs
+++ b/Jellyfin.Server.Implementations/Item/NextUpService.cs
@@ -150,13 +150,9 @@ public class NextUpService : INextUpService
             .Where(id => id != Guid.Empty)
             .Distinct()
             .ToList();
-        var lastWatchedEpisodes = new Dictionary<Guid, BaseItemEntity>();
-        if (allLastWatchedIds.Count > 0)
-        {
-            var lwQuery = context.BaseItems.AsNoTracking().Where(e => allLastWatchedIds.Contains(e.Id));
-            lwQuery = _queryHelpers.ApplyNavigations(lwQuery, filter);
-            lastWatchedEpisodes = lwQuery.ToDictionary(e => e.Id);
-        }
+        var lwQuery = context.BaseItems.AsNoTracking().Where(e => allLastWatchedIds.Contains(e.Id));
+        lwQuery = _queryHelpers.ApplyNavigations(lwQuery, filter);
+        var lastWatchedEpisodes = lwQuery.ToDictionary(e => e.Id);
 
         Dictionary<string, List<BaseItemEntity>> specialsBySeriesKey = new();
         if (includeSpecials)
diff --git a/Jellyfin.Server.Implementations/Item/OrderMapper.cs b/Jellyfin.Server.Implementations/Item/OrderMapper.cs
index aeea8db4d4..ada86c8b87 100644
--- a/Jellyfin.Server.Implementations/Item/OrderMapper.cs
+++ b/Jellyfin.Server.Implementations/Item/OrderMapper.cs
@@ -31,7 +31,7 @@ public static class OrderMapper
     {
         return (sortBy, query.User) switch
         {
-            (ItemSortBy.AirTime, _) => e => e.SortName, // TODO
+            (ItemSortBy.AirTime, _) => e => e.SortName,
             (ItemSortBy.Runtime, _) => e => e.RunTimeTicks,
             (ItemSortBy.Random, _) => e => EF.Functions.Random(),
             (ItemSortBy.DatePlayed, _) => e => e.UserData!.Where(f => f.UserId.Equals(query.User!.Id)).OrderBy(f => f.CustomDataKey).FirstOrDefault()!.LastPlayedDate,