Backport pull request #17980 from jellyfin/release-12.z

Drop dead item data and fix query ordering and bound parameters

Original-merge: 3daa917de024dbb605e0686f632c17729dc9527c

Merged-by: crobibero <cody@robibe.ro>

Backported-by: Cody Robibero <cody@robibe.ro>

4 files changed, 50 insertions, 6 deletions

diff --git a/Jellyfin.Server.Implementations/Item/BaseItemRepository.QueryBuilding.cs b/Jellyfin.Server.Implementations/Item/BaseItemRepository.QueryBuilding.cs
index 92f3a4fceb..1a4c9da41c 100644
--- a/Jellyfin.Server.Implementations/Item/BaseItemRepository.QueryBuilding.cs
+++ b/Jellyfin.Server.Implementations/Item/BaseItemRepository.QueryBuilding.cs
@@ -271,7 +271,7 @@ public sealed partial class BaseItemRepository
 
         if (filter.DtoOptions.EnableImages)
         {
-            dbQuery = dbQuery.Include(e => e.Images);
+            dbQuery = dbQuery.Include(e => e.Images!.OrderBy(i => i.Id));
         }
 
         // Include LinkedChildEntities for container types and videos that use them (BoxSet, Playlist,
@@ -291,7 +291,7 @@ public sealed partial class BaseItemRepository
         };
         if (filter.IncludeItemTypes.Length == 0 || filter.IncludeItemTypes.Any(linkedChildTypes.Contains))
         {
-            dbQuery = dbQuery.Include(e => e.LinkedChildEntities);
+            dbQuery = dbQuery.Include(e => e.LinkedChildEntities!.OrderBy(l => l.SortOrder));
         }
 
         if (filter.IncludeExtras)
diff --git a/Jellyfin.Server.Implementations/Item/ItemPersistenceService.cs b/Jellyfin.Server.Implementations/Item/ItemPersistenceService.cs
index 194c4b33d9..a7b0b1c1fc 100644
--- a/Jellyfin.Server.Implementations/Item/ItemPersistenceService.cs
+++ b/Jellyfin.Server.Implementations/Item/ItemPersistenceService.cs
@@ -277,7 +277,7 @@ public class ItemPersistenceService : IItemPersistenceService
         using var transaction = context.Database.BeginTransaction();
 
         var ids = tuples.Select(f => f.Item.Id).ToArray();
-        var existingItems = context.BaseItems.Where(e => ids.Contains(e.Id)).Select(f => f.Id).ToHashSet();
+        var existingItems = context.BaseItems.WhereOneOrMany(ids, e => e.Id).Select(f => f.Id).ToHashSet();
 
         foreach (var item in tuples)
         {
@@ -337,7 +337,7 @@ public class ItemPersistenceService : IItemPersistenceService
             .Select(f => (f.Item, Values: f.Values.Select(e => itemValuesStore[(e.MagicNumber, e.Value)]).DistinctBy(e => e.ItemValueId).ToArray()))
             .ToArray();
 
-        var mappedValues = context.ItemValuesMap.Where(e => ids.Contains(e.ItemId)).ToList();
+        var mappedValues = context.ItemValuesMap.WhereOneOrMany(ids, e => e.ItemId).ToList();
 
         foreach (var item in valueMap)
         {
diff --git a/Jellyfin.Server.Implementations/Item/PeopleRepository.cs b/Jellyfin.Server.Implementations/Item/PeopleRepository.cs
index da2ad033ec..b1acb277be 100644
--- a/Jellyfin.Server.Implementations/Item/PeopleRepository.cs
+++ b/Jellyfin.Server.Implementations/Item/PeopleRepository.cs
@@ -238,7 +238,7 @@ public class PeopleRepository(IDbContextFactory<JellyfinDbContext> dbProvider, I
         using var context = _dbProvider.CreateDbContext();
         var query = context.PeopleBaseItemMap
             .AsNoTracking()
-            .Where(m => itemIds.Contains(m.ItemId));
+            .WhereOneOrMany(itemIds, m => m.ItemId);
 
         if (personTypes.Count > 0)
         {
@@ -274,7 +274,7 @@ public class PeopleRepository(IDbContextFactory<JellyfinDbContext> dbProvider, I
         using var context = _dbProvider.CreateDbContext();
         var rows = context.PeopleBaseItemMap
             .AsNoTracking()
-            .Where(m => itemIds.Contains(m.ItemId))
+            .WhereOneOrMany(itemIds, m => m.ItemId)
             .OrderBy(m => m.ListOrder)
             .Select(m => new
             {
diff --git a/Jellyfin.Server/Migrations/Routines/20260911120000_StripEmbeddedLinkedChildren.cs b/Jellyfin.Server/Migrations/Routines/20260911120000_StripEmbeddedLinkedChildren.cs
new file mode 100644
index 0000000000..f61e42337a
--- /dev/null
+++ b/Jellyfin.Server/Migrations/Routines/20260911120000_StripEmbeddedLinkedChildren.cs
@@ -0,0 +1,44 @@
+using Jellyfin.Database.Implementations;
+using Microsoft.EntityFrameworkCore;
+using Microsoft.Extensions.Logging;
+
+namespace Jellyfin.Server.Migrations.Routines;
+
+/// <summary>
+/// Drops keys that no current property reads or writes from the serialized <c>BaseItems.Data</c> blob.
+/// </summary>
+[JellyfinMigration("2026-09-11T12:00:00", nameof(StripEmbeddedLinkedChildren))]
+internal class StripEmbeddedLinkedChildren : IDatabaseMigrationRoutine
+{
+    private readonly ILogger<StripEmbeddedLinkedChildren> _logger;
+    private readonly IDbContextFactory<JellyfinDbContext> _dbProvider;
+
+    public StripEmbeddedLinkedChildren(
+        ILoggerFactory loggerFactory,
+        IDbContextFactory<JellyfinDbContext> dbProvider)
+    {
+        _logger = loggerFactory.CreateLogger<StripEmbeddedLinkedChildren>();
+        _dbProvider = dbProvider;
+    }
+
+    /// <inheritdoc/>
+    public void Perform()
+    {
+        using var context = _dbProvider.CreateDbContext();
+
+        // json_valid guards the rare malformed blob: json_remove would abort the statement on it,
+        // and one bad row must not cost every other row the fix.
+        var updated = context.Database.ExecuteSqlRaw(
+            """
+            UPDATE "BaseItems"
+            SET "Data" = json_remove("Data", '$.LinkedChildren', '$.ExtraIds', '$.SupportsExternalTransfer')
+            WHERE "Data" IS NOT NULL
+              AND json_valid("Data") = 1
+              AND ("Data" LIKE '%"LinkedChildren"%'
+                OR "Data" LIKE '%"ExtraIds"%'
+                OR "Data" LIKE '%"SupportsExternalTransfer"%')
+            """);
+
+        _logger.LogInformation("Dropped dead keys from the serialized data of {Count} items", updated);
+    }
+}