From 1c4b5199b8fa42dd41d6d779db98650a460c7117 Mon Sep 17 00:00:00 2001 From: JPVenson Date: Sun, 27 Apr 2025 04:10:54 +0300 Subject: Fix ItemValue query (#13939) --- .../JellyfinQueryHelperExtensions.cs | 166 +++++++++++++++++++++ 1 file changed, 166 insertions(+) create mode 100644 src/Jellyfin.Database/Jellyfin.Database.Implementations/JellyfinQueryHelperExtensions.cs (limited to 'src') 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; + +/// +/// Contains a number of query related extensions. +/// +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 _containsQueryCache = new(); + + /// + /// Builds an optimised query checking one property against a list of values while maintaining an optimal query. + /// + /// The entity. + /// The property type to compare. + /// The source query. + /// The list of items to check. + /// Property expression. + /// A Query. + public static IQueryable WhereOneOrMany(this IQueryable query, IList oneOf, Expression> property) + { + return query.Where(OneOrManyExpressionBuilder(oneOf, property)); + } + + /// + /// Builds a query that checks referenced ItemValues for a cross BaseItem lookup. + /// + /// The source query. + /// The database context. + /// The type of item value to reference. + /// The list of BaseItem ids to check matches. + /// If set an exclusion check is performed instead. + /// A Query. + public static IQueryable WhereReferencedItem( + this IQueryable baseQuery, + JellyfinDbContext context, + ItemValueType itemValueType, + IList referenceIds, + bool invert = false) + { + return baseQuery.Where(ReferencedItemFilterExpressionBuilder(context, itemValueType, referenceIds, invert)); + } + + /// + /// Builds a query expression that checks referenced ItemValues for a cross BaseItem lookup. + /// + /// The database context. + /// The type of item value to reference. + /// The list of BaseItem ids to check matches. + /// If set an exclusion check is performed instead. + /// A Query. + public static Expression> ReferencedItemFilterExpressionBuilder( + this JellyfinDbContext context, + ItemValueType itemValueType, + IList 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(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); + } + + /// + /// Builds an optimised query expression checking one property against a list of values while maintaining an optimal query. + /// + /// The entity. + /// The property type to compare. + /// The list of items to check. + /// Property expression. + /// A Query. + public static Expression> OneOrManyExpressionBuilder(this IList oneOf, Expression> property) + { + var parameter = Expression.Parameter(typeof(TEntity), "item"); + property = ParameterReplacer.Replace, Func>(property, property.Parameters[0], parameter); + if (oneOf.Count == 1) + { + var value = oneOf[0]; + if (typeof(TProperty).IsValueType) + { + return Expression.Lambda>(Expression.Equal(property.Body, Expression.Constant(value)), parameter); + } + else + { + return Expression.Lambda>(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>(Expression.Call(null, containsMethodInfo, Expression.Constant(oneOf), property.Body), parameter); + } + + return Expression.Lambda>(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 Replace( + Expression expression, + ParameterExpression source, + ParameterExpression target) + { + return new ParameterReplacerVisitor(source, target) + .VisitAndConvert(expression); + } + + private sealed class ParameterReplacerVisitor : ExpressionVisitor + { + private readonly ParameterExpression _source; + private readonly ParameterExpression _target; + + public ParameterReplacerVisitor(ParameterExpression source, ParameterExpression target) + { + _source = source; + _target = target; + } + + internal Expression VisitAndConvert(Expression root) + { + return (Expression)VisitLambda(root); + } + + protected override Expression VisitLambda(Expression 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(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); + } + } + } +} -- cgit v1.2.3