Count distinct items for byName ItemCounts and batch every kind

3 files changed, 324 insertions, 254 deletions

diff --git a/Jellyfin.Server.Implementations/Item/BaseItemRepository.ByName.cs b/Jellyfin.Server.Implementations/Item/BaseItemRepository.ByName.cs
index 70e4ca3b1d..cdc8744642 100644
--- a/Jellyfin.Server.Implementations/Item/BaseItemRepository.ByName.cs
+++ b/Jellyfin.Server.Implementations/Item/BaseItemRepository.ByName.cs
@@ -319,14 +319,7 @@ public sealed partial class BaseItemRepository
             .WhereOneOrMany(cleanNames, ivm => ivm.ItemValue.CleanValue);
 
         var seriesTypeName = _itemTypeLookup.BaseItemKindNames[BaseItemKind.Series];
-        var movieTypeName = _itemTypeLookup.BaseItemKindNames[BaseItemKind.Movie];
         var episodeTypeName = _itemTypeLookup.BaseItemKindNames[BaseItemKind.Episode];
-        var musicAlbumTypeName = _itemTypeLookup.BaseItemKindNames[BaseItemKind.MusicAlbum];
-        var musicArtistTypeName = _itemTypeLookup.BaseItemKindNames[BaseItemKind.MusicArtist];
-        var musicVideoTypeName = _itemTypeLookup.BaseItemKindNames[BaseItemKind.MusicVideo];
-        var programTypeName = _itemTypeLookup.BaseItemKindNames[BaseItemKind.LiveTvProgram];
-        var audioTypeName = _itemTypeLookup.BaseItemKindNames[BaseItemKind.Audio];
-        var trailerTypeName = _itemTypeLookup.BaseItemKindNames[BaseItemKind.Trailer];
 
         // Rewrite query to avoid SelectMany on navigation properties (which requires SQL APPLY, not supported on SQLite)
         // Instead, start from ItemValueMaps and join with BaseItems.
@@ -335,9 +328,9 @@ public sealed partial class BaseItemRepository
                 scopedItems,
                 ivm => ivm.ItemId,
                 e => e.Id,
-                (ivm, e) => new { CleanName = ivm.ItemValue.CleanValue, e.Type, e.SeriesId })
+                (ivm, e) => new { CleanName = ivm.ItemValue.CleanValue, e.Type, e.SeriesId, e.Id })
             .GroupBy(x => new { x.CleanName, x.Type, x.SeriesId })
-            .Select(g => new { g.Key.CleanName, g.Key.Type, g.Key.SeriesId, Count = g.Count() })
+            .Select(g => new { g.Key.CleanName, g.Key.Type, g.Key.SeriesId, Count = g.Select(x => x.Id).Distinct().Count() })
             .ToList();
 
         // Only studios and genres pass down from a series to its episodes; an artist credit does not.
@@ -359,46 +352,10 @@ public sealed partial class BaseItemRepository
 
         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 == movieTypeName)
-                {
-                    counts.MovieCount += row.Count;
-                }
-                else if (row.Type == musicAlbumTypeName)
-                {
-                    counts.AlbumCount += row.Count;
-                }
-                else if (row.Type == musicArtistTypeName)
-                {
-                    counts.ArtistCount += row.Count;
-                }
-                else if (row.Type == musicVideoTypeName)
-                {
-                    counts.MusicVideoCount += row.Count;
-                }
-                else if (row.Type == programTypeName)
-                {
-                    counts.ProgramCount += row.Count;
-                }
-                else if (row.Type == audioTypeName)
-                {
-                    counts.SongCount += row.Count;
-                }
-                else if (row.Type == trailerTypeName)
-                {
-                    counts.TrailerCount += row.Count;
-                }
-            }
+            var counts = ItemCountBuilder.Build(_itemTypeLookup, group.Select(row => (row.Type, row.Count)));
 
             // Episodes are counted separately: the value is usually only written on the series.
-            counts.EpisodeCount = episodeCounts.GetValueOrDefault(group.Key);
-            counts.ItemCount = counts.TotalItemCount();
+            ItemCountBuilder.SetEpisodeCount(counts, episodeCounts.GetValueOrDefault(group.Key));
             countsByCleanName[group.Key] = counts;
         }
 
@@ -407,7 +364,9 @@ public sealed partial class BaseItemRepository
         {
             if (!countsByCleanName.ContainsKey(cleanName))
             {
-                countsByCleanName[cleanName] = new ItemCounts { EpisodeCount = episodeCount, ItemCount = episodeCount };
+                var counts = new ItemCounts();
+                ItemCountBuilder.SetEpisodeCount(counts, episodeCount);
+                countsByCleanName[cleanName] = counts;
             }
         }
 
diff --git a/Jellyfin.Server.Implementations/Item/ItemCountBuilder.cs b/Jellyfin.Server.Implementations/Item/ItemCountBuilder.cs
new file mode 100644
index 0000000000..0f8a1b9dfe
--- /dev/null
+++ b/Jellyfin.Server.Implementations/Item/ItemCountBuilder.cs
@@ -0,0 +1,96 @@
+using System;
+using System.Collections.Generic;
+using Jellyfin.Data.Enums;
+using MediaBrowser.Controller.Persistence;
+using MediaBrowser.Model.Dto;
+
+namespace Jellyfin.Server.Implementations.Item;
+
+/// <summary>
+/// Turns per-type counts into an <see cref="ItemCounts"/>.
+/// </summary>
+internal static class ItemCountBuilder
+{
+    /// <summary>
+    /// Builds the counts of one by-name item.
+    /// </summary>
+    /// <param name="itemTypeLookup">The item type lookup.</param>
+    /// <param name="counts">The counted items, by type name. A type may repeat.</param>
+    /// <returns>The counts.</returns>
+    public static ItemCounts Build(IItemTypeLookup itemTypeLookup, IEnumerable<(string Type, int Count)> counts)
+    {
+        ArgumentNullException.ThrowIfNull(itemTypeLookup);
+        ArgumentNullException.ThrowIfNull(counts);
+
+        var lookup = itemTypeLookup.BaseItemKindNames;
+        var result = new ItemCounts();
+
+        foreach (var (type, count) in counts)
+        {
+            // Accumulated rather than assigned: a caller may group by something finer than the
+            // type and hand the same type over more than once.
+            if (string.Equals(type, lookup[BaseItemKind.MusicAlbum], StringComparison.Ordinal))
+            {
+                result.AlbumCount += count;
+            }
+            else if (string.Equals(type, lookup[BaseItemKind.MusicArtist], StringComparison.Ordinal))
+            {
+                result.ArtistCount += count;
+            }
+            else if (string.Equals(type, lookup[BaseItemKind.Episode], StringComparison.Ordinal))
+            {
+                result.EpisodeCount += count;
+            }
+            else if (string.Equals(type, lookup[BaseItemKind.Movie], StringComparison.Ordinal))
+            {
+                result.MovieCount += count;
+            }
+            else if (string.Equals(type, lookup[BaseItemKind.MusicVideo], StringComparison.Ordinal))
+            {
+                result.MusicVideoCount += count;
+            }
+            else if (string.Equals(type, lookup[BaseItemKind.LiveTvProgram], StringComparison.Ordinal))
+            {
+                result.ProgramCount += count;
+            }
+            else if (string.Equals(type, lookup[BaseItemKind.Series], StringComparison.Ordinal))
+            {
+                result.SeriesCount += count;
+            }
+            else if (string.Equals(type, lookup[BaseItemKind.Audio], StringComparison.Ordinal))
+            {
+                result.SongCount += count;
+            }
+            else if (string.Equals(type, lookup[BaseItemKind.Trailer], StringComparison.Ordinal))
+            {
+                result.TrailerCount += count;
+            }
+            else if (string.Equals(type, lookup[BaseItemKind.BoxSet], StringComparison.Ordinal))
+            {
+                result.BoxSetCount += count;
+            }
+            else if (string.Equals(type, lookup[BaseItemKind.Book], StringComparison.Ordinal))
+            {
+                result.BookCount += count;
+            }
+        }
+
+        result.ItemCount = result.TotalItemCount();
+
+        return result;
+    }
+
+    /// <summary>
+    /// Replaces the episode count, which both by-name paths decide separately from the other
+    /// types because a genre or studio is usually written on the series rather than its episodes.
+    /// </summary>
+    /// <param name="counts">The counts to update.</param>
+    /// <param name="episodeCount">The episode count.</param>
+    public static void SetEpisodeCount(ItemCounts counts, int episodeCount)
+    {
+        ArgumentNullException.ThrowIfNull(counts);
+
+        counts.EpisodeCount = episodeCount;
+        counts.ItemCount = counts.TotalItemCount();
+    }
+}
diff --git a/Jellyfin.Server.Implementations/Item/ItemCountService.cs b/Jellyfin.Server.Implementations/Item/ItemCountService.cs
index 8b0aac2836..57705cdf11 100644
--- a/Jellyfin.Server.Implementations/Item/ItemCountService.cs
+++ b/Jellyfin.Server.Implementations/Item/ItemCountService.cs
@@ -126,279 +126,286 @@ public class ItemCountService : IItemCountService
     /// <inheritdoc />
     public ItemCounts GetItemCountsForNameItem(BaseItemKind kind, Guid id, BaseItemKind[] relatedItemKinds, InternalItemsQuery accessFilter)
     {
-        using var context = _dbProvider.CreateDbContext();
-
-        var item = context.BaseItems.AsNoTracking()
-            .Where(e => e.Id == id)
-            .Select(e => new { e.Name, e.CleanName })
-            .FirstOrDefault();
+        return GetItemCountsForNameItems(kind, [id], relatedItemKinds, accessFilter)[id];
+    }
 
-        if (item is null)
+    private static ItemValueType[] GetItemValueTypes(BaseItemKind kind)
+        => kind switch
         {
-            return new ItemCounts();
-        }
+            BaseItemKind.MusicArtist => [ItemValueType.Artist, ItemValueType.AlbumArtist],
+            BaseItemKind.Genre or BaseItemKind.MusicGenre => [ItemValueType.Genre],
+            BaseItemKind.Studio => [ItemValueType.Studios],
+            _ => []
+        };
 
-        IQueryable<BaseItemEntity> baseQuery;
-        switch (kind)
-        {
-            case BaseItemKind.Person:
-                baseQuery = ItemsById(context, context.PeopleBaseItemMap
-                    .AsNoTracking()
-                    .Where(m => m.People.Name == item.Name)
-                    .Select(m => m.ItemId));
-                break;
-            case BaseItemKind.MusicArtist:
-                baseQuery = ItemsById(context, context.ItemValuesMap
-                    .AsNoTracking()
-                    .Where(ivm => ivm.ItemValue.CleanValue == item.CleanName
-                        && (ivm.ItemValue.Type == ItemValueType.Artist || ivm.ItemValue.Type == ItemValueType.AlbumArtist))
-                    .Select(ivm => ivm.ItemId));
-                break;
-            case BaseItemKind.Genre:
-            case BaseItemKind.MusicGenre:
-                baseQuery = ItemsById(context, context.ItemValuesMap
-                    .AsNoTracking()
-                    .Where(ivm => ivm.ItemValue.CleanValue == item.CleanName
-                        && ivm.ItemValue.Type == ItemValueType.Genre)
-                    .Select(ivm => ivm.ItemId));
-                break;
-            case BaseItemKind.Studio:
-                baseQuery = ItemsById(context, context.ItemValuesMap
-                    .AsNoTracking()
-                    .Where(ivm => ivm.ItemValue.CleanValue == item.CleanName
-                        && ivm.ItemValue.Type == ItemValueType.Studios)
-                    .Select(ivm => ivm.ItemId));
-                break;
-            case BaseItemKind.Year:
-                if (int.TryParse(item.Name, NumberStyles.Integer, CultureInfo.InvariantCulture, out var year))
-                {
-                    baseQuery = context.BaseItems
-                        .AsNoTracking()
-                        .Where(e => e.ProductionYear == year);
-                }
-                else
-                {
-                    return new ItemCounts();
-                }
+    /// <inheritdoc />
+    public Dictionary<Guid, ItemCounts> GetItemCountsForNameItems(BaseItemKind kind, IReadOnlyList<Guid> ids, BaseItemKind[] relatedItemKinds, InternalItemsQuery accessFilter)
+    {
+        ArgumentNullException.ThrowIfNull(ids);
+        ArgumentNullException.ThrowIfNull(relatedItemKinds);
+        ArgumentNullException.ThrowIfNull(accessFilter);
 
-                break;
-            default:
-                return new ItemCounts();
+        var result = new Dictionary<Guid, ItemCounts>();
+        if (ids.Count == 0)
+        {
+            return result;
         }
 
-        var typeNames = relatedItemKinds.Select(k => _itemTypeLookup.BaseItemKindNames[k]).ToArray();
-        baseQuery = baseQuery.Where(e => typeNames.Contains(e.Type));
-
-        baseQuery = _queryHelpers.ApplyAccessFiltering(context, baseQuery, accessFilter);
+        using var context = _dbProvider.CreateDbContext();
 
-        var counts = baseQuery
-            .GroupBy(x => x.Type)
-            .Select(x => new { x.Key, Count = x.Count() })
+        var idsArray = ids as Guid[] ?? ids.ToArray();
+        var nameItems = context.BaseItems.AsNoTracking()
+            .WhereOneOrMany(idsArray, e => e.Id)
+            .Select(e => new NameItem(e.Id, e.Name, e.CleanName))
             .ToArray();
 
-        var result = BuildItemCounts(counts.Select(c => (c.Key, c.Count)));
-        var totalCount = result.ItemCount;
+        foreach (var id in ids)
+        {
+            result[id] = new ItemCounts();
+        }
 
-        if (kind is BaseItemKind.Studio or BaseItemKind.Genre or BaseItemKind.MusicGenre
-            && relatedItemKinds.Contains(BaseItemKind.Episode)
-            && relatedItemKinds.Contains(BaseItemKind.Series))
+        if (nameItems.Length == 0)
         {
-            var rolledUpEpisodeCount = CountEpisodesOfTaggedSeries(context, baseQuery, accessFilter, out var directEpisodeCount);
-            totalCount += rolledUpEpisodeCount - result.EpisodeCount + directEpisodeCount;
-            result.EpisodeCount = rolledUpEpisodeCount + directEpisodeCount;
+            return result;
         }
 
-        result.ItemCount = totalCount;
+        var typeNames = relatedItemKinds.Select(k => _itemTypeLookup.BaseItemKindNames[k]).ToArray();
+        var related = _queryHelpers.ApplyAccessFiltering(
+            context,
+            context.BaseItems.AsNoTracking().Where(e => typeNames.Contains(e.Type)),
+            accessFilter);
+
+        var valueTypes = GetItemValueTypes(kind);
+        if (valueTypes.Length > 0)
+        {
+            CountByItemValue(context, related, kind, relatedItemKinds, valueTypes, nameItems, result);
+        }
+        else if (kind == BaseItemKind.Person)
+        {
+            CountByPersonName(context, related, nameItems, result);
+        }
+        else if (kind == BaseItemKind.Year)
+        {
+            CountByProductionYear(related, nameItems, result);
+        }
 
         return result;
     }
 
-    private int CountEpisodesOfTaggedSeries(
+    private void CountByItemValue(
         JellyfinDbContext context,
-        IQueryable<BaseItemEntity> taggedItems,
-        InternalItemsQuery accessFilter,
-        out int unrelatedEpisodeCount)
+        IQueryable<BaseItemEntity> related,
+        BaseItemKind kind,
+        BaseItemKind[] relatedItemKinds,
+        ItemValueType[] valueTypes,
+        NameItem[] nameItems,
+        Dictionary<Guid, ItemCounts> result)
     {
-        var seriesTypeName = _itemTypeLookup.BaseItemKindNames[BaseItemKind.Series];
-        var episodeTypeName = _itemTypeLookup.BaseItemKindNames[BaseItemKind.Episode];
-
-        var taggedSeriesIds = taggedItems.Where(e => e.Type == seriesTypeName).Select(e => e.Id);
-        unrelatedEpisodeCount = taggedItems.Count(e => e.Type == episodeTypeName
-            && (e.SeriesId == null || !taggedSeriesIds.Contains(e.SeriesId.Value)));
-
-        // Materialised so the episode count drives off IX_BaseItems_SeriesId.
-        var seriesIds = taggedItems
-            .Where(e => e.Type == seriesTypeName)
-            .Select(e => e.Id)
+        var cleanNames = nameItems
+            .Select(n => n.CleanName)
+            .OfType<string>()
+            .Distinct(StringComparer.Ordinal)
             .ToArray();
 
-        if (seriesIds.Length == 0)
+        if (cleanNames.Length == 0)
         {
-            return 0;
+            return;
         }
 
-        var episodes = context.BaseItems.AsNoTracking()
-            .Where(e => e.Type == episodeTypeName && e.SeriesId != null)
-            .WhereOneOrMany(seriesIds, e => e.SeriesId!.Value);
+        var grouped = context.ItemValuesMap.AsNoTracking()
+            .Where(ivm => valueTypes.Contains(ivm.ItemValue.Type))
+            .WhereOneOrMany(cleanNames, ivm => ivm.ItemValue.CleanValue)
+            .Join(related, ivm => ivm.ItemId, e => e.Id, (ivm, e) => new { ivm.ItemValue.CleanValue, e.Type, e.Id })
+            .GroupBy(x => new { x.CleanValue, x.Type })
+            .Select(g => new { g.Key.CleanValue, g.Key.Type, Count = g.Select(x => x.Id).Distinct().Count() })
+            .ToArray();
 
-        return _queryHelpers.ApplyAccessFiltering(context, episodes, accessFilter).Count();
-    }
+        var byCleanName = grouped
+            .GroupBy(g => g.CleanValue, StringComparer.Ordinal)
+            .ToDictionary(g => g.Key, g => g.Select(x => (x.Type, x.Count)).ToArray(), StringComparer.Ordinal);
 
-    private ItemCounts BuildItemCounts(IEnumerable<(string Key, int Count)> counts)
-    {
-        var lookup = _itemTypeLookup.BaseItemKindNames;
-        var result = new ItemCounts();
-        var totalCount = 0;
+        var seriesTypeName = _itemTypeLookup.BaseItemKindNames[BaseItemKind.Series];
+        var episodeRollUp = RollsUpEpisodes(kind, relatedItemKinds)
+                && Array.Exists(grouped, g => string.Equals(g.Type, seriesTypeName, StringComparison.Ordinal))
+            ? CountEpisodesOfTaggedSeriesByCleanName(context, related, valueTypes, cleanNames)
+            : null;
 
-        foreach (var count in counts)
+        foreach (var nameItem in nameItems)
         {
-            totalCount += count.Count;
-
-            if (string.Equals(count.Key, lookup[BaseItemKind.MusicAlbum], StringComparison.Ordinal))
-            {
-                result.AlbumCount = count.Count;
-            }
-            else if (string.Equals(count.Key, lookup[BaseItemKind.MusicArtist], StringComparison.Ordinal))
-            {
-                result.ArtistCount = count.Count;
-            }
-            else if (string.Equals(count.Key, lookup[BaseItemKind.Episode], StringComparison.Ordinal))
-            {
-                result.EpisodeCount = count.Count;
-            }
-            else if (string.Equals(count.Key, lookup[BaseItemKind.Movie], StringComparison.Ordinal))
-            {
-                result.MovieCount = count.Count;
-            }
-            else if (string.Equals(count.Key, lookup[BaseItemKind.MusicVideo], StringComparison.Ordinal))
-            {
-                result.MusicVideoCount = count.Count;
-            }
-            else if (string.Equals(count.Key, lookup[BaseItemKind.LiveTvProgram], StringComparison.Ordinal))
-            {
-                result.ProgramCount = count.Count;
-            }
-            else if (string.Equals(count.Key, lookup[BaseItemKind.Series], StringComparison.Ordinal))
-            {
-                result.SeriesCount = count.Count;
-            }
-            else if (string.Equals(count.Key, lookup[BaseItemKind.Audio], StringComparison.Ordinal))
+            if (nameItem.CleanName is null || !byCleanName.TryGetValue(nameItem.CleanName, out var counts))
             {
-                result.SongCount = count.Count;
-            }
-            else if (string.Equals(count.Key, lookup[BaseItemKind.Trailer], StringComparison.Ordinal))
-            {
-                result.TrailerCount = count.Count;
-            }
-            else if (string.Equals(count.Key, lookup[BaseItemKind.BoxSet], StringComparison.Ordinal))
-            {
-                result.BoxSetCount = count.Count;
+                continue;
             }
-            else if (string.Equals(count.Key, lookup[BaseItemKind.Book], StringComparison.Ordinal))
+
+            var itemCounts = ItemCountBuilder.Build(_itemTypeLookup, counts);
+
+            if (episodeRollUp is not null)
             {
-                result.BookCount = count.Count;
-            }
-        }
+                var rollUp = episodeRollUp.GetValueOrDefault(nameItem.CleanName);
 
-        result.ItemCount = totalCount;
+                // Episodes of a tagged series count towards it even when untagged themselves, and
+                // a tagged episode of a tagged series must not be counted a second time.
+                var directEpisodeCount = itemCounts.EpisodeCount - rollUp.TaggedEpisodesOfTaggedSeries;
+                ItemCountBuilder.SetEpisodeCount(itemCounts, rollUp.EpisodesOfTaggedSeries + directEpisodeCount);
+            }
 
-        return result;
+            result[nameItem.Id] = itemCounts;
+        }
     }
 
-    private static ItemValueType[] GetItemValueTypes(BaseItemKind kind)
-        => kind switch
-        {
-            BaseItemKind.MusicArtist => [ItemValueType.Artist, ItemValueType.AlbumArtist],
-            BaseItemKind.Genre or BaseItemKind.MusicGenre => [ItemValueType.Genre],
-            BaseItemKind.Studio => [ItemValueType.Studios],
-            _ => []
-        };
-
-    /// <inheritdoc />
-    public Dictionary<Guid, ItemCounts> GetItemCountsForNameItems(BaseItemKind kind, IReadOnlyList<Guid> ids, BaseItemKind[] relatedItemKinds, InternalItemsQuery accessFilter)
+    private void CountByPersonName(
+        JellyfinDbContext context,
+        IQueryable<BaseItemEntity> related,
+        NameItem[] nameItems,
+        Dictionary<Guid, ItemCounts> result)
     {
-        ArgumentNullException.ThrowIfNull(ids);
-        ArgumentNullException.ThrowIfNull(relatedItemKinds);
+        var names = nameItems
+            .Select(n => n.Name)
+            .OfType<string>()
+            .Distinct(StringComparer.Ordinal)
+            .ToArray();
 
-        var result = new Dictionary<Guid, ItemCounts>();
-        if (ids.Count == 0)
+        if (names.Length == 0)
         {
-            return result;
+            return;
         }
 
-        // Only the kinds keyed by a cleaned item value can be grouped for every id in one query.
-        // Anything else, and the listing that rolls the episodes of a tagged series up into it,
-        // keeps the single item path, so a batch can never report a different number than it.
-        var valueTypes = GetItemValueTypes(kind);
-        var rollsUpEpisodes = kind is BaseItemKind.Studio or BaseItemKind.Genre or BaseItemKind.MusicGenre
-            && relatedItemKinds.Contains(BaseItemKind.Episode)
-            && relatedItemKinds.Contains(BaseItemKind.Series);
+        var grouped = context.PeopleBaseItemMap.AsNoTracking()
+            .WhereOneOrMany(names, m => m.People.Name)
+            .Join(related, m => m.ItemId, e => e.Id, (m, e) => new { m.People.Name, e.Type, e.Id })
+            .GroupBy(x => new { x.Name, x.Type })
+            // A person can be credited on one item more than once, in different roles.
+            .Select(g => new { g.Key.Name, g.Key.Type, Count = g.Select(x => x.Id).Distinct().Count() })
+            .ToArray();
+
+        ApplyGroupedCounts(nameItems, n => n.Name, grouped.Select(g => (g.Name, g.Type, g.Count)), result);
+    }
 
-        if (valueTypes.Length == 0 || rollsUpEpisodes)
+    private void CountByProductionYear(
+        IQueryable<BaseItemEntity> related,
+        NameItem[] nameItems,
+        Dictionary<Guid, ItemCounts> result)
+    {
+        var years = new List<int>();
+        foreach (var nameItem in nameItems)
         {
-            foreach (var id in ids)
+            if (int.TryParse(nameItem.Name, NumberStyles.Integer, CultureInfo.InvariantCulture, out var year)
+                && !years.Contains(year))
             {
-                result[id] = GetItemCountsForNameItem(kind, id, relatedItemKinds, accessFilter);
+                years.Add(year);
             }
-
-            return result;
         }
 
-        using var context = _dbProvider.CreateDbContext();
+        if (years.Count == 0)
+        {
+            return;
+        }
 
-        var nameItems = context.BaseItems.AsNoTracking()
-            .Where(e => ids.Contains(e.Id))
-            .Select(e => new { e.Id, e.CleanName })
+        // No join, so no row can be reached twice and a plain count is the distinct count.
+        var grouped = related
+            .Where(e => e.ProductionYear != null)
+            .WhereOneOrMany(years, e => e.ProductionYear!.Value)
+            .GroupBy(e => new { Year = e.ProductionYear!.Value, e.Type })
+            .Select(g => new { g.Key.Year, g.Key.Type, Count = g.Count() })
             .ToArray();
 
-        foreach (var id in ids)
+        var byYear = grouped
+            .GroupBy(g => g.Year)
+            .ToDictionary(g => g.Key, g => g.Select(x => (x.Type, x.Count)).ToArray());
+
+        foreach (var nameItem in nameItems)
         {
-            result[id] = new ItemCounts();
+            if (int.TryParse(nameItem.Name, NumberStyles.Integer, CultureInfo.InvariantCulture, out var year)
+                && byYear.TryGetValue(year, out var counts))
+            {
+                result[nameItem.Id] = ItemCountBuilder.Build(_itemTypeLookup, counts);
+            }
         }
+    }
 
-        var cleanNames = nameItems
-            .Select(n => n.CleanName)
-            .Where(n => n is not null)
-            .Distinct(StringComparer.Ordinal)
-            .ToArray();
+    private void ApplyGroupedCounts(
+        NameItem[] nameItems,
+        Func<NameItem, string?> keySelector,
+        IEnumerable<(string Key, string Type, int Count)> grouped,
+        Dictionary<Guid, ItemCounts> result)
+    {
+        var byKey = grouped
+            .GroupBy(g => g.Key, StringComparer.Ordinal)
+            .ToDictionary(g => g.Key, g => g.Select(x => (x.Type, x.Count)).ToArray(), StringComparer.Ordinal);
 
-        if (cleanNames.Length == 0)
+        foreach (var nameItem in nameItems)
         {
-            return result;
+            var key = keySelector(nameItem);
+            if (key is not null && byKey.TryGetValue(key, out var counts))
+            {
+                result[nameItem.Id] = ItemCountBuilder.Build(_itemTypeLookup, counts);
+            }
         }
+    }
 
-        var typeNames = relatedItemKinds.Select(k => _itemTypeLookup.BaseItemKindNames[k]).ToArray();
+    private static bool RollsUpEpisodes(BaseItemKind kind, BaseItemKind[] relatedItemKinds)
+        => kind is BaseItemKind.Studio or BaseItemKind.Genre or BaseItemKind.MusicGenre
+            && relatedItemKinds.Contains(BaseItemKind.Episode)
+            && relatedItemKinds.Contains(BaseItemKind.Series);
 
-        var related = _queryHelpers.ApplyAccessFiltering(
-            context,
-            context.BaseItems.AsNoTracking().Where(e => typeNames.Contains(e.Type)),
-            accessFilter);
+    private Dictionary<string, (int EpisodesOfTaggedSeries, int TaggedEpisodesOfTaggedSeries)> CountEpisodesOfTaggedSeriesByCleanName(
+        JellyfinDbContext context,
+        IQueryable<BaseItemEntity> related,
+        ItemValueType[] valueTypes,
+        string[] cleanNames)
+    {
+        var seriesTypeName = _itemTypeLookup.BaseItemKindNames[BaseItemKind.Series];
+        var episodeTypeName = _itemTypeLookup.BaseItemKindNames[BaseItemKind.Episode];
 
-        var grouped = context.ItemValuesMap.AsNoTracking()
-            .Where(ivm => valueTypes.Contains(ivm.ItemValue.Type) && cleanNames.Contains(ivm.ItemValue.CleanValue))
-            .Join(related, ivm => ivm.ItemId, e => e.Id, (ivm, e) => new { ivm.ItemValue.CleanValue, e.Type })
-            .GroupBy(x => new { x.CleanValue, x.Type })
-            .Select(g => new { g.Key.CleanValue, g.Key.Type, Count = g.Count() })
+        var taggedValues = context.ItemValuesMap.AsNoTracking()
+            .Where(ivm => valueTypes.Contains(ivm.ItemValue.Type))
+            .WhereOneOrMany(cleanNames, ivm => ivm.ItemValue.CleanValue);
+
+        // The series carrying each clean name. Distinct, because one item can be mapped to the
+        // same clean name once per value type.
+        var taggedSeries = taggedValues
+            .Join(
+                related.Where(e => e.Type == seriesTypeName),
+                ivm => ivm.ItemId,
+                e => e.Id,
+                (ivm, e) => new { ivm.ItemValue.CleanValue, SeriesId = e.Id })
+            .Distinct();
+
+        var episodes = related.Where(e => e.Type == episodeTypeName && e.SeriesId != null);
+
+        var episodesOfTaggedSeries = taggedSeries
+            .Join(episodes, s => s.SeriesId, e => e.SeriesId!.Value, (s, e) => new { s.CleanValue, e.Id })
+            .GroupBy(x => x.CleanValue)
+            .Select(g => new { CleanValue = g.Key, Count = g.Select(x => x.Id).Distinct().Count() })
             .ToArray();
 
-        var byCleanName = grouped
-            .GroupBy(g => g.CleanValue, StringComparer.Ordinal)
-            .ToDictionary(g => g.Key, g => g.Select(x => (x.Type, x.Count)).ToArray(), StringComparer.Ordinal);
+        // Episodes that carry the clean name themselves *and* belong to a series carrying it. The
+        // roll-up already counts those, so they have to come off the directly tagged ones.
+        var taggedEpisodesOfTaggedSeries = taggedValues
+            .Join(episodes, ivm => ivm.ItemId, e => e.Id, (ivm, e) => new { ivm.ItemValue.CleanValue, e.Id, e.SeriesId })
+            .Join(
+                taggedSeries,
+                e => new { e.CleanValue, SeriesId = e.SeriesId!.Value },
+                s => new { s.CleanValue, s.SeriesId },
+                (e, s) => new { e.CleanValue, e.Id })
+            .GroupBy(x => x.CleanValue)
+            .Select(g => new { CleanValue = g.Key, Count = g.Select(x => x.Id).Distinct().Count() })
+            .ToArray();
 
-        foreach (var nameItem in nameItems)
+        var taggedLookup = taggedEpisodesOfTaggedSeries
+            .ToDictionary(x => x.CleanValue, x => x.Count, StringComparer.Ordinal);
+
+        // Every clean name in taggedLookup came from an episode of a tagged series, so it always
+        // has a row in episodesOfTaggedSeries too - no second merge pass is needed.
+        var result = new Dictionary<string, (int EpisodesOfTaggedSeries, int TaggedEpisodesOfTaggedSeries)>(StringComparer.Ordinal);
+        foreach (var entry in episodesOfTaggedSeries)
         {
-            if (nameItem.CleanName is not null && byCleanName.TryGetValue(nameItem.CleanName, out var counts))
-            {
-                result[nameItem.Id] = BuildItemCounts(counts);
-            }
+            result[entry.CleanValue] = (entry.Count, taggedLookup.GetValueOrDefault(entry.CleanValue));
         }
 
         return result;
     }
 
-    private static IQueryable<BaseItemEntity> ItemsById(JellyfinDbContext context, IQueryable<Guid> itemIds)
-        => context.BaseItems.AsNoTracking().Where(e => itemIds.Contains(e.Id));
-
     /// <inheritdoc/>
     public int GetPlayedCount(InternalItemsQuery filter, Guid ancestorId)
     {
@@ -724,4 +731,12 @@ public class ItemCountService : IItemCountService
 
         return result is null ? (0, 0) : (result.Played, result.Total);
     }
+
+    /// <summary>
+    /// A by-name item, reduced to the three columns the counting keys off.
+    /// </summary>
+    /// <param name="Id">The id of the by-name item.</param>
+    /// <param name="Name">The name of the by-name item.</param>
+    /// <param name="CleanName">The cleaned name of the by-name item.</param>
+    private sealed record NameItem(Guid Id, string? Name, string? CleanName);
 }