Optimize internal querying of UserData, other fixes (#14795)

2 files changed, 86 insertions, 24 deletions

diff --git a/Jellyfin.Server.Implementations/Item/BaseItemRepository.cs b/Jellyfin.Server.Implementations/Item/BaseItemRepository.cs
index 1b55d2dc7..30de711ea 100644
--- a/Jellyfin.Server.Implementations/Item/BaseItemRepository.cs
+++ b/Jellyfin.Server.Implementations/Item/BaseItemRepository.cs
@@ -111,13 +111,15 @@ public sealed class BaseItemRepository
 
         var date = (DateTime?)DateTime.UtcNow;
 
+        var relatedItems = TraverseHirachyDown(id, context).ToArray();
+
         // Remove any UserData entries for the placeholder item that would conflict with the UserData
         // being detached from the item being deleted. This is necessary because, during an update,
         // UserData may be reattached to a new entry, but some entries can be left behind.
         // Ensures there are no duplicate UserId/CustomDataKey combinations for the placeholder.
         context.UserData
             .Join(
-                context.UserData.Where(e => e.ItemId == id),
+                context.UserData.WhereOneOrMany(relatedItems, e => e.ItemId),
                 placeholder => new { placeholder.UserId, placeholder.CustomDataKey },
                 userData => new { userData.UserId, userData.CustomDataKey },
                 (placeholder, userData) => placeholder)
@@ -125,29 +127,31 @@ public sealed class BaseItemRepository
             .ExecuteDelete();
 
         // Detach all user watch data
-        context.UserData.Where(e => e.ItemId == id)
+        context.UserData.WhereOneOrMany(relatedItems, e => e.ItemId)
             .ExecuteUpdate(e => e
                 .SetProperty(f => f.RetentionDate, date)
                 .SetProperty(f => f.ItemId, PlaceholderId));
 
-        context.AncestorIds.Where(e => e.ItemId == id || e.ParentItemId == id).ExecuteDelete();
-        context.AttachmentStreamInfos.Where(e => e.ItemId == id).ExecuteDelete();
-        context.BaseItemImageInfos.Where(e => e.ItemId == id).ExecuteDelete();
-        context.BaseItemMetadataFields.Where(e => e.ItemId == id).ExecuteDelete();
-        context.BaseItemProviders.Where(e => e.ItemId == id).ExecuteDelete();
-        context.BaseItemTrailerTypes.Where(e => e.ItemId == id).ExecuteDelete();
-        context.BaseItems.Where(e => e.Id == id).ExecuteDelete();
-        context.Chapters.Where(e => e.ItemId == id).ExecuteDelete();
-        context.CustomItemDisplayPreferences.Where(e => e.ItemId == id).ExecuteDelete();
-        context.ItemDisplayPreferences.Where(e => e.ItemId == id).ExecuteDelete();
+        context.AncestorIds.WhereOneOrMany(relatedItems, e => e.ItemId).ExecuteDelete();
+        context.AncestorIds.WhereOneOrMany(relatedItems, e => e.ParentItemId).ExecuteDelete();
+        context.AttachmentStreamInfos.WhereOneOrMany(relatedItems, e => e.ItemId).ExecuteDelete();
+        context.BaseItemImageInfos.WhereOneOrMany(relatedItems, e => e.ItemId).ExecuteDelete();
+        context.BaseItemMetadataFields.WhereOneOrMany(relatedItems, e => e.ItemId).ExecuteDelete();
+        context.BaseItemProviders.WhereOneOrMany(relatedItems, e => e.ItemId).ExecuteDelete();
+        context.BaseItemTrailerTypes.WhereOneOrMany(relatedItems, e => e.ItemId).ExecuteDelete();
+        context.BaseItems.WhereOneOrMany(relatedItems, e => e.Id).ExecuteDelete();
+        context.Chapters.WhereOneOrMany(relatedItems, e => e.ItemId).ExecuteDelete();
+        context.CustomItemDisplayPreferences.WhereOneOrMany(relatedItems, e => e.ItemId).ExecuteDelete();
+        context.ItemDisplayPreferences.WhereOneOrMany(relatedItems, e => e.ItemId).ExecuteDelete();
         context.ItemValues.Where(e => e.BaseItemsMap!.Count == 0).ExecuteDelete();
-        context.ItemValuesMap.Where(e => e.ItemId == id).ExecuteDelete();
-        context.KeyframeData.Where(e => e.ItemId == id).ExecuteDelete();
-        context.MediaSegments.Where(e => e.ItemId == id).ExecuteDelete();
-        context.MediaStreamInfos.Where(e => e.ItemId == id).ExecuteDelete();
-        context.PeopleBaseItemMap.Where(e => e.ItemId == id).ExecuteDelete();
-        context.Peoples.Where(e => e.BaseItems!.Count == 0).ExecuteDelete();
-        context.TrickplayInfos.Where(e => e.ItemId == id).ExecuteDelete();
+        context.ItemValuesMap.WhereOneOrMany(relatedItems, e => e.ItemId).ExecuteDelete();
+        context.KeyframeData.WhereOneOrMany(relatedItems, e => e.ItemId).ExecuteDelete();
+        context.MediaSegments.WhereOneOrMany(relatedItems, e => e.ItemId).ExecuteDelete();
+        context.MediaStreamInfos.WhereOneOrMany(relatedItems, e => e.ItemId).ExecuteDelete();
+        var query = context.PeopleBaseItemMap.WhereOneOrMany(relatedItems, e => e.ItemId).Select(f => f.PeopleId).Distinct().ToArray();
+        context.PeopleBaseItemMap.WhereOneOrMany(relatedItems, e => e.ItemId).ExecuteDelete();
+        context.Peoples.WhereOneOrMany(query, e => e.Id).Where(e => e.BaseItems!.Count == 0).ExecuteDelete();
+        context.TrickplayInfos.WhereOneOrMany(relatedItems, e => e.ItemId).ExecuteDelete();
         context.SaveChanges();
         transaction.Commit();
     }
@@ -434,7 +438,8 @@ public sealed class BaseItemRepository
         dbQuery = dbQuery.AsSingleQuery()
             .Include(e => e.TrailerTypes)
             .Include(e => e.Provider)
-            .Include(e => e.LockedFields);
+            .Include(e => e.LockedFields)
+            .Include(e => e.UserData);
 
         if (filter.DtoOptions.EnableImages)
         {
@@ -745,8 +750,9 @@ public sealed class BaseItemRepository
     /// <param name="entity">The entity.</param>
     /// <param name="dto">The dto base instance.</param>
     /// <param name="appHost">The Application server Host.</param>
+    /// <param name="logger">The applogger.</param>
     /// <returns>The dto to map.</returns>
-    public static BaseItemDto Map(BaseItemEntity entity, BaseItemDto dto, IServerApplicationHost? appHost)
+    public static BaseItemDto Map(BaseItemEntity entity, BaseItemDto dto, IServerApplicationHost? appHost, ILogger logger)
     {
         dto.Id = entity.Id;
         dto.ParentId = entity.ParentId.GetValueOrDefault();
@@ -791,6 +797,8 @@ public sealed class BaseItemRepository
         dto.OwnerId = string.IsNullOrWhiteSpace(entity.OwnerId) ? Guid.Empty : (Guid.TryParse(entity.OwnerId, out var ownerId) ? ownerId : Guid.Empty);
         dto.Width = entity.Width.GetValueOrDefault();
         dto.Height = entity.Height.GetValueOrDefault();
+        dto.UserData = entity.UserData;
+
         if (entity.Provider is not null)
         {
             dto.ProviderIds = entity.Provider.ToDictionary(e => e.ProviderId, e => e.ProviderValue);
@@ -1144,7 +1152,7 @@ public sealed class BaseItemRepository
             dto = Activator.CreateInstance(type) as BaseItemDto ?? throw new InvalidOperationException("Cannot deserialize unknown type.");
         }
 
-        return Map(baseItemEntity, dto, appHost);
+        return Map(baseItemEntity, dto, appHost, logger);
     }
 
     private QueryResult<(BaseItemDto Item, ItemCounts? ItemCounts)> GetItemValues(InternalItemsQuery filter, IReadOnlyList<ItemValueType> itemValueTypes, string returnType)
@@ -2449,4 +2457,56 @@ public sealed class BaseItemRepository
             return await dbContext.BaseItems.AnyAsync(f => f.Id == id).ConfigureAwait(false);
         }
     }
+
+    /// <inheritdoc/>
+    public bool GetIsPlayed(User user, Guid id, bool recursive)
+    {
+        using var dbContext = _dbProvider.CreateDbContext();
+
+        if (recursive)
+        {
+            var folderList = TraverseHirachyDown(id, dbContext, item => (item.IsFolder || item.IsVirtualItem));
+
+            return dbContext.BaseItems
+                    .Where(e => folderList.Contains(e.ParentId!.Value) && !e.IsFolder && !e.IsVirtualItem)
+                    .All(f => f.UserData!.Any(e => e.UserId == user.Id && e.Played));
+        }
+
+        return dbContext.BaseItems.Where(e => e.ParentId == id).All(f => f.UserData!.Any(e => e.UserId == user.Id && e.Played));
+    }
+
+    private static HashSet<Guid> TraverseHirachyDown(Guid parentId, JellyfinDbContext dbContext, Expression<Func<BaseItemEntity, bool>>? filter = null)
+    {
+        var folderStack = new HashSet<Guid>()
+            {
+                parentId
+            };
+        var folderList = new HashSet<Guid>()
+            {
+                parentId
+            };
+
+        while (folderStack.Count != 0)
+        {
+            var items = folderStack.ToArray();
+            folderStack.Clear();
+            var query = dbContext.BaseItems
+                .WhereOneOrMany(items, e => e.ParentId!.Value);
+
+            if (filter != null)
+            {
+                query = query.Where(filter);
+            }
+
+            foreach (var item in query.Select(e => e.Id).ToArray())
+            {
+                if (folderList.Add(item))
+                {
+                    folderStack.Add(item);
+                }
+            }
+        }
+
+        return folderList;
+    }
 }
diff --git a/Jellyfin.Server.Implementations/Item/PeopleRepository.cs b/Jellyfin.Server.Implementations/Item/PeopleRepository.cs
index be58e2a52..b52de5dd1 100644
--- a/Jellyfin.Server.Implementations/Item/PeopleRepository.cs
+++ b/Jellyfin.Server.Implementations/Item/PeopleRepository.cs
@@ -68,11 +68,12 @@ public class PeopleRepository(IDbContextFactory<JellyfinDbContext> dbProvider, I
     /// <inheritdoc />
     public void UpdatePeople(Guid itemId, IReadOnlyList<PersonInfo> people)
     {
-        using var context = _dbProvider.CreateDbContext();
-
         // TODO: yes for __SOME__ reason there can be duplicates.
         people = people.DistinctBy(e => e.Id).ToArray();
         var personids = people.Select(f => f.Id);
+
+        using var context = _dbProvider.CreateDbContext();
+        using var transaction = context.Database.BeginTransaction();
         var existingPersons = context.Peoples.Where(p => personids.Contains(p.Id)).Select(f => f.Id).ToArray();
         context.Peoples.AddRange(people.Where(e => !existingPersons.Contains(e.Id)).Select(Map));
         context.SaveChanges();
@@ -104,6 +105,7 @@ public class PeopleRepository(IDbContextFactory<JellyfinDbContext> dbProvider, I
         context.PeopleBaseItemMap.RemoveRange(maps);
 
         context.SaveChanges();
+        transaction.Commit();
     }
 
     private PersonInfo Map(People people)