Remove ExtraIds column and use OwnerId relationship for extras

- Remove ExtraIds property from BaseItemEntity and BaseItem
- Update RefreshExtras to query via OwnerId instead of cached ExtraIds
- Update GetExtras methods to query database via OwnerIds filter
- Add OwnerIds and ExtraTypes filter support to InternalItemsQuery
- Add filter handling in BaseItemRepository for new query options
- Update HasSpecialFeature/HasTrailer filters to use Extras relationship
- Add CleanupOrphanedExtras migration routine
- Add database migration to drop ExtraIds column

2 files changed, 25 insertions, 17 deletions

diff --git a/MediaBrowser.Controller/Entities/BaseItem.cs b/MediaBrowser.Controller/Entities/BaseItem.cs
index 498b7d19e4..eb7daeb532 100644
--- a/MediaBrowser.Controller/Entities/BaseItem.cs
+++ b/MediaBrowser.Controller/Entities/BaseItem.cs
@@ -107,7 +107,6 @@ namespace MediaBrowser.Controller.Entities
             ImageInfos = Array.Empty<ItemImageInfo>();
             ProductionLocations = Array.Empty<string>();
             RemoteTrailers = Array.Empty<MediaUrl>();
-            ExtraIds = Array.Empty<Guid>();
             UserData = [];
         }
 
@@ -398,8 +397,6 @@ namespace MediaBrowser.Controller.Entities
 
         public int Height { get; set; }
 
-        public Guid[] ExtraIds { get; set; }
-
         /// <summary>
         /// Gets the primary image path.
         /// </summary>
@@ -1396,7 +1393,13 @@ namespace MediaBrowser.Controller.Entities
         {
             var extras = LibraryManager.FindExtras(item, fileSystemChildren, options.DirectoryService).ToArray();
             var newExtraIds = Array.ConvertAll(extras, x => x.Id);
-            var extrasChanged = !item.ExtraIds.SequenceEqual(newExtraIds);
+
+            var currentExtraIds = LibraryManager.GetItemList(new InternalItemsQuery()
+            {
+                OwnerIds = [item.Id]
+            }).Select(e => e.Id).ToArray();
+
+            var extrasChanged = !currentExtraIds.OrderBy(x => x).SequenceEqual(newExtraIds.OrderBy(x => x));
 
             if (!extrasChanged && !options.ReplaceAllMetadata && options.MetadataRefreshMode != MetadataRefreshMode.FullRefresh)
             {
@@ -1418,8 +1421,7 @@ namespace MediaBrowser.Controller.Entities
                 return RefreshMetadataForOwnedItem(i, true, subOptions, cancellationToken);
             });
 
-            // Cleanup removed extras
-            var removedExtraIds = item.ExtraIds.Where(e => !newExtraIds.Contains(e)).ToArray();
+            var removedExtraIds = currentExtraIds.Where(e => !newExtraIds.Contains(e)).ToArray();
             if (removedExtraIds.Length > 0)
             {
                 var removedExtras = LibraryManager.GetItemList(new InternalItemsQuery()
@@ -1437,8 +1439,6 @@ namespace MediaBrowser.Controller.Entities
 
             await Task.WhenAll(tasks).ConfigureAwait(false);
 
-            item.ExtraIds = newExtraIds;
-
             return true;
         }
 
@@ -2668,10 +2668,11 @@ namespace MediaBrowser.Controller.Entities
         /// <returns>An enumerable containing the items.</returns>
         public IEnumerable<BaseItem> GetExtras()
         {
-            return ExtraIds
-                .Select(LibraryManager.GetItemById)
-                .Where(i => i is not null)
-                .OrderBy(i => i.SortName);
+            return LibraryManager.GetItemList(new InternalItemsQuery()
+            {
+                OwnerIds = [Id],
+                OrderBy = [(ItemSortBy.SortName, SortOrder.Ascending)]
+            });
         }
 
         /// <summary>
@@ -2681,11 +2682,12 @@ namespace MediaBrowser.Controller.Entities
         /// <returns>An enumerable containing the extras.</returns>
         public IEnumerable<BaseItem> GetExtras(IReadOnlyCollection<ExtraType> extraTypes)
         {
-            return ExtraIds
-                .Select(LibraryManager.GetItemById)
-                .Where(i => i is not null)
-                .Where(i => i.ExtraType.HasValue && extraTypes.Contains(i.ExtraType.Value))
-                .OrderBy(i => i.SortName);
+            return LibraryManager.GetItemList(new InternalItemsQuery()
+            {
+                OwnerIds = [Id],
+                ExtraTypes = extraTypes.ToArray(),
+                OrderBy = [(ItemSortBy.SortName, SortOrder.Ascending)]
+            });
         }
 
         public virtual long GetRunTimeTicksForPlayState()
diff --git a/MediaBrowser.Controller/Entities/InternalItemsQuery.cs b/MediaBrowser.Controller/Entities/InternalItemsQuery.cs
index 076a592922..24fe3bb32d 100644
--- a/MediaBrowser.Controller/Entities/InternalItemsQuery.cs
+++ b/MediaBrowser.Controller/Entities/InternalItemsQuery.cs
@@ -39,6 +39,8 @@ namespace MediaBrowser.Controller.Entities
             ImageTypes = Array.Empty<ImageType>();
             IncludeItemTypes = Array.Empty<BaseItemKind>();
             ItemIds = Array.Empty<Guid>();
+            OwnerIds = Array.Empty<Guid>();
+            ExtraTypes = Array.Empty<ExtraType>();
             MediaTypes = Array.Empty<MediaType>();
             OfficialRatings = Array.Empty<string>();
             OrderBy = Array.Empty<(ItemSortBy, SortOrder)>();
@@ -133,6 +135,10 @@ namespace MediaBrowser.Controller.Entities
 
         public Guid[] ItemIds { get; set; }
 
+        public Guid[] OwnerIds { get; set; }
+
+        public ExtraType[] ExtraTypes { get; set; }
+
         public Guid[] ExcludeItemIds { get; set; }
 
         public Guid? AdjacentTo { get; set; }