Merge remote-tracking branch 'upstream/master' into tmdb-missing-episodes

# Conflicts:
#	Jellyfin.Server.Implementations/Item/BaseItemRepository.QueryBuilding.cs

4 files changed, 48 insertions, 27 deletions

diff --git a/src/Jellyfin.Database/Jellyfin.Database.Implementations/Entities/LinkedChildEntity.cs b/src/Jellyfin.Database/Jellyfin.Database.Implementations/Entities/LinkedChildEntity.cs
index 7361775711..be315f1b2c 100644
--- a/src/Jellyfin.Database/Jellyfin.Database.Implementations/Entities/LinkedChildEntity.cs
+++ b/src/Jellyfin.Database/Jellyfin.Database.Implementations/Entities/LinkedChildEntity.cs
@@ -25,7 +25,7 @@ public class LinkedChildEntity
     /// <summary>
     /// Gets or sets the sort order.
     /// </summary>
-    public int? SortOrder { get; set; }
+    public int SortOrder { get; set; }
 
     /// <summary>
     /// Gets or sets the parent item navigation property.
diff --git a/src/Jellyfin.Database/Jellyfin.Database.Implementations/JellyfinQueryHelperExtensions.cs b/src/Jellyfin.Database/Jellyfin.Database.Implementations/JellyfinQueryHelperExtensions.cs
index 1af7460540..fec37ce723 100644
--- a/src/Jellyfin.Database/Jellyfin.Database.Implementations/JellyfinQueryHelperExtensions.cs
+++ b/src/Jellyfin.Database/Jellyfin.Database.Implementations/JellyfinQueryHelperExtensions.cs
@@ -70,15 +70,24 @@ public static class JellyfinQueryHelperExtensions
         bool invert = false)
     {
         var itemFilter = OneOrManyExpressionBuilder<BaseItemEntity, Guid>(referenceIds, f => f.Id);
-        var typeFilter = OneOrManyExpressionBuilder<ItemValue, ItemValueType>(itemValueTypes, iv => iv.Type);
-
-        return baseQuery.Where(item =>
-            context.ItemValues
-                .Where(typeFilter)
-                .Join(context.ItemValuesMap, e => e.ItemValueId, e => e.ItemValueId, (itemVal, map) => new { itemVal, map })
-                .Any(val =>
-                    context.BaseItems.Where(itemFilter).Any(e => e.CleanName == val.itemVal.CleanValue)
-                    && val.map.ItemId == item.Id) == EF.Constant(!invert));
+        var typeFilter = OneOrManyExpressionBuilder<ItemValueMap, ItemValueType>(itemValueTypes, m => m.ItemValue.Type);
+
+        // Flat sub-selects + Contains instead of a nested correlated .Any(...Any(...)).
+        var referencedCleanValues = context.BaseItems
+            .Where(itemFilter)
+            .Select(e => e.CleanName);
+
+        var matchingItemIds = context.ItemValuesMap
+            .Where(typeFilter)
+            .Where(m => referencedCleanValues.Contains(m.ItemValue.CleanValue))
+            .Select(m => m.ItemId);
+
+        if (invert)
+        {
+            return baseQuery.Where(e => !matchingItemIds.Contains(e.Id));
+        }
+
+        return baseQuery.Where(e => matchingItemIds.Contains(e.Id));
     }
 
     /// <summary>
@@ -102,13 +111,21 @@ public static class JellyfinQueryHelperExtensions
 
         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);
+        // Flat sub-selects + Contains instead of a nested correlated .Any(...Any(...)).
+        var referencedCleanValues = context.BaseItems
+            .Where(itemFilter)
+            .Select(e => e.CleanName);
+
+        var matchingItemIds = context.ItemValuesMap
+            .Where(m => m.ItemValue.Type == itemValueType && referencedCleanValues.Contains(m.ItemValue.CleanValue))
+            .Select(m => m.ItemId);
+
+        if (invert)
+        {
+            return item => !matchingItemIds.Contains(item.Id);
+        }
+
+        return item => matchingItemIds.Contains(item.Id);
     }
 
     /// <summary>
@@ -224,14 +241,14 @@ public static class JellyfinQueryHelperExtensions
 
         var containsMethodInfo = _containsQueryCache.GetOrAdd(typeof(TProperty), static (key) => _containsMethodGenericCache.MakeGenericMethod(key));
 
-        // Threshold picked from microbenchmarks on SQLite: inline IN(const,...) beats a
-        // parameterized array lookup by ~5-10% up to ~32 elements.
-        if (oneOf.Count <= 32)
-        {
-            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);
+        // Always wrap the collection in EF.Parameter so EF Core caches a single compiled plan and reuses it across calls.
+        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
diff --git a/src/Jellyfin.Database/Jellyfin.Database.Implementations/ModelConfiguration/BaseItemConfiguration.cs b/src/Jellyfin.Database/Jellyfin.Database.Implementations/ModelConfiguration/BaseItemConfiguration.cs
index 8556fb7bb3..ee36be035b 100644
--- a/src/Jellyfin.Database/Jellyfin.Database.Implementations/ModelConfiguration/BaseItemConfiguration.cs
+++ b/src/Jellyfin.Database/Jellyfin.Database.Implementations/ModelConfiguration/BaseItemConfiguration.cs
@@ -61,6 +61,11 @@ public class BaseItemConfiguration : IEntityTypeConfiguration<BaseItemEntity>
         builder.HasIndex(e => new { e.TopParentId, e.MediaType, e.IsVirtualItem, e.DateCreated });
         // resume
         builder.HasIndex(e => new { e.MediaType, e.TopParentId, e.IsVirtualItem, e.PresentationUniqueKey });
+        // alternate versions of an item, e.g. resolving the played date of a version onto its primary.
+        // Filtered: almost no item has a primary version, and an index covering those rows too would tempt
+        // the planner into serving "PrimaryVersionId IS NULL" - true for the whole library - out of it.
+        builder.HasIndex(e => e.PrimaryVersionId)
+            .HasFilter("\"PrimaryVersionId\" IS NOT NULL");
         // sorted library queries (e.g., Series sorted by SortName)
         builder.HasIndex(e => new { e.Type, e.TopParentId, e.SortName });
         // NextUp: per-series episode ordering (index seek + range scan on season/episode)
diff --git a/src/Jellyfin.Database/Jellyfin.Database.Implementations/ModelConfiguration/LinkedChildConfiguration.cs b/src/Jellyfin.Database/Jellyfin.Database.Implementations/ModelConfiguration/LinkedChildConfiguration.cs
index 2abccd41f0..b4013a394f 100644
--- a/src/Jellyfin.Database/Jellyfin.Database.Implementations/ModelConfiguration/LinkedChildConfiguration.cs
+++ b/src/Jellyfin.Database/Jellyfin.Database.Implementations/ModelConfiguration/LinkedChildConfiguration.cs
@@ -13,8 +13,7 @@ public class LinkedChildConfiguration : IEntityTypeConfiguration<LinkedChildEnti
     public void Configure(EntityTypeBuilder<LinkedChildEntity> builder)
     {
         builder.ToTable("LinkedChildren");
-        builder.HasKey(e => new { e.ParentId, e.ChildId });
-        builder.HasIndex(e => new { e.ParentId, e.SortOrder });
+        builder.HasKey(e => new { e.ParentId, e.SortOrder });
         builder.HasIndex(e => new { e.ParentId, e.ChildType });
         builder.HasIndex(e => new { e.ChildId, e.ChildType });