Fix ItemValue query (#13939)

2 files changed, 323 insertions, 101 deletions

diff --git a/Jellyfin.Server.Implementations/Item/BaseItemRepository.cs b/Jellyfin.Server.Implementations/Item/BaseItemRepository.cs
index e7f82389d..7d30ac1a0 100644
--- a/Jellyfin.Server.Implementations/Item/BaseItemRepository.cs
+++ b/Jellyfin.Server.Implementations/Item/BaseItemRepository.cs
@@ -148,37 +148,37 @@ public sealed class BaseItemRepository
     }
 
     /// <inheritdoc />
-    public QueryResult<(BaseItemDto Item, ItemCounts ItemCounts)> GetAllArtists(InternalItemsQuery filter)
+    public QueryResult<(BaseItemDto Item, ItemCounts? ItemCounts)> GetAllArtists(InternalItemsQuery filter)
     {
         return GetItemValues(filter, _getAllArtistsValueTypes, _itemTypeLookup.BaseItemKindNames[BaseItemKind.MusicArtist]);
     }
 
     /// <inheritdoc />
-    public QueryResult<(BaseItemDto Item, ItemCounts ItemCounts)> GetArtists(InternalItemsQuery filter)
+    public QueryResult<(BaseItemDto Item, ItemCounts? ItemCounts)> GetArtists(InternalItemsQuery filter)
     {
         return GetItemValues(filter, _getArtistValueTypes, _itemTypeLookup.BaseItemKindNames[BaseItemKind.MusicArtist]);
     }
 
     /// <inheritdoc />
-    public QueryResult<(BaseItemDto Item, ItemCounts ItemCounts)> GetAlbumArtists(InternalItemsQuery filter)
+    public QueryResult<(BaseItemDto Item, ItemCounts? ItemCounts)> GetAlbumArtists(InternalItemsQuery filter)
     {
         return GetItemValues(filter, _getAlbumArtistValueTypes, _itemTypeLookup.BaseItemKindNames[BaseItemKind.MusicArtist]);
     }
 
     /// <inheritdoc />
-    public QueryResult<(BaseItemDto Item, ItemCounts ItemCounts)> GetStudios(InternalItemsQuery filter)
+    public QueryResult<(BaseItemDto Item, ItemCounts? ItemCounts)> GetStudios(InternalItemsQuery filter)
     {
         return GetItemValues(filter, _getStudiosValueTypes, _itemTypeLookup.BaseItemKindNames[BaseItemKind.Studio]);
     }
 
     /// <inheritdoc />
-    public QueryResult<(BaseItemDto Item, ItemCounts ItemCounts)> GetGenres(InternalItemsQuery filter)
+    public QueryResult<(BaseItemDto Item, ItemCounts? ItemCounts)> GetGenres(InternalItemsQuery filter)
     {
         return GetItemValues(filter, _getGenreValueTypes, _itemTypeLookup.BaseItemKindNames[BaseItemKind.Genre]);
     }
 
     /// <inheritdoc />
-    public QueryResult<(BaseItemDto Item, ItemCounts ItemCounts)> GetMusicGenres(InternalItemsQuery filter)
+    public QueryResult<(BaseItemDto Item, ItemCounts? ItemCounts)> GetMusicGenres(InternalItemsQuery filter)
     {
         return GetItemValues(filter, _getGenreValueTypes, _itemTypeLookup.BaseItemKindNames[BaseItemKind.MusicGenre]);
     }
@@ -402,7 +402,8 @@ public sealed class BaseItemRepository
 
     private IQueryable<BaseItemEntity> PrepareItemQuery(JellyfinDbContext context, InternalItemsQuery filter)
     {
-        IQueryable<BaseItemEntity> dbQuery = context.BaseItems.AsNoTracking().AsSplitQuery()
+        IQueryable<BaseItemEntity> dbQuery = context.BaseItems.AsNoTracking();
+        dbQuery = dbQuery.AsSingleQuery()
             .Include(e => e.TrailerTypes)
             .Include(e => e.Provider)
             .Include(e => e.LockedFields);
@@ -1037,7 +1038,7 @@ public sealed class BaseItemRepository
         return Map(baseItemEntity, dto, appHost);
     }
 
-    private QueryResult<(BaseItemDto Item, ItemCounts ItemCounts)> GetItemValues(InternalItemsQuery filter, IReadOnlyList<ItemValueType> itemValueTypes, string returnType)
+    private QueryResult<(BaseItemDto Item, ItemCounts? ItemCounts)> GetItemValues(InternalItemsQuery filter, IReadOnlyList<ItemValueType> itemValueTypes, string returnType)
     {
         ArgumentNullException.ThrowIfNull(filter);
 
@@ -1048,20 +1049,59 @@ public sealed class BaseItemRepository
 
         using var context = _dbProvider.CreateDbContext();
 
-        var query = TranslateQuery(context.BaseItems.AsNoTracking(), context, filter);
+        var innerQueryFilter = TranslateQuery(context.BaseItems, context, new InternalItemsQuery(filter.User)
+        {
+            ExcludeItemTypes = filter.ExcludeItemTypes,
+            IncludeItemTypes = filter.IncludeItemTypes,
+            MediaTypes = filter.MediaTypes,
+            AncestorIds = filter.AncestorIds,
+            ItemIds = filter.ItemIds,
+            TopParentIds = filter.TopParentIds,
+            ParentId = filter.ParentId,
+            IsAiring = filter.IsAiring,
+            IsMovie = filter.IsMovie,
+            IsSports = filter.IsSports,
+            IsKids = filter.IsKids,
+            IsNews = filter.IsNews,
+            IsSeries = filter.IsSeries
+        });
 
-        query = query.Where(e => e.Type == returnType);
-        // this does not seem to be nesseary but it does not make any sense why this isn't working.
-        // && e.ItemValues!.Any(f => e.CleanName == f.ItemValue.CleanValue && itemValueTypes.Any(w => (ItemValueType)w == f.ItemValue.Type)));
+        var innerQuery = PrepareItemQuery(context, filter)
+            .Where(e => e.Type == returnType)
+            .Where(e => context.ItemValues!
+                .Where(f => itemValueTypes.Contains(f.Type))
+                .Where(f => innerQueryFilter.Any(g => f.BaseItemsMap!.Any(w => w.ItemId == g.Id)))
+                .Select(f => f.CleanValue)
+                .Contains(e.CleanName));
+
+        var outerQueryFilter = new InternalItemsQuery(filter.User)
+        {
+            IsPlayed = filter.IsPlayed,
+            IsFavorite = filter.IsFavorite,
+            IsFavoriteOrLiked = filter.IsFavoriteOrLiked,
+            IsLiked = filter.IsLiked,
+            IsLocked = filter.IsLocked,
+            NameLessThan = filter.NameLessThan,
+            NameStartsWith = filter.NameStartsWith,
+            NameStartsWithOrGreater = filter.NameStartsWithOrGreater,
+            Tags = filter.Tags,
+            OfficialRatings = filter.OfficialRatings,
+            StudioIds = filter.StudioIds,
+            GenreIds = filter.GenreIds,
+            Genres = filter.Genres,
+            Years = filter.Years,
+            NameContains = filter.NameContains,
+            SearchTerm = filter.SearchTerm,
+            ExcludeItemIds = filter.ExcludeItemIds
+        };
 
-        if (filter.OrderBy.Count != 0
-            || !string.IsNullOrEmpty(filter.SearchTerm))
-        {
-            query = ApplyOrder(query, filter);
-        }
-        else
+        var query = TranslateQuery(innerQuery, context, outerQueryFilter)
+            .GroupBy(e => e.PresentationUniqueKey);
+
+        var result = new QueryResult<(BaseItemDto, ItemCounts?)>();
+        if (filter.EnableTotalRecordCount)
         {
-            query = query.OrderBy(e => e.SortName);
+            result.TotalRecordCount = query.Count();
         }
 
         if (filter.Limit.HasValue || filter.StartIndex.HasValue)
@@ -1079,41 +1119,84 @@ public sealed class BaseItemRepository
             }
         }
 
-        var result = new QueryResult<(BaseItemDto, ItemCounts)>();
-        if (filter.EnableTotalRecordCount)
-        {
-            result.TotalRecordCount = query.GroupBy(e => e.PresentationUniqueKey).Select(e => e.First()).Count();
-        }
+        IQueryable<BaseItemEntity>? itemCountQuery = null;
 
-        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 audioTypeName = _itemTypeLookup.BaseItemKindNames[BaseItemKind.Audio];
-        var trailerTypeName = _itemTypeLookup.BaseItemKindNames[BaseItemKind.Trailer];
-
-        var resultQuery = query.Select(e => new
+        if (filter.IncludeItemTypes.Length > 0)
         {
-            item = e,
-            // TODO: This is bad refactor!
-            itemCount = new ItemCounts()
+            // if we are to include more then one type, sub query those items beforehand.
+
+            var typeSubQuery = new InternalItemsQuery(filter.User)
             {
-                SeriesCount = e.ItemValues!.Count(f => f.Item.Type == seriesTypeName),
-                EpisodeCount = e.ItemValues!.Count(f => f.Item.Type == episodeTypeName),
-                MovieCount = e.ItemValues!.Count(f => f.Item.Type == movieTypeName),
-                AlbumCount = e.ItemValues!.Count(f => f.Item.Type == musicAlbumTypeName),
-                ArtistCount = e.ItemValues!.Count(f => f.Item.Type == musicArtistTypeName),
-                SongCount = e.ItemValues!.Count(f => f.Item.Type == audioTypeName),
-                TrailerCount = e.ItemValues!.Count(f => f.Item.Type == trailerTypeName),
-            }
-        });
+                ExcludeItemTypes = filter.ExcludeItemTypes,
+                IncludeItemTypes = filter.IncludeItemTypes,
+                MediaTypes = filter.MediaTypes,
+                AncestorIds = filter.AncestorIds,
+                ExcludeItemIds = filter.ExcludeItemIds,
+                ItemIds = filter.ItemIds,
+                TopParentIds = filter.TopParentIds,
+                ParentId = filter.ParentId,
+                IsPlayed = filter.IsPlayed
+            };
 
-        result.StartIndex = filter.StartIndex ?? 0;
-        result.Items = resultQuery.ToArray().Where(e => e is not null).Select(e =>
+            itemCountQuery = TranslateQuery(context.BaseItems.AsNoTracking(), context, typeSubQuery)
+                .Where(e => e.ItemValues!.Any(f => itemValueTypes!.Contains(f.ItemValue.Type)));
+
+            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 audioTypeName = _itemTypeLookup.BaseItemKindNames[BaseItemKind.Audio];
+            var trailerTypeName = _itemTypeLookup.BaseItemKindNames[BaseItemKind.Trailer];
+
+            var resultQuery = query.Select(e => new
+            {
+                item = e.AsQueryable()
+                        .Include(e => e.TrailerTypes)
+                        .Include(e => e.Provider)
+                        .Include(e => e.LockedFields)
+                        .Include(e => e.Images)
+                        .AsSingleQuery().First(),
+                // TODO: This is bad refactor!
+                itemCount = new ItemCounts()
+                {
+                    SeriesCount = itemCountQuery!.Count(f => f.Type == seriesTypeName),
+                    EpisodeCount = itemCountQuery!.Count(f => f.Type == episodeTypeName),
+                    MovieCount = itemCountQuery!.Count(f => f.Type == movieTypeName),
+                    AlbumCount = itemCountQuery!.Count(f => f.Type == musicAlbumTypeName),
+                    ArtistCount = itemCountQuery!.Count(f => f.Type == musicArtistTypeName),
+                    SongCount = itemCountQuery!.Count(f => f.Type == audioTypeName),
+                    TrailerCount = itemCountQuery!.Count(f => f.Type == trailerTypeName),
+                }
+            });
+
+            result.StartIndex = filter.StartIndex ?? 0;
+            result.Items =
+            [
+                .. resultQuery
+                    .AsEnumerable()
+                    .Where(e => e is not null)
+                    .Select(e =>
+                    {
+                        return (DeserialiseBaseItem(e.item, filter.SkipDeserialization), e.itemCount);
+                    })
+            ];
+        }
+        else
         {
-            return (DeserialiseBaseItem(e.item, filter.SkipDeserialization), e.itemCount);
-        }).ToArray();
+            result.StartIndex = filter.StartIndex ?? 0;
+            result.Items =
+            [
+                .. query
+                    .Select(e => e.First())
+                    .AsEnumerable()
+                    .Where(e => e is not null)
+                    .Select<BaseItemEntity, (BaseItemDto, ItemCounts?)>(e =>
+                    {
+                        return (DeserialiseBaseItem(e, filter.SkipDeserialization), null);
+                    })
+            ];
+        }
 
         return result;
     }
@@ -1296,7 +1379,7 @@ public sealed class BaseItemRepository
         }
         else if (orderBy.Count == 0)
         {
-            return query;
+            return query.OrderBy(e => e.SortName);
         }
 
         IOrderedQueryable<BaseItemEntity>? orderedQuery = null;
@@ -1478,6 +1561,7 @@ public sealed class BaseItemRepository
         }
 
         var includeTypes = filter.IncludeItemTypes;
+
         // Only specify excluded types if no included types are specified
         if (filter.IncludeItemTypes.Length == 0)
         {
@@ -1503,25 +1587,10 @@ public sealed class BaseItemRepository
                 baseQuery = baseQuery.Where(e => !excludeTypeName.Contains(e.Type));
             }
         }
-        else if (includeTypes.Length == 1)
-        {
-            if (_itemTypeLookup.BaseItemKindNames.TryGetValue(includeTypes[0], out var includeTypeName))
-            {
-                baseQuery = baseQuery.Where(e => e.Type == includeTypeName);
-            }
-        }
-        else if (includeTypes.Length > 1)
+        else
         {
-            var includeTypeName = new List<string>();
-            foreach (var includeType in includeTypes)
-            {
-                if (_itemTypeLookup.BaseItemKindNames.TryGetValue(includeType, out var baseItemKindName))
-                {
-                    includeTypeName.Add(baseItemKindName!);
-                }
-            }
-
-            baseQuery = baseQuery.Where(e => includeTypeName.Contains(e.Type));
+            string[] types = includeTypes.Select(f => _itemTypeLookup.BaseItemKindNames.GetValueOrDefault(f)).Where(e => e != null).ToArray()!;
+            baseQuery = baseQuery.WhereOneOrMany(types, f => f.Type);
         }
 
         if (filter.ChannelIds.Count > 0)
@@ -1779,64 +1848,59 @@ public sealed class BaseItemRepository
 
         if (filter.ArtistIds.Length > 0)
         {
-            baseQuery = baseQuery
-                   .Where(e => e.ItemValues!.Any(f => f.ItemValue.Type <= ItemValueType.Artist && filter.ArtistIds.Contains(f.ItemId)));
+            baseQuery = baseQuery.WhereReferencedItem(context, ItemValueType.Artist, filter.ArtistIds);
         }
 
         if (filter.AlbumArtistIds.Length > 0)
         {
-            baseQuery = baseQuery
-                   .Where(e => e.ItemValues!.Any(f => f.ItemValue.Type == ItemValueType.Artist && filter.AlbumArtistIds.Contains(f.ItemId)));
+            baseQuery = baseQuery.WhereReferencedItem(context, ItemValueType.Artist, filter.AlbumArtistIds);
         }
 
         if (filter.ContributingArtistIds.Length > 0)
         {
-            baseQuery = baseQuery
-                   .Where(e => e.ItemValues!.Any(f => f.ItemValue.Type == ItemValueType.Artist && filter.ContributingArtistIds.Contains(f.ItemId)));
+            baseQuery = baseQuery.WhereReferencedItem(context, ItemValueType.Artist, filter.ContributingArtistIds);
         }
 
         if (filter.AlbumIds.Length > 0)
         {
-            baseQuery = baseQuery.Where(e => context.BaseItems.Where(f => filter.AlbumIds.Contains(f.Id)).Any(f => f.Name == e.Album));
+            var subQuery = context.BaseItems.WhereOneOrMany(filter.AlbumIds, f => f.Id);
+            baseQuery = baseQuery.Where(e => subQuery.Any(f => f.Name == e.Album));
         }
 
         if (filter.ExcludeArtistIds.Length > 0)
         {
-            baseQuery = baseQuery
-                   .Where(e => !e.ItemValues!.Any(f => f.ItemValue.Type == ItemValueType.Artist && filter.ExcludeArtistIds.Contains(f.ItemId)));
+            baseQuery = baseQuery.WhereReferencedItem(context, ItemValueType.Artist, filter.ExcludeArtistIds, true);
         }
 
         if (filter.GenreIds.Count > 0)
         {
-            baseQuery = baseQuery
-                   .Where(e => e.ItemValues!.Any(f => f.ItemValue.Type == ItemValueType.Genre && filter.GenreIds.Contains(f.ItemId)));
+            baseQuery = baseQuery.WhereReferencedItem(context, ItemValueType.Genre, filter.GenreIds.ToArray());
         }
 
         if (filter.Genres.Count > 0)
         {
-            var cleanGenres = filter.Genres.Select(e => GetCleanValue(e)).ToArray();
+            var cleanGenres = filter.Genres.Select(e => GetCleanValue(e)).ToArray().OneOrManyExpressionBuilder<ItemValueMap, string>(f => f.ItemValue.CleanValue);
             baseQuery = baseQuery
-                    .Where(e => e.ItemValues!.Any(f => f.ItemValue.Type == ItemValueType.Genre && cleanGenres.Contains(f.ItemValue.CleanValue)));
+                    .Where(e => e.ItemValues!.AsQueryable().Where(f => f.ItemValue.Type == ItemValueType.Genre).Any(cleanGenres));
         }
 
         if (tags.Count > 0)
         {
-            var cleanValues = tags.Select(e => GetCleanValue(e)).ToArray();
+            var cleanValues = tags.Select(e => GetCleanValue(e)).ToArray().OneOrManyExpressionBuilder<ItemValueMap, string>(f => f.ItemValue.CleanValue);
             baseQuery = baseQuery
-                    .Where(e => e.ItemValues!.Any(f => f.ItemValue.Type == ItemValueType.Tags && cleanValues.Contains(f.ItemValue.CleanValue)));
+                    .Where(e => e.ItemValues!.AsQueryable().Where(f => f.ItemValue.Type == ItemValueType.Tags).Any(cleanValues));
         }
 
         if (excludeTags.Count > 0)
         {
-            var cleanValues = excludeTags.Select(e => GetCleanValue(e)).ToArray();
+            var cleanValues = excludeTags.Select(e => GetCleanValue(e)).ToArray().OneOrManyExpressionBuilder<ItemValueMap, string>(f => f.ItemValue.CleanValue);
             baseQuery = baseQuery
-                    .Where(e => !e.ItemValues!.Any(f => f.ItemValue.Type == ItemValueType.Tags && cleanValues.Contains(f.ItemValue.CleanValue)));
+                    .Where(e => !e.ItemValues!.AsQueryable().Where(f => f.ItemValue.Type == ItemValueType.Tags).Any(cleanValues));
         }
 
         if (filter.StudioIds.Length > 0)
         {
-            baseQuery = baseQuery
-                    .Where(e => e.ItemValues!.Any(f => f.ItemValue.Type == ItemValueType.Studios && filter.StudioIds.Contains(f.ItemId)));
+            baseQuery = baseQuery.WhereReferencedItem(context, ItemValueType.Studios, filter.StudioIds.ToArray());
         }
 
         if (filter.OfficialRatings.Length > 0)
@@ -2027,15 +2091,9 @@ public sealed class BaseItemRepository
                 .Where(e => !context.Peoples.Any(f => f.Name == e.Name));
         }
 
-        if (filter.Years.Length == 1)
+        if (filter.Years.Length > 0)
         {
-            baseQuery = baseQuery
-                .Where(e => e.ProductionYear == filter.Years[0]);
-        }
-        else if (filter.Years.Length > 1)
-        {
-            baseQuery = baseQuery
-                .Where(e => filter.Years.Any(f => f == e.ProductionYear));
+            baseQuery = baseQuery.WhereOneOrMany(filter.Years, e => e.ProductionYear!.Value);
         }
 
         var isVirtualItem = filter.IsVirtualItem ?? filter.IsMissing;
@@ -2076,14 +2134,12 @@ public sealed class BaseItemRepository
         if (filter.MediaTypes.Length > 0)
         {
             var mediaTypes = filter.MediaTypes.Select(f => f.ToString()).ToArray();
-            baseQuery = baseQuery
-                .Where(e => mediaTypes.Contains(e.MediaType));
+            baseQuery = baseQuery.WhereOneOrMany(mediaTypes, e => e.MediaType);
         }
 
         if (filter.ItemIds.Length > 0)
         {
-            baseQuery = baseQuery
-                .Where(e => filter.ItemIds.Contains(e.Id));
+            baseQuery = baseQuery.WhereOneOrMany(filter.ItemIds, e => e.Id);
         }
 
         if (filter.ExcludeItemIds.Length > 0)
@@ -2129,13 +2185,13 @@ public sealed class BaseItemRepository
             }
             else
             {
-                baseQuery = baseQuery.Where(e => queryTopParentIds.Contains(e.TopParentId!.Value));
+                baseQuery = baseQuery.WhereOneOrMany(queryTopParentIds, e => e.TopParentId!.Value);
             }
         }
 
         if (filter.AncestorIds.Length > 0)
         {
-            baseQuery = baseQuery.Where(e => e.Children!.Any(f => filter.AncestorIds.Contains(f.ParentItemId)));
+            baseQuery = baseQuery.Where(e => e.Children!.Any(f => filter.AncestorIds.Contains(f.ItemId)));
         }
 
         if (!string.IsNullOrWhiteSpace(filter.AncestorWithPresentationUniqueKey))
diff --git a/src/Jellyfin.Database/Jellyfin.Database.Implementations/JellyfinQueryHelperExtensions.cs b/src/Jellyfin.Database/Jellyfin.Database.Implementations/JellyfinQueryHelperExtensions.cs
new file mode 100644
index 000000000..4d5cfb8c9
--- /dev/null
+++ b/src/Jellyfin.Database/Jellyfin.Database.Implementations/JellyfinQueryHelperExtensions.cs
@@ -0,0 +1,166 @@
+#pragma warning disable RS0030 // Do not use banned APIs
+
+using System;
+using System.Collections.Concurrent;
+using System.Collections.Generic;
+using System.Linq;
+using System.Linq.Expressions;
+using System.Reflection;
+using Jellyfin.Database.Implementations.Entities;
+using Microsoft.EntityFrameworkCore;
+
+namespace Jellyfin.Database.Implementations;
+
+/// <summary>
+/// Contains a number of query related extensions.
+/// </summary>
+public static class JellyfinQueryHelperExtensions
+{
+    private static readonly MethodInfo _containsMethodGenericCache = typeof(Enumerable).GetMethods(BindingFlags.Public | BindingFlags.Static).First(m => m.Name == nameof(Enumerable.Contains) && m.GetParameters().Length == 2);
+    private static readonly MethodInfo _efParameterInstruction = typeof(EF).GetMethod(nameof(EF.Parameter), BindingFlags.Public | BindingFlags.Static)!;
+    private static readonly ConcurrentDictionary<Type, MethodInfo> _containsQueryCache = new();
+
+    /// <summary>
+    /// Builds an optimised query checking one property against a list of values while maintaining an optimal query.
+    /// </summary>
+    /// <typeparam name="TEntity">The entity.</typeparam>
+    /// <typeparam name="TProperty">The property type to compare.</typeparam>
+    /// <param name="query">The source query.</param>
+    /// <param name="oneOf">The list of items to check.</param>
+    /// <param name="property">Property expression.</param>
+    /// <returns>A Query.</returns>
+    public static IQueryable<TEntity> WhereOneOrMany<TEntity, TProperty>(this IQueryable<TEntity> query, IList<TProperty> oneOf, Expression<Func<TEntity, TProperty>> property)
+    {
+        return query.Where(OneOrManyExpressionBuilder(oneOf, property));
+    }
+
+    /// <summary>
+    /// Builds a query that checks referenced ItemValues for a cross BaseItem lookup.
+    /// </summary>
+    /// <param name="baseQuery">The source query.</param>
+    /// <param name="context">The database context.</param>
+    /// <param name="itemValueType">The type of item value to reference.</param>
+    /// <param name="referenceIds">The list of BaseItem ids to check matches.</param>
+    /// <param name="invert">If set an exclusion check is performed instead.</param>
+    /// <returns>A Query.</returns>
+    public static IQueryable<BaseItemEntity> WhereReferencedItem(
+        this IQueryable<BaseItemEntity> baseQuery,
+        JellyfinDbContext context,
+        ItemValueType itemValueType,
+        IList<Guid> referenceIds,
+        bool invert = false)
+    {
+        return baseQuery.Where(ReferencedItemFilterExpressionBuilder(context, itemValueType, referenceIds, invert));
+    }
+
+    /// <summary>
+    /// Builds a query expression that checks referenced ItemValues for a cross BaseItem lookup.
+    /// </summary>
+    /// <param name="context">The database context.</param>
+    /// <param name="itemValueType">The type of item value to reference.</param>
+    /// <param name="referenceIds">The list of BaseItem ids to check matches.</param>
+    /// <param name="invert">If set an exclusion check is performed instead.</param>
+    /// <returns>A Query.</returns>
+    public static Expression<Func<BaseItemEntity, bool>> ReferencedItemFilterExpressionBuilder(
+        this JellyfinDbContext context,
+        ItemValueType itemValueType,
+        IList<Guid> referenceIds,
+        bool invert = false)
+    {
+        // Well genre/artist/album etc items do not actually set the ItemValue of thier specitic types so we cannot match it that way.
+        /*
+        "(guid in (select itemid from ItemValues where CleanValue = (select CleanName from TypedBaseItems where guid=@GenreIds and Type=2)))"
+        */
+
+        var itemFilter = OneOrManyExpressionBuilder<BaseItemEntity, Guid>(referenceIds, f => f.Id);
+
+        return item =>
+          context.ItemValues
+              .Join(context.ItemValuesMap, e => e.ItemValueId, e => e.ItemValueId, (item, map) => new { item, map })
+              .Any(val =>
+                  val.item.Type == itemValueType
+                  && context.BaseItems.Where(itemFilter).Any(e => e.CleanName == val.item.CleanValue)
+                  && val.map.ItemId == item.Id) == EF.Constant(!invert);
+    }
+
+    /// <summary>
+    /// Builds an optimised query expression checking one property against a list of values while maintaining an optimal query.
+    /// </summary>
+    /// <typeparam name="TEntity">The entity.</typeparam>
+    /// <typeparam name="TProperty">The property type to compare.</typeparam>
+    /// <param name="oneOf">The list of items to check.</param>
+    /// <param name="property">Property expression.</param>
+    /// <returns>A Query.</returns>
+    public static Expression<Func<TEntity, bool>> OneOrManyExpressionBuilder<TEntity, TProperty>(this IList<TProperty> oneOf, Expression<Func<TEntity, TProperty>> property)
+    {
+        var parameter = Expression.Parameter(typeof(TEntity), "item");
+        property = ParameterReplacer.Replace<Func<TEntity, TProperty>, Func<TEntity, TProperty>>(property, property.Parameters[0], parameter);
+        if (oneOf.Count == 1)
+        {
+            var value = oneOf[0];
+            if (typeof(TProperty).IsValueType)
+            {
+                return Expression.Lambda<Func<TEntity, bool>>(Expression.Equal(property.Body, Expression.Constant(value)), parameter);
+            }
+            else
+            {
+                return Expression.Lambda<Func<TEntity, bool>>(Expression.ReferenceEqual(property.Body, Expression.Constant(value)), parameter);
+            }
+        }
+
+        var containsMethodInfo = _containsQueryCache.GetOrAdd(typeof(TProperty), static (key) => _containsMethodGenericCache.MakeGenericMethod(key));
+
+        if (oneOf.Count < 4) // arbitrary value choosen.
+        {
+            // if we have 3 or fewer values to check against its faster to do a IN(const,const,const) lookup
+            return Expression.Lambda<Func<TEntity, bool>>(Expression.Call(null, containsMethodInfo, Expression.Constant(oneOf), property.Body), parameter);
+        }
+
+        return Expression.Lambda<Func<TEntity, bool>>(Expression.Call(null, containsMethodInfo, Expression.Call(null, _efParameterInstruction.MakeGenericMethod(oneOf.GetType()), Expression.Constant(oneOf)), property.Body), parameter);
+    }
+
+    internal static class ParameterReplacer
+    {
+        // Produces an expression identical to 'expression'
+        // except with 'source' parameter replaced with 'target' expression.
+        internal static Expression<TOutput> Replace<TInput, TOutput>(
+                        Expression<TInput> expression,
+                        ParameterExpression source,
+                        ParameterExpression target)
+        {
+            return new ParameterReplacerVisitor<TOutput>(source, target)
+                        .VisitAndConvert(expression);
+        }
+
+        private sealed class ParameterReplacerVisitor<TOutput> : ExpressionVisitor
+        {
+            private readonly ParameterExpression _source;
+            private readonly ParameterExpression _target;
+
+            public ParameterReplacerVisitor(ParameterExpression source, ParameterExpression target)
+            {
+                _source = source;
+                _target = target;
+            }
+
+            internal Expression<TOutput> VisitAndConvert<T>(Expression<T> root)
+            {
+                return (Expression<TOutput>)VisitLambda(root);
+            }
+
+            protected override Expression VisitLambda<T>(Expression<T> node)
+            {
+                // Leave all parameters alone except the one we want to replace.
+                var parameters = node.Parameters.Select(p => p == _source ? _target : p);
+
+                return Expression.Lambda<TOutput>(Visit(node.Body), parameters);
+            }
+
+            protected override Expression VisitParameter(ParameterExpression node)
+            {
+                // Replace the source with the target, visit other params as usual.
+                return node == _source ? _target : base.VisitParameter(node);
+            }
+        }
+    }
+}