Fix GetBaseItemDto to return related item counts via SQL count

For API call /Items/{item id} GetBaseItemDto will return the counts of related items e.g. artists, albums, songs.  GetBaseItemDto currently does this by calling GetTaggedItems which retrieves the objects into memory to count them.  Replace with SQL count.

Fixes:
This should be an improvement for any large libraries, but especially large music libraries.  Example:

Request Library -> Genres -> any very popular genre in your large library, e.g. Classical
Number of albums = 1552, songs = 23515, ...

- Before change: Try to retrieve 1552 albums, 23515 songs, ... in memory, API never returns, database on fire
- After change: API returns in 367ms and Genre view opens with 200 albums in 2 seconds

I verified the numbers returned are correct but note that there is a bug somewhere else in Jellyfin that is setting TopParentId to NULL for a large portion of my MusicArtists, which causes them to not be counted by the existing GetCount().  This is not related to this change, also happens with the existing code, and does not seem to affect the Web UI.

Includes Cory's changes in:
- https://github.com/jellyfin/jellyfin/pull/14610#issuecomment-3172211468
- https://github.com/jellyfin/jellyfin/pull/14610#issuecomment-3172239154

1 files changed, 35 insertions, 38 deletions

diff --git a/Emby.Server.Implementations/Dto/DtoService.cs b/Emby.Server.Implementations/Dto/DtoService.cs
index cf886ae82..adeb889b7 100644
--- a/Emby.Server.Implementations/Dto/DtoService.cs
+++ b/Emby.Server.Implementations/Dto/DtoService.cs
@@ -102,21 +102,9 @@ namespace Emby.Server.Implementations.Dto
                     (programTuples ??= []).Add((item, dto));
                 }
 
-                if (item is IItemByName byName)
+                if (item is IItemByName itemByName && options.ContainsField(ItemFields.ItemCounts))
                 {
-                    if (options.ContainsField(ItemFields.ItemCounts))
-                    {
-                        var libraryItems = byName.GetTaggedItems(new InternalItemsQuery(user)
-                        {
-                            Recursive = true,
-                            DtoOptions = new DtoOptions(false)
-                            {
-                                EnableImages = false
-                            }
-                        });
-
-                        SetItemByNameInfo(item, dto, libraryItems);
-                    }
+                    SetItemByNameInfo(itemByName, dto, user);
                 }
 
                 returnItems[index] = dto;
@@ -147,34 +135,14 @@ namespace Emby.Server.Implementations.Dto
                 LivetvManager.AddInfoToProgramDto(new[] { (item, dto) }, options.Fields, user).GetAwaiter().GetResult();
             }
 
-            if (item is IItemByName itemByName
-                && options.ContainsField(ItemFields.ItemCounts))
+            if (item is IItemByName itemByName && options.ContainsField(ItemFields.ItemCounts))
             {
-                SetItemByNameInfo(
-                    item,
-                    dto,
-                    GetTaggedItems(
-                        itemByName,
-                        user,
-                        new DtoOptions(false)
-                        {
-                            EnableImages = false
-                        }));
+                SetItemByNameInfo(itemByName, dto, user);
             }
 
             return dto;
         }
 
-        private static IReadOnlyList<BaseItem> GetTaggedItems(IItemByName byName, User? user, DtoOptions options)
-        {
-            return byName.GetTaggedItems(
-                new InternalItemsQuery(user)
-                {
-                    Recursive = true,
-                    DtoOptions = options
-                });
-        }
-
         private BaseItemDto GetBaseItemDtoInternal(BaseItem item, DtoOptions options, User? user = null, BaseItem? owner = null)
         {
             var dto = new BaseItemDto
@@ -319,14 +287,43 @@ namespace Emby.Server.Implementations.Dto
         {
             var dto = GetBaseItemDtoInternal(item, options, user);
 
-            if (taggedItems is not null && options.ContainsField(ItemFields.ItemCounts))
+            if (options.ContainsField(ItemFields.ItemCounts))
             {
-                SetItemByNameInfo(item, dto, taggedItems);
+                if (taggedItems is not null)
+                {
+                    SetItemByNameInfo(item, dto, taggedItems!);
+                }
+                else if (item is IItemByName itemByName)
+                {
+                    SetItemByNameInfo(itemByName, dto, user);
+                }
             }
 
             return dto;
         }
 
+        private static void SetItemByNameInfo(IItemByName item, BaseItemDto dto, User? user)
+        {
+            var query = new InternalItemsQuery(user)
+            {
+                Recursive = true,
+                DtoOptions = new DtoOptions(false) { EnableImages = false }
+            };
+
+            var counts = item.GetTaggedItemCounts(query);
+
+            dto.AlbumCount = counts.AlbumCount;
+            dto.ArtistCount = counts.ArtistCount;
+            dto.EpisodeCount = counts.EpisodeCount;
+            dto.MovieCount = counts.MovieCount;
+            dto.MusicVideoCount = counts.MusicVideoCount;
+            dto.ProgramCount = counts.ProgramCount;
+            dto.SeriesCount = counts.SeriesCount;
+            dto.SongCount = counts.SongCount;
+            dto.TrailerCount = counts.TrailerCount;
+            dto.ChildCount = counts.ChildCount;
+        }
+
         private static void SetItemByNameInfo(BaseItem item, BaseItemDto dto, IReadOnlyList<BaseItem> taggedItems)
         {
             if (item is MusicArtist)