Batch alternate version detection in DtoService to remove MediaSourceCount N+1

Browsing a page of videos with the MediaSourceCount field ran one alternate
version query per item, each opening a fresh DbContext. On a large library that
turned a single page into hundreds of sequential round trips and made the Items
endpoint take tens of seconds while holding a request thread the whole time.

Detect which videos own alternate versions once per page with a single query,
mirroring the existing people batch. Videos absent from that set have a single
media source, so the per item lookups are skipped for the common case. Behavior
is unchanged: a video with no alternates already resolved to a count of one.

Adds a regression test asserting the count resolves from the batch and the per
item lookups are never called.

6 files changed, 123 insertions, 12 deletions

diff --git a/Emby.Server.Implementations/Dto/DtoService.cs b/Emby.Server.Implementations/Dto/DtoService.cs
index da0c52df5b..062c19a1d4 100644
--- a/Emby.Server.Implementations/Dto/DtoService.cs
+++ b/Emby.Server.Implementations/Dto/DtoService.cs
@@ -253,6 +253,18 @@ namespace Emby.Server.Implementations.Dto
                 }
             }
 
+            // Batch-detect which videos own alternate versions to avoid the per-item alternate-version
+            // queries in MediaSourceCount. Videos absent from this set have a single media source.
+            IReadOnlySet<Guid>? alternateVersionItemIds = null;
+            if (options.ContainsField(ItemFields.MediaSourceCount))
+            {
+                var versionItemIds = accessibleItems.OfType<Video>().Select(i => i.Id).ToList();
+                if (versionItemIds.Count > 0)
+                {
+                    alternateVersionItemIds = _libraryManager.GetItemsWithAlternateVersions(versionItemIds);
+                }
+            }
+
             for (int index = 0; index < accessibleItems.Count; index++)
             {
                 var item = accessibleItems[index];
@@ -267,7 +279,8 @@ namespace Emby.Server.Implementations.Dto
                     playedCountBatch,
                     artistsBatch,
                     resumeDataBatch?.GetValueOrDefault(item.Id),
-                    peopleBatch);
+                    peopleBatch,
+                    alternateVersionItemIds);
 
                 if (item is LiveTvChannel tvChannel)
                 {
@@ -330,7 +343,8 @@ namespace Emby.Server.Implementations.Dto
             Dictionary<Guid, (int Played, int Total)>? playedCountBatch = null,
             IReadOnlyDictionary<string, MusicArtist[]>? artistsBatch = null,
             VersionResumeData? resumeData = null,
-            IReadOnlyDictionary<Guid, IReadOnlyList<PersonInfo>>? peopleBatch = null)
+            IReadOnlyDictionary<Guid, IReadOnlyList<PersonInfo>>? peopleBatch = null,
+            IReadOnlySet<Guid>? alternateVersionItemIds = null)
         {
             var dto = new BaseItemDto
             {
@@ -399,7 +413,7 @@ namespace Emby.Server.Implementations.Dto
                 AttachStudios(dto, item);
             }
 
-            AttachBasicFields(dto, item, owner, options, artistsBatch, user);
+            AttachBasicFields(dto, item, owner, options, artistsBatch, user, alternateVersionItemIds);
 
             if (options.ContainsField(ItemFields.CanDelete))
             {
@@ -984,7 +998,8 @@ namespace Emby.Server.Implementations.Dto
         /// <param name="options">The options.</param>
         /// <param name="artistsBatch">Optional pre-fetched artist lookup shared across a batch of items.</param>
         /// <param name="user">The user, for per-user values such as the accessible media source count.</param>
-        private void AttachBasicFields(BaseItemDto dto, BaseItem item, BaseItem? owner, DtoOptions options, IReadOnlyDictionary<string, MusicArtist[]>? artistsBatch = null, User? user = null)
+        /// <param name="alternateVersionItemIds">Optional pre-fetched set of item IDs that own alternate versions, shared across a batch of items.</param>
+        private void AttachBasicFields(BaseItemDto dto, BaseItem item, BaseItem? owner, DtoOptions options, IReadOnlyDictionary<string, MusicArtist[]>? artistsBatch = null, User? user = null, IReadOnlySet<Guid>? alternateVersionItemIds = null)
         {
             if (options.ContainsField(ItemFields.DateCreated))
             {
@@ -1298,15 +1313,25 @@ namespace Emby.Server.Implementations.Dto
 
                 if (options.ContainsField(ItemFields.MediaSourceCount))
                 {
-                    // Match the per-user filtering of the media sources: versions the user cannot
-                    // access are not selectable, so they must not count towards the badge either.
-                    var mediaSourceCount = user is null
-                        || (!video.PrimaryVersionId.HasValue && video.LinkedAlternateVersions.Length == 0 && !video.HasLocalAlternateVersions)
-                            ? video.MediaSourceCount
-                            : video.GetAllVersions().Count(v => v.Id.Equals(video.Id) || v.IsVisibleStandalone(user));
-                    if (mediaSourceCount != 1)
+                    // A video with no primary version and no alternate versions always has a single
+                    // media source. When the batch has already determined this item owns no alternate
+                    // versions, skip the per-item alternate-version queries entirely (the common case).
+                    var hasNoAlternateVersions = alternateVersionItemIds is not null
+                        && !video.PrimaryVersionId.HasValue
+                        && !alternateVersionItemIds.Contains(video.Id);
+
+                    if (!hasNoAlternateVersions)
                     {
-                        dto.MediaSourceCount = mediaSourceCount;
+                        // Match the per-user filtering of the media sources: versions the user cannot
+                        // access are not selectable, so they must not count towards the badge either.
+                        var mediaSourceCount = user is null
+                            || (!video.PrimaryVersionId.HasValue && video.LinkedAlternateVersions.Length == 0 && !video.HasLocalAlternateVersions)
+                                ? video.MediaSourceCount
+                                : video.GetAllVersions().Count(v => v.Id.Equals(video.Id) || v.IsVisibleStandalone(user));
+                        if (mediaSourceCount != 1)
+                        {
+                            dto.MediaSourceCount = mediaSourceCount;
+                        }
                     }
                 }
 
diff --git a/Emby.Server.Implementations/Library/LibraryManager.cs b/Emby.Server.Implementations/Library/LibraryManager.cs
index 19371f68d7..9bb962c504 100644
--- a/Emby.Server.Implementations/Library/LibraryManager.cs
+++ b/Emby.Server.Implementations/Library/LibraryManager.cs
@@ -2235,6 +2235,12 @@ namespace Emby.Server.Implementations.Library
         }
 
         /// <inheritdoc />
+        public IReadOnlySet<Guid> GetItemsWithAlternateVersions(IReadOnlyList<Guid> itemIds)
+        {
+            return _linkedChildrenService.GetItemsWithAlternateVersions(itemIds);
+        }
+
+        /// <inheritdoc />
         public void UpsertLinkedChild(Guid parentId, Guid childId, MediaBrowser.Controller.Entities.LinkedChildType childType)
         {
             _linkedChildrenService.UpsertLinkedChild(parentId, childId, childType);
diff --git a/Jellyfin.Server.Implementations/Item/LinkedChildrenService.cs b/Jellyfin.Server.Implementations/Item/LinkedChildrenService.cs
index 5f1d9bf87a..2452f8e3c6 100644
--- a/Jellyfin.Server.Implementations/Item/LinkedChildrenService.cs
+++ b/Jellyfin.Server.Implementations/Item/LinkedChildrenService.cs
@@ -60,6 +60,27 @@ public class LinkedChildrenService : ILinkedChildrenService
     }
 
     /// <inheritdoc/>
+    public IReadOnlySet<Guid> GetItemsWithAlternateVersions(IReadOnlyList<Guid> itemIds)
+    {
+        if (itemIds.Count == 0)
+        {
+            return new HashSet<Guid>();
+        }
+
+        using var dbContext = _dbProvider.CreateDbContext();
+
+        var parentIds = dbContext.LinkedChildren
+            .Where(lc => (lc.ChildType == DbLinkedChildType.LocalAlternateVersion
+                    || lc.ChildType == DbLinkedChildType.LinkedAlternateVersion)
+                && itemIds.Contains(lc.ParentId))
+            .Select(lc => lc.ParentId)
+            .Distinct()
+            .ToArray();
+
+        return parentIds.ToHashSet();
+    }
+
+    /// <inheritdoc/>
     public IReadOnlyDictionary<string, MusicArtist[]> FindArtists(IReadOnlyList<string> artistNames)
     {
         using var dbContext = _dbProvider.CreateDbContext();
diff --git a/MediaBrowser.Controller/Library/ILibraryManager.cs b/MediaBrowser.Controller/Library/ILibraryManager.cs
index 5eae6e103f..f6bd948b99 100644
--- a/MediaBrowser.Controller/Library/ILibraryManager.cs
+++ b/MediaBrowser.Controller/Library/ILibraryManager.cs
@@ -256,6 +256,14 @@ namespace MediaBrowser.Controller.Library
         IEnumerable<Video> GetLinkedAlternateVersions(Video video);
 
         /// <summary>
+        /// Gets, in a single query, the subset of the supplied items that own at least one alternate
+        /// version (local or linked). Items absent from the result have no alternate versions.
+        /// </summary>
+        /// <param name="itemIds">The item IDs to check.</param>
+        /// <returns>The set of item IDs that have alternate versions.</returns>
+        IReadOnlySet<Guid> GetItemsWithAlternateVersions(IReadOnlyList<Guid> itemIds);
+
+        /// <summary>
         /// Creates or updates a LinkedChild entry linking a parent to a child item.
         /// </summary>
         /// <param name="parentId">The parent item ID.</param>
diff --git a/MediaBrowser.Controller/Persistence/ILinkedChildrenService.cs b/MediaBrowser.Controller/Persistence/ILinkedChildrenService.cs
index a4614fc125..c1fe3231f4 100644
--- a/MediaBrowser.Controller/Persistence/ILinkedChildrenService.cs
+++ b/MediaBrowser.Controller/Persistence/ILinkedChildrenService.cs
@@ -20,6 +20,15 @@ public interface ILinkedChildrenService
     IReadOnlyList<Guid> GetLinkedChildrenIds(Guid parentId, int? childType = null);
 
     /// <summary>
+    /// Gets, in a single query, the subset of the supplied items that own at least one alternate
+    /// version (local or linked). Items absent from the result have no alternate versions, so their
+    /// media source count is one.
+    /// </summary>
+    /// <param name="itemIds">The item IDs to check.</param>
+    /// <returns>The set of item IDs that have alternate versions.</returns>
+    IReadOnlySet<Guid> GetItemsWithAlternateVersions(IReadOnlyList<Guid> itemIds);
+
+    /// <summary>
     /// Gets all artist matches from the database.
     /// </summary>
     /// <param name="artistNames">The names of the artists.</param>
diff --git a/tests/Jellyfin.Server.Implementations.Tests/Dto/DtoServiceImageInheritanceTests.cs b/tests/Jellyfin.Server.Implementations.Tests/Dto/DtoServiceImageInheritanceTests.cs
index d18f8c6cff..fa94250287 100644
--- a/tests/Jellyfin.Server.Implementations.Tests/Dto/DtoServiceImageInheritanceTests.cs
+++ b/tests/Jellyfin.Server.Implementations.Tests/Dto/DtoServiceImageInheritanceTests.cs
@@ -9,6 +9,7 @@ using MediaBrowser.Controller.Drawing;
 using MediaBrowser.Controller.Dto;
 using MediaBrowser.Controller.Entities;
 using MediaBrowser.Controller.Entities.Audio;
+using MediaBrowser.Controller.Entities.Movies;
 using MediaBrowser.Controller.Library;
 using MediaBrowser.Controller.LiveTv;
 using MediaBrowser.Controller.Providers;
@@ -205,6 +206,43 @@ public class DtoServiceImageInheritanceTests
         libraryManager.Verify(x => x.GetPeople(It.IsAny<BaseItem>()), Times.Never);
     }
 
+    [Fact]
+    public void GetBaseItemDtos_Videos_ResolveMediaSourceCountFromBatch_WithoutPerItemLookup()
+    {
+        static Movie MakeMovie() => new Movie
+        {
+            Id = Guid.NewGuid(),
+            Name = "Movie",
+            ImageInfos = []
+        };
+
+        var movieOne = MakeMovie();
+        var movieTwo = MakeMovie();
+
+        var libraryManager = new Mock<ILibraryManager>();
+
+        // DtoService detects which videos own alternate versions in ONE batch
+        // (GetItemsWithAlternateVersions) before the per-item loop. Videos absent from that set have a
+        // single media source, so the per-item GetLinkedAlternateVersions/GetLocalAlternateVersionIds
+        // queries (the N+1) must be skipped entirely. Here neither movie has alternate versions.
+        libraryManager
+            .Setup(x => x.GetItemsWithAlternateVersions(It.IsAny<IReadOnlyList<Guid>>()))
+            .Returns(new HashSet<Guid>());
+
+        var dtoService = BuildDtoService(libraryManager);
+
+        var options = new DtoOptions(false) { Fields = [ItemFields.MediaSourceCount] };
+        var dtos = dtoService.GetBaseItemDtos([movieOne, movieTwo], options);
+
+        Assert.Equal(2, dtos.Count);
+
+        // The alternate-version check is batched once for the whole set, and the per-item lookups are
+        // never reached because the batch already ruled out alternate versions.
+        libraryManager.Verify(x => x.GetItemsWithAlternateVersions(It.IsAny<IReadOnlyList<Guid>>()), Times.Once);
+        libraryManager.Verify(x => x.GetLinkedAlternateVersions(It.IsAny<Video>()), Times.Never);
+        libraryManager.Verify(x => x.GetLocalAlternateVersionIds(It.IsAny<Video>()), Times.Never);
+    }
+
     private static DtoService BuildDtoService(BaseItem displayParent)
     {
         var libraryManager = new Mock<ILibraryManager>();
@@ -231,6 +269,10 @@ public class DtoServiceImageInheritanceTests
             .Setup(x => x.GetImageCacheTag(It.IsAny<BaseItem>(), It.IsAny<ItemImageInfo>()))
             .Returns<BaseItem, ItemImageInfo>((_, image) => image.Path);
 
+        // Video.IsActiveRecording() dereferences this static during DTO building.
+        Video.RecordingsManager = recordingsManager.Object;
+        BaseItem.LibraryManager = libraryManager.Object;
+
         return new DtoService(
             logger.Object,
             libraryManager.Object,