1 files changed, 93 insertions, 7 deletions
diff --git a/src/Jellyfin.Database/Jellyfin.Database.Implementations/JellyfinQueryHelperExtensions.cs b/src/Jellyfin.Database/Jellyfin.Database.Implementations/JellyfinQueryHelperExtensions.cs
index f386e882e2..e366bdb095 100644
--- a/src/Jellyfin.Database/Jellyfin.Database.Implementations/JellyfinQueryHelperExtensions.cs
+++ b/src/Jellyfin.Database/Jellyfin.Database.Implementations/JellyfinQueryHelperExtensions.cs
@@ -112,6 +112,92 @@ public static class JellyfinQueryHelperExtensions
     }
 
     /// <summary>
+    /// Filters items that match any of the specified (provider name, value) pairs.
+    /// </summary>
+    /// <param name="baseQuery">The source query.</param>
+    /// <param name="providerIds">Dictionary mapping provider names to arrays of values to match.</param>
+    /// <returns>A filtered query.</returns>
+    public static IQueryable<BaseItemEntity> WhereHasAnyProviderIds(
+        this IQueryable<BaseItemEntity> baseQuery,
+        IReadOnlyDictionary<string, string[]> providerIds)
+    {
+        var providerKeys = providerIds
+            .SelectMany(kvp => kvp.Value.Select(v => $"{kvp.Key}:{v}"))
+            .ToList();
+
+        if (providerKeys.Count == 0)
+        {
+            return baseQuery;
+        }
+
+        return baseQuery.Where(e => e.Provider!.Any(p => providerKeys.Contains(p.ProviderId + ":" + p.ProviderValue)));
+    }
+
+    /// <summary>
+    /// Filters items that have any of the specified providers. Empty/null values match any value for that provider.
+    /// </summary>
+    /// <param name="baseQuery">The source query.</param>
+    /// <param name="providerIds">Dictionary mapping provider names to optional values.</param>
+    /// <returns>A filtered query.</returns>
+    public static IQueryable<BaseItemEntity> WhereHasAnyProviderId(
+        this IQueryable<BaseItemEntity> baseQuery,
+        IReadOnlyDictionary<string, string> providerIds)
+    {
+        var existenceOnly = providerIds
+            .Where(e => string.IsNullOrEmpty(e.Value))
+            .Select(e => e.Key)
+            .ToList();
+
+        var specificValues = providerIds
+            .Where(e => !string.IsNullOrEmpty(e.Value))
+            .Select(e => $"{e.Key}:{e.Value}")
+            .ToList();
+
+        if (existenceOnly.Count == 0 && specificValues.Count == 0)
+        {
+            return baseQuery;
+        }
+
+        if (existenceOnly.Count == 0)
+        {
+            return baseQuery.Where(e => e.Provider!.Any(p =>
+                specificValues.Contains(p.ProviderId + ":" + p.ProviderValue)));
+        }
+
+        if (specificValues.Count == 0)
+        {
+            return baseQuery.Where(e => e.Provider!.Any(p => existenceOnly.Contains(p.ProviderId)));
+        }
+
+        // Single EXISTS over Provider with both predicates OR'd, instead of two separate subqueries.
+        return baseQuery.Where(e => e.Provider!.Any(p =>
+            existenceOnly.Contains(p.ProviderId) ||
+            specificValues.Contains(p.ProviderId + ":" + p.ProviderValue)));
+    }
+
+    /// <summary>
+    /// Excludes items that match any of the specified (provider name, value) pairs.
+    /// </summary>
+    /// <param name="baseQuery">The source query.</param>
+    /// <param name="providerIds">Dictionary mapping provider names to values to exclude.</param>
+    /// <returns>A filtered query.</returns>
+    public static IQueryable<BaseItemEntity> WhereExcludeProviderIds(
+        this IQueryable<BaseItemEntity> baseQuery,
+        IReadOnlyDictionary<string, string> providerIds)
+    {
+        var excludeKeys = providerIds
+            .Select(e => $"{e.Key}:{e.Value}")
+            .ToList();
+
+        if (excludeKeys.Count == 0)
+        {
+            return baseQuery;
+        }
+
+        return baseQuery.Where(e => e.Provider!.All(p => !excludeKeys.Contains(p.ProviderId + ":" + p.ProviderValue)));
+    }
+
+    /// <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>
@@ -138,13 +224,13 @@ public static class JellyfinQueryHelperExtensions
 
         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);
+        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