avoid correlated item value name queries

2 files changed, 54 insertions, 8 deletions

diff --git a/Jellyfin.Server.Implementations/Item/BaseItemRepository.ByName.cs b/Jellyfin.Server.Implementations/Item/BaseItemRepository.ByName.cs
index 2ff8131ade..70e4ca3b1d 100644
--- a/Jellyfin.Server.Implementations/Item/BaseItemRepository.ByName.cs
+++ b/Jellyfin.Server.Implementations/Item/BaseItemRepository.ByName.cs
@@ -121,20 +121,22 @@ public sealed partial class BaseItemRepository
     {
         using var context = _dbProvider.CreateDbContext();
 
-        var query = context.ItemValuesMap
-            .AsNoTracking()
-            .Where(e => itemValueTypes.Any(w => w == e.ItemValue.Type));
+        var maps = context.ItemValuesMap.AsNoTracking();
         if (withItemTypes.Count > 0)
         {
-            query = query.Where(e => withItemTypes.Contains(e.Item.Type));
+            maps = maps.Where(e => withItemTypes.Contains(e.Item.Type));
         }
 
         if (excludeItemTypes.Count > 0)
         {
-            query = query.Where(e => !excludeItemTypes.Contains(e.Item.Type));
+            maps = maps.Where(e => !excludeItemTypes.Contains(e.Item.Type));
         }
 
-        return query.Select(e => e.ItemValue)
+        return context.ItemValues
+            .AsNoTracking()
+            .WhereOneOrMany(itemValueTypes, e => e.Type)
+            .Where(e => maps.Any(m => m.ItemValueId == e.ItemValueId))
+            .Select(e => new { e.CleanValue, e.Value })
             .GroupBy(e => e.CleanValue)
             .Select(g => g.Min(v => v.Value)!)
             .ToArray();
diff --git a/tests/Jellyfin.Server.Implementations.Tests/Item/BaseItemRepositoryLegacyFilterTests.cs b/tests/Jellyfin.Server.Implementations.Tests/Item/BaseItemRepositoryItemValueTests.cs
index 12b85cb021..91148501ce 100644
--- a/tests/Jellyfin.Server.Implementations.Tests/Item/BaseItemRepositoryLegacyFilterTests.cs
+++ b/tests/Jellyfin.Server.Implementations.Tests/Item/BaseItemRepositoryItemValueTests.cs
@@ -8,13 +8,13 @@ using BaseItemKind = Jellyfin.Data.Enums.BaseItemKind;
 
 namespace Jellyfin.Server.Implementations.Tests.Item;
 
-public sealed class BaseItemRepositoryLegacyFilterTests : SqliteDbTestFixture
+public sealed class BaseItemRepositoryItemValueTests : SqliteDbTestFixture
 {
     private readonly BaseItemRepository _repository;
     private readonly string _audioTypeName;
     private readonly string _movieTypeName;
 
-    public BaseItemRepositoryLegacyFilterTests()
+    public BaseItemRepositoryItemValueTests()
     {
         var itemTypeLookup = new ItemTypeLookup();
         _audioTypeName = itemTypeLookup.BaseItemKindNames[BaseItemKind.Audio];
@@ -103,6 +103,39 @@ public sealed class BaseItemRepositoryLegacyFilterTests : SqliteDbTestFixture
         Assert.Equal(["Genre Leak"], result.Genres);
     }
 
+    [Fact]
+    public void GetGenreNames_GroupsAndFiltersMappedItemValues()
+    {
+        var movie = CreateMovieEntity(Guid.NewGuid(), "Movie");
+        var audio = new BaseItemEntity
+        {
+            Id = Guid.NewGuid(),
+            Type = _audioTypeName,
+            Name = "Audio",
+            MediaType = "Audio",
+            IsFolder = false,
+            IsVirtualItem = false
+        };
+        var movieGenre = CreateItemValue(ItemValueType.Genre, "Movie Genre", "movie genre");
+        var duplicateMovieGenre = CreateItemValue(ItemValueType.Genre, "movie genre", "movie genre");
+        var musicGenre = CreateItemValue(ItemValueType.Genre, "Music Genre", "music genre");
+        var orphanedGenre = CreateItemValue(ItemValueType.Genre, "Orphaned Genre", "orphaned genre");
+
+        using (var context = CreateDbContext())
+        {
+            context.BaseItems.AddRange(movie, audio);
+            context.ItemValues.AddRange(movieGenre, duplicateMovieGenre, musicGenre, orphanedGenre);
+            context.ItemValuesMap.AddRange(
+                CreateMap(movie, movieGenre),
+                CreateMap(movie, duplicateMovieGenre),
+                CreateMap(audio, musicGenre));
+            context.SaveChanges();
+        }
+
+        Assert.Equal(["Movie Genre"], _repository.GetGenreNames());
+        Assert.Equal(["Music Genre"], _repository.GetMusicGenreNames());
+    }
+
     private BaseItemEntity CreateMovieEntity(Guid id, string name)
     {
         return new BaseItemEntity
@@ -127,4 +160,15 @@ public sealed class BaseItemRepositoryLegacyFilterTests : SqliteDbTestFixture
             ItemValue = itemValue
         };
     }
+
+    private static ItemValue CreateItemValue(ItemValueType type, string value, string cleanValue)
+    {
+        return new ItemValue
+        {
+            ItemValueId = Guid.NewGuid(),
+            Type = type,
+            Value = value,
+            CleanValue = cleanValue
+        };
+    }
 }