1 files changed, 74 insertions, 45 deletions
diff --git a/Jellyfin.Server.Implementations/Item/BaseItemRepository.ByName.cs b/Jellyfin.Server.Implementations/Item/BaseItemRepository.ByName.cs
index 6fe8563bf5..5a41619390 100644
--- a/Jellyfin.Server.Implementations/Item/BaseItemRepository.ByName.cs
+++ b/Jellyfin.Server.Implementations/Item/BaseItemRepository.ByName.cs
@@ -4,6 +4,7 @@ using System;
 using System.Collections.Generic;
 using System.Linq;
 using Jellyfin.Data.Enums;
+using Jellyfin.Database.Implementations;
 using Jellyfin.Database.Implementations.Entities;
 using MediaBrowser.Controller.Entities;
 using MediaBrowser.Model.Dto;
@@ -109,7 +110,6 @@ public sealed partial class BaseItemRepository
                 IsNews = filter.IsNews,
                 IsSeries = filter.IsSeries
             })
-            .Where(e => e.MediaStreams != null)
             .SelectMany(e => e.MediaStreams!)
             .Where(e => e.StreamType == (MediaStreamTypeEntity)mediaStreamType)
             .Select(s => string.IsNullOrEmpty(s.Language) ? "und" : s.Language) // und = undetermined
@@ -144,11 +144,6 @@ public sealed partial class BaseItemRepository
     {
         ArgumentNullException.ThrowIfNull(filter);
 
-        if (!filter.Limit.HasValue)
-        {
-            filter.EnableTotalRecordCount = false;
-        }
-
         using var context = _dbProvider.CreateDbContext();
 
         var innerQueryFilter = TranslateQuery(context.BaseItems.Where(e => e.Id != EF.Constant(PlaceholderId)), context, new InternalItemsQuery(filter.User)
@@ -168,21 +163,16 @@ public sealed partial class BaseItemRepository
             IsSeries = filter.IsSeries
         });
 
-        // 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(
-                innerQueryFilter,
-                ivm => ivm.ItemId,
-                g => g.Id,
-                (ivm, g) => ivm.ItemValue.CleanValue)
-            .Distinct();
-
         var innerQuery = PrepareItemQuery(context, filter)
             .Where(e => e.Type == returnType)
-            .Where(e => matchingCleanValues.Contains(e.CleanName!));
+            .Where(e => context.ItemValuesMap
+                .Where(ivm => itemValueTypes.Contains(ivm.ItemValue.Type) && ivm.ItemValue.CleanValue == e.CleanName)
+                .Join(
+                    innerQueryFilter,
+                    ivm => ivm.ItemId,
+                    g => g.Id,
+                    (ivm, g) => ivm.ItemId)
+                .Any());
 
         var outerQueryFilter = new InternalItemsQuery(filter.User)
         {
@@ -205,32 +195,42 @@ public sealed partial class BaseItemRepository
             ExcludeItemIds = filter.ExcludeItemIds
         };
 
-        // Collapse rows that share a PresentationUniqueKey (e.g. alternate versions) by picking
-        // the lowest Id per group. For MusicArtist, prefer the entity from a library the user
-        // can actually access,since the same artist can have a folder in multiple libraries.
-        // Keep as an IQueryable sub-select so paging is applied AFTER
-        // ApplyOrder runs the caller's actual sort.
+        // Collapse rows that share a PresentationUniqueKey (e.g. alternate versions) into one
+        // representative id per group, then materialize the representative ids once.
         var masterQuery = TranslateQuery(innerQuery, context, outerQueryFilter);
         var isMusicArtist = returnType == _itemTypeLookup.BaseItemKindNames[BaseItemKind.MusicArtist];
-        var representativeIds = isMusicArtist
-            ? masterQuery
+        List<Guid> representativeIds;
+        if (isMusicArtist)
+        {
+            // For MusicArtist, prefer the entity from a library the user can actually access.
+            // Materialize to prevent correlated per-group first-row queries which hurt performance.
+            var topParentIds = filter.TopParentIds;
+            representativeIds = masterQuery
+                .Select(e => new { e.Id, e.PresentationUniqueKey, e.TopParentId })
+                .AsEnumerable()
                 .GroupBy(e => e.PresentationUniqueKey)
                 .Select(g => g
-                    .OrderBy(e => filter.TopParentIds.Contains(e.TopParentId ?? Guid.Empty) ? 0 : 1)
+                    .OrderBy(e => topParentIds.Contains(e.TopParentId ?? Guid.Empty) ? 0 : 1)
                     .ThenBy(e => e.Id)
                     .First().Id)
-            : masterQuery
+                .ToList();
+        }
+        else
+        {
+            representativeIds = masterQuery
                 .GroupBy(e => e.PresentationUniqueKey)
-                .Select(g => g.Min(e => e.Id));
+                .Select(g => g.Min(e => e.Id))
+                .ToList();
+        }
 
         var result = new QueryResult<(BaseItemDto, ItemCounts?)>();
         if (filter.EnableTotalRecordCount)
         {
-            result.TotalRecordCount = representativeIds.Count();
+            result.TotalRecordCount = representativeIds.Count;
         }
 
         var query = ApplyNavigations(
-                context.BaseItems.AsNoTracking().AsSingleQuery().Where(e => representativeIds.Contains(e.Id)),
+                context.BaseItems.AsNoTracking().AsSingleQuery().WhereOneOrMany(representativeIds, e => e.Id),
                 filter);
 
         query = ApplyOrder(query, filter, context);
@@ -311,8 +311,8 @@ public sealed partial class BaseItemRepository
         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
-        return context.ItemValuesMap
+        // Instead, start from ItemValueMaps and join with BaseItems.
+        var rawCounts = context.ItemValuesMap
             .Where(ivm => itemValueTypes.Contains(ivm.ItemValue.Type))
             .Where(ivm => itemIds.Contains(ivm.ItemId))
             .Join(
@@ -322,18 +322,47 @@ public sealed partial class BaseItemRepository
                 (ivm, e) => new { CleanName = ivm.ItemValue.CleanValue, e.Type })
             .GroupBy(x => new { x.CleanName, x.Type })
             .Select(g => new { g.Key.CleanName, g.Key.Type, Count = g.Count() })
-            .GroupBy(x => x.CleanName)
-            .ToDictionary(
-                g => g.Key,
-                g => new ItemCounts
+            .AsEnumerable();
+
+        var countsByCleanName = new Dictionary<string, ItemCounts>();
+        foreach (var group in rawCounts.GroupBy(x => x.CleanName))
+        {
+            var counts = new ItemCounts();
+            foreach (var row in group)
+            {
+                if (row.Type == seriesTypeName)
+                {
+                    counts.SeriesCount += row.Count;
+                }
+                else if (row.Type == episodeTypeName)
+                {
+                    counts.EpisodeCount += row.Count;
+                }
+                else if (row.Type == movieTypeName)
+                {
+                    counts.MovieCount += row.Count;
+                }
+                else if (row.Type == musicAlbumTypeName)
                 {
-                    SeriesCount = g.Where(x => x.Type == seriesTypeName).Sum(x => x.Count),
-                    EpisodeCount = g.Where(x => x.Type == episodeTypeName).Sum(x => x.Count),
-                    MovieCount = g.Where(x => x.Type == movieTypeName).Sum(x => x.Count),
-                    AlbumCount = g.Where(x => x.Type == musicAlbumTypeName).Sum(x => x.Count),
-                    ArtistCount = g.Where(x => x.Type == musicArtistTypeName).Sum(x => x.Count),
-                    SongCount = g.Where(x => x.Type == audioTypeName).Sum(x => x.Count),
-                    TrailerCount = g.Where(x => x.Type == trailerTypeName).Sum(x => x.Count),
-                });
+                    counts.AlbumCount += row.Count;
+                }
+                else if (row.Type == musicArtistTypeName)
+                {
+                    counts.ArtistCount += row.Count;
+                }
+                else if (row.Type == audioTypeName)
+                {
+                    counts.SongCount += row.Count;
+                }
+                else if (row.Type == trailerTypeName)
+                {
+                    counts.TrailerCount += row.Count;
+                }
+            }
+
+            countsByCleanName[group.Key] = counts;
+        }
+
+        return countsByCleanName;
     }
 }