From 077fa89717957f871b172ca4b2dc4a178efd3bc5 Mon Sep 17 00:00:00 2001 From: Shadowghost Date: Sat, 7 Mar 2026 20:12:42 +0100 Subject: Split BaseItemRepository and IItemRepository --- .../Item/BaseItemRepository.QueryBuilding.cs | 541 +++++++++++++++++++++ 1 file changed, 541 insertions(+) create mode 100644 Jellyfin.Server.Implementations/Item/BaseItemRepository.QueryBuilding.cs (limited to 'Jellyfin.Server.Implementations/Item/BaseItemRepository.QueryBuilding.cs') diff --git a/Jellyfin.Server.Implementations/Item/BaseItemRepository.QueryBuilding.cs b/Jellyfin.Server.Implementations/Item/BaseItemRepository.QueryBuilding.cs new file mode 100644 index 0000000000..d4cdfdbc3e --- /dev/null +++ b/Jellyfin.Server.Implementations/Item/BaseItemRepository.QueryBuilding.cs @@ -0,0 +1,541 @@ +#pragma warning disable RS0030 // Do not use banned APIs +#pragma warning disable CA1304 // Specify CultureInfo +#pragma warning disable CA1311 // Specify a culture or use an invariant version +#pragma warning disable CA1862 // Use the 'StringComparison' method overloads to perform case-insensitive string comparisons + +using System; +using System.Collections.Generic; +using System.Linq; +using System.Linq.Expressions; +using Jellyfin.Data.Enums; +using Jellyfin.Database.Implementations; +using Jellyfin.Database.Implementations.Entities; +using Jellyfin.Database.Implementations.Enums; +using Jellyfin.Extensions; +using MediaBrowser.Controller.Entities; +using MediaBrowser.Model.Entities; +using MediaBrowser.Model.Querying; +using Microsoft.EntityFrameworkCore; +using BaseItemEntity = Jellyfin.Database.Implementations.Entities.BaseItemEntity; + +namespace Jellyfin.Server.Implementations.Item; + +public sealed partial class BaseItemRepository +{ + /// + public IQueryable PrepareItemQuery(JellyfinDbContext context, InternalItemsQuery filter) + { + IQueryable dbQuery = context.BaseItems.AsNoTracking(); + dbQuery = dbQuery.AsSingleQuery(); + + return dbQuery; + } + + private IQueryable ApplyQueryFilter(IQueryable dbQuery, JellyfinDbContext context, InternalItemsQuery filter) + { + dbQuery = TranslateQuery(dbQuery, context, filter); + dbQuery = ApplyGroupingFilter(context, dbQuery, filter); + dbQuery = ApplyQueryPaging(dbQuery, filter); + dbQuery = ApplyNavigations(dbQuery, filter); + return dbQuery; + } + + private IQueryable ApplyQueryPaging(IQueryable dbQuery, InternalItemsQuery filter) + { + if (filter.Limit.HasValue || filter.StartIndex.HasValue) + { + var offset = filter.StartIndex ?? 0; + + if (offset > 0) + { + dbQuery = dbQuery.Skip(offset); + } + + if (filter.Limit.HasValue) + { + dbQuery = dbQuery.Take(filter.Limit.Value); + } + } + + return dbQuery; + } + + private IQueryable ApplyGroupingFilter(JellyfinDbContext context, IQueryable dbQuery, InternalItemsQuery filter) + { + // This whole block is needed to filter duplicate entries on request + // for the time being it cannot be used because it would destroy the ordering + // this results in "duplicate" responses for queries that try to lookup individual series or multiple versions but + // for that case the invoker has to run a DistinctBy(e => e.PresentationUniqueKey) on their own + + var enableGroupByPresentationUniqueKey = EnableGroupByPresentationUniqueKey(filter); + if (enableGroupByPresentationUniqueKey && filter.GroupBySeriesPresentationUniqueKey) + { + var tempQuery = dbQuery.GroupBy(e => new { e.PresentationUniqueKey, e.SeriesPresentationUniqueKey }).Select(e => e.OrderBy(x => x.Id).FirstOrDefault()).Select(e => e!.Id); + dbQuery = context.BaseItems.Where(e => tempQuery.Contains(e.Id)); + } + else if (enableGroupByPresentationUniqueKey) + { + var tempQuery = dbQuery.GroupBy(e => e.PresentationUniqueKey).Select(e => e.OrderBy(x => x.Id).FirstOrDefault()).Select(e => e!.Id); + dbQuery = context.BaseItems.Where(e => tempQuery.Contains(e.Id)); + } + else if (filter.GroupBySeriesPresentationUniqueKey) + { + var tempQuery = dbQuery.GroupBy(e => e.SeriesPresentationUniqueKey).Select(e => e.OrderBy(x => x.Id).FirstOrDefault()).Select(e => e!.Id); + dbQuery = context.BaseItems.Where(e => tempQuery.Contains(e.Id)); + } + else + { + dbQuery = dbQuery.Distinct(); + } + + if (filter.CollapseBoxSetItems == true) + { + dbQuery = ApplyBoxSetCollapsing(context, dbQuery, filter.CollapseBoxSetItemTypes); + + // Apply name-range filters after collapse so BoxSets are filtered by their own name, + // not by their children's names. + dbQuery = ApplyNameFilters(dbQuery, filter); + } + + dbQuery = ApplyOrder(dbQuery, filter, context); + + return dbQuery; + } + + private IQueryable ApplyBoxSetCollapsing( + JellyfinDbContext context, + IQueryable dbQuery, + BaseItemKind[] collapsibleTypes) + { + var boxSetTypeName = _itemTypeLookup.BaseItemKindNames[BaseItemKind.BoxSet]; + + var currentIds = dbQuery.Select(e => e.Id); + + if (collapsibleTypes.Length == 0) + { + // Collapse all item types into box sets + return ApplyBoxSetCollapsingAll(context, currentIds, boxSetTypeName); + } + + // Only collapse specific item types, keep others untouched + var collapsibleTypeNames = collapsibleTypes.Select(t => _itemTypeLookup.BaseItemKindNames[t]).ToList(); + + // Items whose type is NOT collapsible (always kept in results) + var nonCollapsibleIds = currentIds + .Where(id => !context.BaseItems.Any(bi => bi.Id == id && collapsibleTypeNames.Contains(bi.Type))); + + // Collapsible items that are NOT in any box set (kept in results) + var collapsibleNotInBoxSet = currentIds + .Where(id => + context.BaseItems.Any(bi => bi.Id == id && collapsibleTypeNames.Contains(bi.Type)) + && !context.BaseItems.Any(bs => bs.Id == id && bs.Type == boxSetTypeName) + && !context.LinkedChildren.Any(lc => + lc.ChildId == id + && lc.ChildType == Jellyfin.Database.Implementations.Entities.LinkedChildType.Manual + && context.BaseItems.Any(bs => bs.Id == lc.ParentId && bs.Type == boxSetTypeName))); + + // Box set IDs containing at least one accessible collapsible child item + var boxSetIds = context.LinkedChildren + .Where(lc => + lc.ChildType == Jellyfin.Database.Implementations.Entities.LinkedChildType.Manual + && currentIds.Contains(lc.ChildId) + && context.BaseItems.Any(bi => bi.Id == lc.ChildId && collapsibleTypeNames.Contains(bi.Type)) + && context.BaseItems.Any(bs => bs.Id == lc.ParentId && bs.Type == boxSetTypeName)) + .Select(lc => lc.ParentId) + .Distinct(); + + var collapsedIds = nonCollapsibleIds.Union(collapsibleNotInBoxSet).Union(boxSetIds); + return context.BaseItems.Where(e => collapsedIds.Contains(e.Id)); + } + + private static IQueryable ApplyBoxSetCollapsingAll( + JellyfinDbContext context, + IQueryable currentIds, + string boxSetTypeName) + { + // Items that are NOT box sets and NOT in any box set + var notInBoxSet = currentIds + .Where(id => + !context.BaseItems.Any(bs => bs.Id == id && bs.Type == boxSetTypeName) + && !context.LinkedChildren.Any(lc => + lc.ChildId == id + && lc.ChildType == Jellyfin.Database.Implementations.Entities.LinkedChildType.Manual + && context.BaseItems.Any(bs => bs.Id == lc.ParentId && bs.Type == boxSetTypeName))); + + // Box set IDs containing at least one accessible child item + var boxSetIds = context.LinkedChildren + .Where(lc => + lc.ChildType == Jellyfin.Database.Implementations.Entities.LinkedChildType.Manual + && currentIds.Contains(lc.ChildId) + && context.BaseItems.Any(bs => bs.Id == lc.ParentId && bs.Type == boxSetTypeName)) + .Select(lc => lc.ParentId) + .Distinct(); + + var collapsedIds = notInBoxSet.Union(boxSetIds); + return context.BaseItems.Where(e => collapsedIds.Contains(e.Id)); + } + + private static IQueryable ApplyNameFilters(IQueryable dbQuery, InternalItemsQuery filter) + { + if (!string.IsNullOrWhiteSpace(filter.NameStartsWith)) + { + var nameStartsWithLower = filter.NameStartsWith.ToLowerInvariant(); + dbQuery = dbQuery.Where(e => e.SortName!.ToLower().StartsWith(nameStartsWithLower)); + } + + if (!string.IsNullOrWhiteSpace(filter.NameStartsWithOrGreater)) + { + var startsOrGreaterLower = filter.NameStartsWithOrGreater.ToLowerInvariant(); + dbQuery = dbQuery.Where(e => e.SortName!.ToLower().CompareTo(startsOrGreaterLower) >= 0); + } + + if (!string.IsNullOrWhiteSpace(filter.NameLessThan)) + { + var lessThanLower = filter.NameLessThan.ToLowerInvariant(); + dbQuery = dbQuery.Where(e => e.SortName!.ToLower().CompareTo(lessThanLower) < 0); + } + + return dbQuery; + } + + /// + public IQueryable ApplyNavigations(IQueryable dbQuery, InternalItemsQuery filter) + { + if (filter.TrailerTypes.Length > 0 || filter.IncludeItemTypes.Contains(BaseItemKind.Trailer)) + { + dbQuery = dbQuery.Include(e => e.TrailerTypes); + } + + if (filter.DtoOptions.ContainsField(ItemFields.ProviderIds)) + { + dbQuery = dbQuery.Include(e => e.Provider); + } + + if (filter.DtoOptions.ContainsField(ItemFields.Settings)) + { + dbQuery = dbQuery.Include(e => e.LockedFields); + } + + if (filter.DtoOptions.EnableUserData) + { + dbQuery = dbQuery.Include(e => e.UserData); + } + + if (filter.DtoOptions.EnableImages) + { + dbQuery = dbQuery.Include(e => e.Images); + } + + // Include LinkedChildEntities for container types and videos that use them + // (BoxSet, Playlist, CollectionFolder for manual linking; Video, Movie for alternate versions). + // When IncludeItemTypes is empty (any type may be returned), always include them to ensure + // LinkedChildren are loaded before items are saved back, preventing accidental deletion. + var linkedChildTypes = new[] + { + BaseItemKind.BoxSet, + BaseItemKind.Playlist, + BaseItemKind.CollectionFolder, + BaseItemKind.Video, + BaseItemKind.Movie + }; + if (filter.IncludeItemTypes.Length == 0 || filter.IncludeItemTypes.Any(linkedChildTypes.Contains)) + { + dbQuery = dbQuery.Include(e => e.LinkedChildEntities); + } + + if (filter.IncludeExtras) + { + dbQuery = dbQuery.Include(e => e.Extras); + } + + return dbQuery; + } + + /// + public IQueryable ApplyOrder(IQueryable query, InternalItemsQuery filter, JellyfinDbContext context) + { + var orderBy = filter.OrderBy.Where(e => e.OrderBy != ItemSortBy.Default).ToArray(); + var hasSearch = !string.IsNullOrEmpty(filter.SearchTerm); + + // SeriesDatePlayed requires special handling to avoid correlated subqueries. + // Instead of running a MAX() subquery per-row in ORDER BY, we pre-aggregate + // max played dates per series in one query and left-join it. + if (!hasSearch && orderBy.Any(o => o.OrderBy == ItemSortBy.SeriesDatePlayed)) + { + return ApplySeriesDatePlayedOrder(query, filter, context, orderBy); + } + + IOrderedQueryable? orderedQuery = null; + + if (hasSearch) + { + var relevanceExpression = OrderMapper.MapSearchRelevanceOrder(filter.SearchTerm!); + orderedQuery = query.OrderBy(relevanceExpression); + } + + if (orderBy.Length > 0) + { + var firstOrdering = orderBy[0]; + var expression = OrderMapper.MapOrderByField(firstOrdering.OrderBy, filter, context); + + if (orderedQuery is null) + { + orderedQuery = firstOrdering.SortOrder == SortOrder.Ascending + ? query.OrderBy(expression) + : query.OrderByDescending(expression); + } + else + { + orderedQuery = firstOrdering.SortOrder == SortOrder.Ascending + ? orderedQuery.ThenBy(expression) + : orderedQuery.ThenByDescending(expression); + } + + if (firstOrdering.OrderBy is ItemSortBy.Default or ItemSortBy.SortName) + { + orderedQuery = firstOrdering.SortOrder == SortOrder.Ascending + ? orderedQuery.ThenBy(e => e.Name) + : orderedQuery.ThenByDescending(e => e.Name); + } + + foreach (var item in orderBy.Skip(1)) + { + expression = OrderMapper.MapOrderByField(item.OrderBy, filter, context); + orderedQuery = item.SortOrder == SortOrder.Ascending + ? orderedQuery.ThenBy(expression) + : orderedQuery.ThenByDescending(expression); + } + } + + if (orderedQuery is null) + { + return query.OrderBy(e => e.SortName); + } + + // Add SortName as final tiebreaker + if (!hasSearch && (orderBy.Length == 0 || orderBy.All(o => o.OrderBy is not ItemSortBy.SortName and not ItemSortBy.Name))) + { + orderedQuery = orderedQuery.ThenBy(e => e.SortName); + } + + return orderedQuery; + } + + private IQueryable ApplySeriesDatePlayedOrder( + IQueryable query, + InternalItemsQuery filter, + JellyfinDbContext context, + (ItemSortBy OrderBy, SortOrder SortOrder)[] orderBy) + { + // Pre-aggregate max played date per series key in ONE query. + // This generates a single: SELECT SeriesPresentationUniqueKey, MAX(LastPlayedDate) ... GROUP BY + // instead of a correlated subquery per outer row. + IQueryable userDataQuery = filter.User is not null + ? context.UserData.Where(ud => ud.UserId == filter.User.Id && ud.Played) + : context.UserData.Where(ud => ud.Played); + + var seriesMaxDates = userDataQuery + .Join( + context.BaseItems, + ud => ud.ItemId, + bi => bi.Id, + (ud, bi) => new { bi.SeriesPresentationUniqueKey, ud.LastPlayedDate }) + .Where(x => x.SeriesPresentationUniqueKey != null) + .GroupBy(x => x.SeriesPresentationUniqueKey) + .Select(g => new { SeriesKey = g.Key!, MaxDate = g.Max(x => x.LastPlayedDate) }); + + var joined = query.LeftJoin( + seriesMaxDates, + e => e.PresentationUniqueKey, + s => s.SeriesKey, + (e, s) => new { Item = e, MaxDate = s != null ? s.MaxDate : (DateTime?)null }); + + var seriesSort = orderBy.First(o => o.OrderBy == ItemSortBy.SeriesDatePlayed); + + return seriesSort.SortOrder == SortOrder.Ascending + ? joined.OrderBy(x => x.MaxDate).ThenBy(x => x.Item.SortName).Select(x => x.Item) + : joined.OrderByDescending(x => x.MaxDate).ThenBy(x => x.Item.SortName).Select(x => x.Item); + } + + /// + /// Builds a query for descendants of an ancestor with user access filtering applied. + /// Uses recursive CTE to traverse both hierarchical (AncestorIds) and linked (LinkedChildren) relationships. + /// + /// + public IQueryable BuildAccessFilteredDescendantsQuery( + JellyfinDbContext context, + InternalItemsQuery filter, + Guid ancestorId) + { + // Use recursive CTE to get all descendants (hierarchical and linked) + var allDescendantIds = DescendantQueryHelper.GetAllDescendantIds(context, ancestorId); + + var baseQuery = context.BaseItems + .Where(b => allDescendantIds.Contains(b.Id) && !b.IsFolder && !b.IsVirtualItem); + + return ApplyAccessFiltering(context, baseQuery, filter); + } + + /// + /// Applies user access filtering to a query. + /// Includes TopParentIds, parental rating, and tag filtering. + /// + /// + public IQueryable ApplyAccessFiltering( + JellyfinDbContext context, + IQueryable baseQuery, + InternalItemsQuery filter) + { + // Apply TopParentIds filtering (library folder access) + if (filter.TopParentIds.Length > 0) + { + var topParentIds = filter.TopParentIds; + baseQuery = baseQuery.Where(e => topParentIds.Contains(e.TopParentId!.Value)); + } + + // Apply parental rating filtering + if (filter.MaxParentalRating is not null) + { + baseQuery = baseQuery.Where(BuildMaxParentalRatingFilter(context, filter.MaxParentalRating)); + } + + // Apply block unrated items filtering + if (filter.BlockUnratedItems.Length > 0) + { + var unratedItemTypes = filter.BlockUnratedItems.Select(f => f.ToString()).ToArray(); + baseQuery = baseQuery.Where(e => + e.InheritedParentalRatingValue != null || !unratedItemTypes.Contains(e.UnratedType)); + } + + // Apply excluded tags filtering (blocked tags) + if (filter.ExcludeInheritedTags.Length > 0) + { + var excludedTags = filter.ExcludeInheritedTags.Select(e => e.GetCleanValue()).ToArray(); + baseQuery = baseQuery.Where(e => + !context.ItemValuesMap.Any(f => + f.ItemValue.Type == ItemValueType.Tags + && excludedTags.Contains(f.ItemValue.CleanValue) + && (f.ItemId == e.Id + || (e.SeriesId.HasValue && f.ItemId == e.SeriesId.Value) + || e.Parents!.Any(p => f.ItemId == p.ParentItemId) + || (e.TopParentId.HasValue && f.ItemId == e.TopParentId.Value)))); + } + + // Apply included tags filtering (allowed tags - item must have at least one) + if (filter.IncludeInheritedTags.Length > 0) + { + var includeTags = filter.IncludeInheritedTags.Select(e => e.GetCleanValue()).ToArray(); + baseQuery = baseQuery.Where(e => + context.ItemValuesMap.Any(f => + f.ItemValue.Type == ItemValueType.Tags + && includeTags.Contains(f.ItemValue.CleanValue) + && (f.ItemId == e.Id + || (e.SeriesId.HasValue && f.ItemId == e.SeriesId.Value) + || e.Parents!.Any(p => f.ItemId == p.ParentItemId) + || (e.TopParentId.HasValue && f.ItemId == e.TopParentId.Value)))); + } + + return baseQuery; + } + + /// + /// Builds a filter expression for max parental rating that handles both rated items + /// and unrated BoxSets/Playlists (which check linked children's ratings). + /// + private static Expression> BuildMaxParentalRatingFilter( + JellyfinDbContext context, + ParentalRatingScore maxRating) + { + var maxScore = maxRating.Score; + var maxSubScore = maxRating.SubScore ?? 0; + var linkedChildren = context.LinkedChildren; + + return e => + // Item has a rating: check against limit + (e.InheritedParentalRatingValue != null + && (e.InheritedParentalRatingValue < maxScore + || (e.InheritedParentalRatingValue == maxScore && (e.InheritedParentalRatingSubValue ?? 0) <= maxSubScore))) + // Item has no rating + || (e.InheritedParentalRatingValue == null + && ( + // No linked children (not a BoxSet/Playlist): pass as unrated + !linkedChildren.Any(lc => lc.ParentId == e.Id) + // Has linked children: at least one child must be within limits + || linkedChildren.Any(lc => lc.ParentId == e.Id + && (lc.Child!.InheritedParentalRatingValue == null + || lc.Child.InheritedParentalRatingValue < maxScore + || (lc.Child.InheritedParentalRatingValue == maxScore + && (lc.Child.InheritedParentalRatingSubValue ?? 0) <= maxSubScore))))); + } + + private Dictionary GetPlayedAndTotalCountBatch(IReadOnlyList folderIds, User user) + { + ArgumentNullException.ThrowIfNull(folderIds); + ArgumentNullException.ThrowIfNull(user); + + if (folderIds.Count == 0) + { + return new Dictionary(); + } + + using var dbContext = _dbProvider.CreateDbContext(); + var folderIdsArray = folderIds.ToArray(); + var filter = new InternalItemsQuery(user); + var userId = user.Id; + + var leafItems = dbContext.BaseItems + .Where(b => !b.IsFolder && !b.IsVirtualItem); + leafItems = ApplyAccessFiltering(dbContext, leafItems, filter); + + var playedLeafItems = leafItems + .Select(b => new { b.Id, Played = b.UserData!.Any(ud => ud.UserId == userId && ud.Played) }); + + var ancestorLeaves = dbContext.AncestorIds + .WhereOneOrMany(folderIdsArray, a => a.ParentItemId) + .Join( + playedLeafItems, + a => a.ItemId, + b => b.Id, + (a, b) => new { FolderId = a.ParentItemId, b.Id, b.Played }); + + var linkedLeaves = dbContext.LinkedChildren + .WhereOneOrMany(folderIdsArray, lc => lc.ParentId) + .Join( + playedLeafItems, + lc => lc.ChildId, + b => b.Id, + (lc, b) => new { FolderId = lc.ParentId, b.Id, b.Played }); + + var linkedFolderLeaves = dbContext.LinkedChildren + .WhereOneOrMany(folderIdsArray, lc => lc.ParentId) + .Join( + dbContext.BaseItems.Where(b => b.IsFolder), + lc => lc.ChildId, + b => b.Id, + (lc, b) => new { lc.ParentId, FolderChildId = b.Id }) + .Join( + dbContext.AncestorIds, + x => x.FolderChildId, + a => a.ParentItemId, + (x, a) => new { x.ParentId, DescendantId = a.ItemId }) + .Join( + playedLeafItems, + x => x.DescendantId, + b => b.Id, + (x, b) => new { FolderId = x.ParentId, b.Id, b.Played }); + + var results = ancestorLeaves + .Union(linkedLeaves) + .Union(linkedFolderLeaves) + .GroupBy(x => x.FolderId) + .Select(g => new + { + FolderId = g.Key, + Total = g.Select(x => x.Id).Distinct().Count(), + Played = g.Where(x => x.Played).Select(x => x.Id).Distinct().Count() + }) + .ToDictionary(x => x.FolderId, x => (x.Played, x.Total)); + + return results; + } +} -- cgit v1.2.3 From ea1c1d046856ab46e6bda1903c97702292e61ff9 Mon Sep 17 00:00:00 2001 From: Shadowghost Date: Sat, 7 Mar 2026 20:14:04 +0100 Subject: Optimize grouping query performance --- .../Item/BaseItemRepository.QueryBuilding.cs | 8 +++++--- .../Item/BaseItemRepository.Querying.cs | 1 + 2 files changed, 6 insertions(+), 3 deletions(-) (limited to 'Jellyfin.Server.Implementations/Item/BaseItemRepository.QueryBuilding.cs') diff --git a/Jellyfin.Server.Implementations/Item/BaseItemRepository.QueryBuilding.cs b/Jellyfin.Server.Implementations/Item/BaseItemRepository.QueryBuilding.cs index d4cdfdbc3e..b1f7326d06 100644 --- a/Jellyfin.Server.Implementations/Item/BaseItemRepository.QueryBuilding.cs +++ b/Jellyfin.Server.Implementations/Item/BaseItemRepository.QueryBuilding.cs @@ -68,19 +68,21 @@ public sealed partial class BaseItemRepository // for that case the invoker has to run a DistinctBy(e => e.PresentationUniqueKey) on their own var enableGroupByPresentationUniqueKey = EnableGroupByPresentationUniqueKey(filter); + // Use Min(Id) instead of OrderBy(Id).FirstOrDefault() to avoid EF Core generating + // a correlated scalar subquery per group. if (enableGroupByPresentationUniqueKey && filter.GroupBySeriesPresentationUniqueKey) { - var tempQuery = dbQuery.GroupBy(e => new { e.PresentationUniqueKey, e.SeriesPresentationUniqueKey }).Select(e => e.OrderBy(x => x.Id).FirstOrDefault()).Select(e => e!.Id); + var tempQuery = dbQuery.GroupBy(e => new { e.PresentationUniqueKey, e.SeriesPresentationUniqueKey }).Select(e => e.Min(x => x.Id)); dbQuery = context.BaseItems.Where(e => tempQuery.Contains(e.Id)); } else if (enableGroupByPresentationUniqueKey) { - var tempQuery = dbQuery.GroupBy(e => e.PresentationUniqueKey).Select(e => e.OrderBy(x => x.Id).FirstOrDefault()).Select(e => e!.Id); + var tempQuery = dbQuery.GroupBy(e => e.PresentationUniqueKey).Select(e => e.Min(x => x.Id)); dbQuery = context.BaseItems.Where(e => tempQuery.Contains(e.Id)); } else if (filter.GroupBySeriesPresentationUniqueKey) { - var tempQuery = dbQuery.GroupBy(e => e.SeriesPresentationUniqueKey).Select(e => e.OrderBy(x => x.Id).FirstOrDefault()).Select(e => e!.Id); + var tempQuery = dbQuery.GroupBy(e => e.SeriesPresentationUniqueKey).Select(e => e.Min(x => x.Id)); dbQuery = context.BaseItems.Where(e => tempQuery.Contains(e.Id)); } else diff --git a/Jellyfin.Server.Implementations/Item/BaseItemRepository.Querying.cs b/Jellyfin.Server.Implementations/Item/BaseItemRepository.Querying.cs index 3487fa09d7..f1899cd2de 100644 --- a/Jellyfin.Server.Implementations/Item/BaseItemRepository.Querying.cs +++ b/Jellyfin.Server.Implementations/Item/BaseItemRepository.Querying.cs @@ -88,6 +88,7 @@ public sealed partial class BaseItemRepository } var itemsById = ApplyNavigations(context.BaseItems.Where(e => orderedIds.Contains(e.Id)), filter) + .AsSplitQuery() .AsEnumerable() .Select(w => DeserializeBaseItem(w, filter.SkipDeserialization)) .Where(dto => dto != null) -- cgit v1.2.3 From ba722b45175a15b66d6c934d80a50bbb1ed6e695 Mon Sep 17 00:00:00 2001 From: Shadowghost Date: Sun, 8 Mar 2026 15:10:01 +0100 Subject: Optimize Search and NextUp queries --- Jellyfin.Api/Controllers/ItemsController.cs | 9 +- Jellyfin.Api/Controllers/TrailersController.cs | 7 +- .../Item/BaseItemRepository.ByName.cs | 67 +- .../Item/BaseItemRepository.QueryBuilding.cs | 18 +- .../Item/BaseItemRepository.Querying.cs | 17 +- .../Item/NextUpService.cs | 52 +- .../ModelConfiguration/BaseItemConfiguration.cs | 2 + ...0260308123920_AddTypeCleanNameIndex.Designer.cs | 1793 ++++++++++++++++++++ .../20260308123920_AddTypeCleanNameIndex.cs | 27 + .../Migrations/JellyfinDbModelSnapshot.cs | 2 + 10 files changed, 1920 insertions(+), 74 deletions(-) create mode 100644 src/Jellyfin.Database/Jellyfin.Database.Providers.Sqlite/Migrations/20260308123920_AddTypeCleanNameIndex.Designer.cs create mode 100644 src/Jellyfin.Database/Jellyfin.Database.Providers.Sqlite/Migrations/20260308123920_AddTypeCleanNameIndex.cs (limited to 'Jellyfin.Server.Implementations/Item/BaseItemRepository.QueryBuilding.cs') diff --git a/Jellyfin.Api/Controllers/ItemsController.cs b/Jellyfin.Api/Controllers/ItemsController.cs index f8c715dc86..63950d96d7 100644 --- a/Jellyfin.Api/Controllers/ItemsController.cs +++ b/Jellyfin.Api/Controllers/ItemsController.cs @@ -1,6 +1,7 @@ using System; using System.ComponentModel.DataAnnotations; using System.Linq; +using System.Threading.Tasks; using Jellyfin.Api.Extensions; using Jellyfin.Api.Helpers; using Jellyfin.Api.ModelBinders; @@ -159,7 +160,7 @@ public class ItemsController : BaseJellyfinApiController /// A with the items. [HttpGet("Items")] [ProducesResponseType(StatusCodes.Status200OK)] - public ActionResult> GetItems( + public async Task>> GetItems( [FromQuery] Guid? userId, [FromQuery] string? maxOfficialRating, [FromQuery] bool? hasThemeSong, @@ -626,7 +627,7 @@ public class ItemsController : BaseJellyfinApiController [Obsolete("Kept for backwards compatibility")] [ApiExplorerSettings(IgnoreApi = true)] [ProducesResponseType(StatusCodes.Status200OK)] - public ActionResult> GetItemsByUserIdLegacy( + public async Task>> GetItemsByUserIdLegacy( [FromRoute] Guid userId, [FromQuery] string? maxOfficialRating, [FromQuery] bool? hasThemeSong, @@ -712,7 +713,7 @@ public class ItemsController : BaseJellyfinApiController [FromQuery, ModelBinder(typeof(CommaDelimitedCollectionModelBinder))] Guid[] genreIds, [FromQuery] bool enableTotalRecordCount = true, [FromQuery] bool? enableImages = true) - => GetItems( + => await GetItems( userId, maxOfficialRating, hasThemeSong, @@ -798,7 +799,7 @@ public class ItemsController : BaseJellyfinApiController studioIds, genreIds, enableTotalRecordCount, - enableImages); + enableImages).ConfigureAwait(false); /// /// Gets items based on a query. diff --git a/Jellyfin.Api/Controllers/TrailersController.cs b/Jellyfin.Api/Controllers/TrailersController.cs index 3e4bac89a5..99ff3a21ee 100644 --- a/Jellyfin.Api/Controllers/TrailersController.cs +++ b/Jellyfin.Api/Controllers/TrailersController.cs @@ -1,4 +1,5 @@ using System; +using System.Threading.Tasks; using Jellyfin.Api.ModelBinders; using Jellyfin.Data.Enums; using Jellyfin.Database.Implementations.Enums; @@ -118,7 +119,7 @@ public class TrailersController : BaseJellyfinApiController /// A with the trailers. [HttpGet] [ProducesResponseType(StatusCodes.Status200OK)] - public ActionResult> GetTrailers( + public async Task>> GetTrailers( [FromQuery] Guid? userId, [FromQuery] string? maxOfficialRating, [FromQuery] bool? hasThemeSong, @@ -206,7 +207,7 @@ public class TrailersController : BaseJellyfinApiController { var includeItemTypes = new[] { BaseItemKind.Trailer }; - return _itemsController + return await _itemsController .GetItems( userId, maxOfficialRating, @@ -293,6 +294,6 @@ public class TrailersController : BaseJellyfinApiController studioIds, genreIds, enableTotalRecordCount, - enableImages); + enableImages).ConfigureAwait(false); } } diff --git a/Jellyfin.Server.Implementations/Item/BaseItemRepository.ByName.cs b/Jellyfin.Server.Implementations/Item/BaseItemRepository.ByName.cs index 0a8f8627b4..907d8527aa 100644 --- a/Jellyfin.Server.Implementations/Item/BaseItemRepository.ByName.cs +++ b/Jellyfin.Server.Implementations/Item/BaseItemRepository.ByName.cs @@ -99,10 +99,9 @@ public sealed partial class BaseItemRepository query = query.Where(e => !excludeItemTypes.Contains(e.Item.Type)); } - // query = query.DistinctBy(e => e.CleanValue); return query.Select(e => e.ItemValue) .GroupBy(e => e.CleanValue) - .Select(e => e.OrderBy(v => v.Value).First().Value) + .Select(g => g.Min(v => v.Value)!) .ToArray(); } @@ -133,17 +132,22 @@ public sealed partial class BaseItemRepository IsNews = filter.IsNews, IsSeries = filter.IsSeries }); - var itemValuesQuery = context.ItemValuesMap + + // Materialize the matching CleanValues early. This splits one massive expression tree + // into two simpler queries, dramatically reducing EF Core expression compilation time. + var matchingCleanValues = context.ItemValuesMap .Where(ivm => itemValueTypes.Contains(ivm.ItemValue.Type)) .Join( innerQueryFilter, ivm => ivm.ItemId, g => g.Id, - (ivm, g) => ivm.ItemValue.CleanValue); + (ivm, g) => ivm.ItemValue.CleanValue) + .Distinct() + .ToList(); var innerQuery = PrepareItemQuery(context, filter) .Where(e => e.Type == returnType) - .Where(e => itemValuesQuery.Contains(e.CleanName)); + .Where(e => matchingCleanValues.Contains(e.CleanName!)); var outerQueryFilter = new InternalItemsQuery(filter.User) { @@ -166,43 +170,40 @@ public sealed partial class BaseItemRepository ExcludeItemIds = filter.ExcludeItemIds }; - var masterQuery = TranslateQuery(innerQuery, context, outerQueryFilter) - .GroupBy(e => e.PresentationUniqueKey) - .Select(e => e.OrderBy(x => x.Id).FirstOrDefault()) - .Select(e => e!.Id); - - var query = context.BaseItems - .Include(e => e.TrailerTypes) - .Include(e => e.Provider) - .Include(e => e.LockedFields) - .Include(e => e.Images) - .Include(e => e.LinkedChildEntities) - .AsSingleQuery() - .Where(e => masterQuery.Contains(e.Id)); + // Materialize the matching IDs first. This keeps the complex nested subquery + // (inner filter + ItemValues join + search + GroupBy) as a single simple SQL statement, + // and then the entity load with Includes uses a flat WHERE Id IN (...) list. + // This avoids EF having to compile the entire nested expression tree into the final query. + var masterQuery = TranslateQuery(innerQuery, context, outerQueryFilter); - query = ApplyOrder(query, filter, context); + var orderedMasterQuery = ApplyOrder(masterQuery, filter, context) + .GroupBy(e => e.PresentationUniqueKey) + .Select(g => g.Min(e => e.Id)); var result = new QueryResult<(BaseItemDto, ItemCounts?)>(); if (filter.EnableTotalRecordCount) { - result.TotalRecordCount = query.Count(); + result.TotalRecordCount = orderedMasterQuery.Count(); } - if (filter.Limit.HasValue || filter.StartIndex.HasValue) + if (filter.StartIndex.HasValue && filter.StartIndex.Value > 0) { - var offset = filter.StartIndex ?? 0; - - if (offset > 0) - { - query = query.Skip(offset); - } + orderedMasterQuery = orderedMasterQuery.Skip(filter.StartIndex.Value); + } - if (filter.Limit.HasValue) - { - query = query.Take(filter.Limit.Value); - } + if (filter.Limit.HasValue) + { + orderedMasterQuery = orderedMasterQuery.Take(filter.Limit.Value); } + var masterIds = orderedMasterQuery.ToList(); + + var query = ApplyNavigations( + context.BaseItems.AsSingleQuery().Where(e => masterIds.Contains(e.Id)), + filter); + + query = ApplyOrder(query, filter, context); + if (filter.IncludeItemTypes.Length > 0) { var typeSubQuery = new InternalItemsQuery(filter.User) @@ -229,8 +230,8 @@ public sealed partial class BaseItemRepository var audioTypeName = _itemTypeLookup.BaseItemKindNames[BaseItemKind.Audio]; var trailerTypeName = _itemTypeLookup.BaseItemKindNames[BaseItemKind.Trailer]; - // Get the IDs from itemCountQuery to use in the join - var itemIds = itemCountQuery.Select(e => e.Id); + // Materialize the matching IDs to avoid nested subquery in the counts expression tree. + var itemIds = itemCountQuery.Select(e => e.Id).ToList(); // Rewrite query to avoid SelectMany on navigation properties (which requires SQL APPLY, not supported on SQLite) // Instead, start from ItemValueMaps and join with BaseItems diff --git a/Jellyfin.Server.Implementations/Item/BaseItemRepository.QueryBuilding.cs b/Jellyfin.Server.Implementations/Item/BaseItemRepository.QueryBuilding.cs index b1f7326d06..83f108bed0 100644 --- a/Jellyfin.Server.Implementations/Item/BaseItemRepository.QueryBuilding.cs +++ b/Jellyfin.Server.Implementations/Item/BaseItemRepository.QueryBuilding.cs @@ -68,22 +68,24 @@ public sealed partial class BaseItemRepository // for that case the invoker has to run a DistinctBy(e => e.PresentationUniqueKey) on their own var enableGroupByPresentationUniqueKey = EnableGroupByPresentationUniqueKey(filter); - // Use Min(Id) instead of OrderBy(Id).FirstOrDefault() to avoid EF Core generating - // a correlated scalar subquery per group. + // Materialize GroupBy IDs first to split the complex expression tree. + // This runs the filter+GroupBy+Min as one simple SQL query, then the downstream + // Order/Paging/Navigations work on a flat WHERE Id IN (...) list, avoiding + // EF Core having to compile a deeply nested expression tree. if (enableGroupByPresentationUniqueKey && filter.GroupBySeriesPresentationUniqueKey) { - var tempQuery = dbQuery.GroupBy(e => new { e.PresentationUniqueKey, e.SeriesPresentationUniqueKey }).Select(e => e.Min(x => x.Id)); - dbQuery = context.BaseItems.Where(e => tempQuery.Contains(e.Id)); + var groupedIds = dbQuery.GroupBy(e => new { e.PresentationUniqueKey, e.SeriesPresentationUniqueKey }).Select(e => e.Min(x => x.Id)).ToList(); + dbQuery = context.BaseItems.Where(e => groupedIds.Contains(e.Id)); } else if (enableGroupByPresentationUniqueKey) { - var tempQuery = dbQuery.GroupBy(e => e.PresentationUniqueKey).Select(e => e.Min(x => x.Id)); - dbQuery = context.BaseItems.Where(e => tempQuery.Contains(e.Id)); + var groupedIds = dbQuery.GroupBy(e => e.PresentationUniqueKey).Select(e => e.Min(x => x.Id)).ToList(); + dbQuery = context.BaseItems.Where(e => groupedIds.Contains(e.Id)); } else if (filter.GroupBySeriesPresentationUniqueKey) { - var tempQuery = dbQuery.GroupBy(e => e.SeriesPresentationUniqueKey).Select(e => e.Min(x => x.Id)); - dbQuery = context.BaseItems.Where(e => tempQuery.Contains(e.Id)); + var groupedIds = dbQuery.GroupBy(e => e.SeriesPresentationUniqueKey).Select(e => e.Min(x => x.Id)).ToList(); + dbQuery = context.BaseItems.Where(e => groupedIds.Contains(e.Id)); } else { diff --git a/Jellyfin.Server.Implementations/Item/BaseItemRepository.Querying.cs b/Jellyfin.Server.Implementations/Item/BaseItemRepository.Querying.cs index f1899cd2de..7ca3559324 100644 --- a/Jellyfin.Server.Implementations/Item/BaseItemRepository.Querying.cs +++ b/Jellyfin.Server.Implementations/Item/BaseItemRepository.Querying.cs @@ -181,7 +181,7 @@ public sealed partial class BaseItemRepository var firstIds = allItemsLite .DistinctBy(e => e.GroupKey) .Select(e => e.Id) - .AsEnumerable(); + .ToList(); var itemsQuery = context.BaseItems.AsNoTracking().Where(e => firstIds.Contains(e.Id)); itemsQuery = ApplyNavigations(itemsQuery, filter); @@ -236,13 +236,18 @@ public sealed partial class BaseItemRepository topSeriesWithDates = topSeriesWithDates.Take(limit.Value).OrderByDescending(g => g.MaxDate); } - var topSeriesNames = topSeriesWithDates.Select(g => g.SeriesName).AsEnumerable(); + // Materialize series names and cutoff to avoid embedding the GroupBy+OrderBy + // expression tree as a subquery inside the episode query. + var topSeriesData = topSeriesWithDates + .Select(g => new { g.SeriesName, g.MaxDate }) + .ToList(); + var topSeriesNames = topSeriesData.Select(g => g.SeriesName).ToList(); // Compute a global date cutoff: the oldest series' max date minus the window. // Episodes before this cutoff cannot be in any series' "recent additions" window, // so we can safely exclude them to avoid loading ancient episodes. - var globalCutoff = topSeriesWithDates.Any() - ? topSeriesWithDates.Min(g => g.MaxDate)?.AddHours(-RecentAdditionWindowHours) + var globalCutoff = topSeriesData.Count > 0 + ? topSeriesData.Min(g => g.MaxDate)?.AddHours(-RecentAdditionWindowHours) : null; // Fetch only the columns needed for analysis (lightweight projection). @@ -530,7 +535,7 @@ public sealed partial class BaseItemRepository .Where(ivm => matchingItemIds.Contains(ivm.ItemId)) .Select(ivm => ivm.ItemValue) .GroupBy(iv => iv.CleanValue) - .Select(g => g.OrderBy(iv => iv.Value).First().Value) + .Select(g => g.Min(iv => iv.Value)) .OrderBy(t => t) .ToArray(); @@ -539,7 +544,7 @@ public sealed partial class BaseItemRepository .Where(ivm => matchingItemIds.Contains(ivm.ItemId)) .Select(ivm => ivm.ItemValue) .GroupBy(iv => iv.CleanValue) - .Select(g => g.OrderBy(iv => iv.Value).First().Value) + .Select(g => g.Min(iv => iv.Value)) .OrderBy(g => g) .ToArray(); diff --git a/Jellyfin.Server.Implementations/Item/NextUpService.cs b/Jellyfin.Server.Implementations/Item/NextUpService.cs index 1f5616bb5e..b25b347868 100644 --- a/Jellyfin.Server.Implementations/Item/NextUpService.cs +++ b/Jellyfin.Server.Implementations/Item/NextUpService.cs @@ -97,17 +97,28 @@ public class NextUpService : INextUpService .Where(e => e.ParentIndexNumber != 0) .Where(e => e.UserData!.Any(ud => ud.UserId == userId && ud.Played)); lastWatchedBase = _queryHelpers.ApplyAccessFiltering(context, lastWatchedBase, filter); - var lastWatchedInfo = lastWatchedBase - .GroupBy(e => e.SeriesPresentationUniqueKey) - .Select(g => new + + // Use lightweight projection + client-side grouping to avoid correlated scalar subquery + // per group that EF generates for GroupBy+OrderByDescending+FirstOrDefault. + var allPlayedLite = lastWatchedBase + .Select(e => new { - SeriesKey = g.Key!, - LastWatchedId = g.OrderByDescending(e => e.ParentIndexNumber) - .ThenByDescending(e => e.IndexNumber) - .Select(e => e.Id) - .FirstOrDefault() + e.Id, + e.SeriesPresentationUniqueKey, + e.ParentIndexNumber, + e.IndexNumber }) - .ToDictionary(x => x.SeriesKey, x => x.LastWatchedId); + .ToList(); + + var lastWatchedInfo = new Dictionary(); + foreach (var group in allPlayedLite.GroupBy(e => e.SeriesPresentationUniqueKey)) + { + var lastWatched = group + .OrderByDescending(e => e.ParentIndexNumber) + .ThenByDescending(e => e.IndexNumber) + .First(); + lastWatchedInfo[group.Key!] = lastWatched.Id; + } Dictionary lastWatchedByDateInfo = new(); if (includeWatchedForRewatching) @@ -119,18 +130,19 @@ public class NextUpService : INextUpService .Where(e => e.ParentIndexNumber != 0) .Where(e => e.UserData!.Any(ud => ud.UserId == userId && ud.Played)); lastWatchedByDateBase = _queryHelpers.ApplyAccessFiltering(context, lastWatchedByDateBase, filter); - lastWatchedByDateInfo = lastWatchedByDateBase + + // Use lightweight projection + client-side grouping instead of + // SelectMany+GroupBy+OrderByDescending+FirstOrDefault (correlated subquery). + var playedWithDates = lastWatchedByDateBase .SelectMany(e => e.UserData!.Where(ud => ud.UserId == userId && ud.Played) - .Select(ud => new { Episode = e, ud.LastPlayedDate })) - .GroupBy(x => x.Episode.SeriesPresentationUniqueKey) - .Select(g => new - { - SeriesKey = g.Key!, - LastWatchedId = g.OrderByDescending(x => x.LastPlayedDate) - .Select(x => x.Episode.Id) - .FirstOrDefault() - }) - .ToDictionary(x => x.SeriesKey, x => x.LastWatchedId); + .Select(ud => new { EpisodeId = e.Id, e.SeriesPresentationUniqueKey, ud.LastPlayedDate })) + .ToList(); + + foreach (var group in playedWithDates.GroupBy(x => x.SeriesPresentationUniqueKey)) + { + var mostRecent = group.OrderByDescending(x => x.LastPlayedDate).First(); + lastWatchedByDateInfo[group.Key!] = mostRecent.EpisodeId; + } } var allLastWatchedIds = lastWatchedInfo.Values diff --git a/src/Jellyfin.Database/Jellyfin.Database.Implementations/ModelConfiguration/BaseItemConfiguration.cs b/src/Jellyfin.Database/Jellyfin.Database.Implementations/ModelConfiguration/BaseItemConfiguration.cs index 910d76cde8..7fe1836c42 100644 --- a/src/Jellyfin.Database/Jellyfin.Database.Implementations/ModelConfiguration/BaseItemConfiguration.cs +++ b/src/Jellyfin.Database/Jellyfin.Database.Implementations/ModelConfiguration/BaseItemConfiguration.cs @@ -65,6 +65,8 @@ public class BaseItemConfiguration : IEntityTypeConfiguration builder.HasIndex(e => new { e.Type, e.TopParentId, e.SortName }); // NextUp: per-series episode ordering (index seek + range scan on season/episode) builder.HasIndex(e => new { e.Type, e.SeriesPresentationUniqueKey, e.ParentIndexNumber, e.IndexNumber }); + // ByName queries: WHERE Type = X AND CleanName IN (...) + builder.HasIndex(e => new { e.Type, e.CleanName }); // Latest TV: GROUP BY SeriesName builder.HasIndex(e => e.SeriesName); // Latest TV: episode count per season, season count per series diff --git a/src/Jellyfin.Database/Jellyfin.Database.Providers.Sqlite/Migrations/20260308123920_AddTypeCleanNameIndex.Designer.cs b/src/Jellyfin.Database/Jellyfin.Database.Providers.Sqlite/Migrations/20260308123920_AddTypeCleanNameIndex.Designer.cs new file mode 100644 index 0000000000..4c9ccc13bf --- /dev/null +++ b/src/Jellyfin.Database/Jellyfin.Database.Providers.Sqlite/Migrations/20260308123920_AddTypeCleanNameIndex.Designer.cs @@ -0,0 +1,1793 @@ +// +using System; +using Jellyfin.Database.Implementations; +using Microsoft.EntityFrameworkCore; +using Microsoft.EntityFrameworkCore.Infrastructure; +using Microsoft.EntityFrameworkCore.Migrations; +using Microsoft.EntityFrameworkCore.Storage.ValueConversion; + +#nullable disable + +namespace Jellyfin.Database.Providers.Sqlite.Migrations +{ + [DbContext(typeof(JellyfinDbContext))] + [Migration("20260308123920_AddTypeCleanNameIndex")] + partial class AddTypeCleanNameIndex + { + /// + protected override void BuildTargetModel(ModelBuilder modelBuilder) + { +#pragma warning disable 612, 618 + modelBuilder.HasAnnotation("ProductVersion", "10.0.3"); + + modelBuilder.Entity("Jellyfin.Database.Implementations.Entities.AccessSchedule", b => + { + b.Property("Id") + .ValueGeneratedOnAdd() + .HasColumnType("INTEGER"); + + b.Property("DayOfWeek") + .HasColumnType("INTEGER"); + + b.Property("EndHour") + .HasColumnType("REAL"); + + b.Property("StartHour") + .HasColumnType("REAL"); + + b.Property("UserId") + .HasColumnType("TEXT"); + + b.HasKey("Id"); + + b.HasIndex("UserId"); + + b.ToTable("AccessSchedules"); + + b.HasAnnotation("Sqlite:UseSqlReturningClause", false); + }); + + modelBuilder.Entity("Jellyfin.Database.Implementations.Entities.ActivityLog", b => + { + b.Property("Id") + .ValueGeneratedOnAdd() + .HasColumnType("INTEGER"); + + b.Property("DateCreated") + .HasColumnType("TEXT"); + + b.Property("ItemId") + .HasMaxLength(256) + .HasColumnType("TEXT"); + + b.Property("LogSeverity") + .HasColumnType("INTEGER"); + + b.Property("Name") + .IsRequired() + .HasMaxLength(512) + .HasColumnType("TEXT"); + + b.Property("Overview") + .HasMaxLength(512) + .HasColumnType("TEXT"); + + b.Property("RowVersion") + .IsConcurrencyToken() + .HasColumnType("INTEGER"); + + b.Property("ShortOverview") + .HasMaxLength(512) + .HasColumnType("TEXT"); + + b.Property("Type") + .IsRequired() + .HasMaxLength(256) + .HasColumnType("TEXT"); + + b.Property("UserId") + .HasColumnType("TEXT"); + + b.HasKey("Id"); + + b.HasIndex("DateCreated"); + + b.ToTable("ActivityLogs"); + + b.HasAnnotation("Sqlite:UseSqlReturningClause", false); + }); + + modelBuilder.Entity("Jellyfin.Database.Implementations.Entities.AncestorId", b => + { + b.Property("ItemId") + .HasColumnType("TEXT"); + + b.Property("ParentItemId") + .HasColumnType("TEXT"); + + b.HasKey("ItemId", "ParentItemId"); + + b.HasIndex("ParentItemId"); + + b.ToTable("AncestorIds"); + + b.HasAnnotation("Sqlite:UseSqlReturningClause", false); + }); + + modelBuilder.Entity("Jellyfin.Database.Implementations.Entities.AttachmentStreamInfo", b => + { + b.Property("ItemId") + .HasColumnType("TEXT"); + + b.Property("Index") + .HasColumnType("INTEGER"); + + b.Property("Codec") + .HasColumnType("TEXT"); + + b.Property("CodecTag") + .HasColumnType("TEXT"); + + b.Property("Comment") + .HasColumnType("TEXT"); + + b.Property("Filename") + .HasColumnType("TEXT"); + + b.Property("MimeType") + .HasColumnType("TEXT"); + + b.HasKey("ItemId", "Index"); + + b.ToTable("AttachmentStreamInfos"); + + b.HasAnnotation("Sqlite:UseSqlReturningClause", false); + }); + + modelBuilder.Entity("Jellyfin.Database.Implementations.Entities.BaseItemEntity", b => + { + b.Property("Id") + .ValueGeneratedOnAdd() + .HasColumnType("TEXT"); + + b.Property("Album") + .HasColumnType("TEXT"); + + b.Property("AlbumArtists") + .HasColumnType("TEXT"); + + b.Property("Artists") + .HasColumnType("TEXT"); + + b.Property("Audio") + .HasColumnType("INTEGER"); + + b.Property("ChannelId") + .HasColumnType("TEXT"); + + b.Property("CleanName") + .HasColumnType("TEXT"); + + b.Property("CommunityRating") + .HasColumnType("REAL"); + + b.Property("CriticRating") + .HasColumnType("REAL"); + + b.Property("CustomRating") + .HasColumnType("TEXT"); + + b.Property("Data") + .HasColumnType("TEXT"); + + b.Property("DateCreated") + .HasColumnType("TEXT"); + + b.Property("DateLastMediaAdded") + .HasColumnType("TEXT"); + + b.Property("DateLastRefreshed") + .HasColumnType("TEXT"); + + b.Property("DateLastSaved") + .HasColumnType("TEXT"); + + b.Property("DateModified") + .HasColumnType("TEXT"); + + b.Property("EndDate") + .HasColumnType("TEXT"); + + b.Property("EpisodeTitle") + .HasColumnType("TEXT"); + + b.Property("ExternalId") + .HasColumnType("TEXT"); + + b.Property("ExternalSeriesId") + .HasColumnType("TEXT"); + + b.Property("ExternalServiceId") + .HasColumnType("TEXT"); + + b.Property("ExtraType") + .HasColumnType("INTEGER"); + + b.Property("ForcedSortName") + .HasColumnType("TEXT"); + + b.Property("Genres") + .HasColumnType("TEXT"); + + b.Property("Height") + .HasColumnType("INTEGER"); + + b.Property("IndexNumber") + .HasColumnType("INTEGER"); + + b.Property("InheritedParentalRatingSubValue") + .HasColumnType("INTEGER"); + + b.Property("InheritedParentalRatingValue") + .HasColumnType("INTEGER"); + + b.Property("IsFolder") + .HasColumnType("INTEGER"); + + b.Property("IsInMixedFolder") + .HasColumnType("INTEGER"); + + b.Property("IsLocked") + .HasColumnType("INTEGER"); + + b.Property("IsMovie") + .HasColumnType("INTEGER"); + + b.Property("IsRepeat") + .HasColumnType("INTEGER"); + + b.Property("IsSeries") + .HasColumnType("INTEGER"); + + b.Property("IsVirtualItem") + .HasColumnType("INTEGER"); + + b.Property("LUFS") + .HasColumnType("REAL"); + + b.Property("MediaType") + .HasColumnType("TEXT"); + + b.Property("Name") + .HasColumnType("TEXT"); + + b.Property("NormalizationGain") + .HasColumnType("REAL"); + + b.Property("OfficialRating") + .HasColumnType("TEXT"); + + b.Property("OriginalTitle") + .HasColumnType("TEXT"); + + b.Property("Overview") + .HasColumnType("TEXT"); + + b.Property("OwnerId") + .HasColumnType("TEXT"); + + b.Property("ParentId") + .HasColumnType("TEXT"); + + b.Property("ParentIndexNumber") + .HasColumnType("INTEGER"); + + b.Property("Path") + .HasColumnType("TEXT"); + + b.Property("PreferredMetadataCountryCode") + .HasColumnType("TEXT"); + + b.Property("PreferredMetadataLanguage") + .HasColumnType("TEXT"); + + b.Property("PremiereDate") + .HasColumnType("TEXT"); + + b.Property("PresentationUniqueKey") + .HasColumnType("TEXT"); + + b.Property("PrimaryVersionId") + .HasColumnType("TEXT"); + + b.Property("ProductionLocations") + .HasColumnType("TEXT"); + + b.Property("ProductionYear") + .HasColumnType("INTEGER"); + + b.Property("RunTimeTicks") + .HasColumnType("INTEGER"); + + b.Property("SeasonId") + .HasColumnType("TEXT"); + + b.Property("SeasonName") + .HasColumnType("TEXT"); + + b.Property("SeriesId") + .HasColumnType("TEXT"); + + b.Property("SeriesName") + .HasColumnType("TEXT"); + + b.Property("SeriesPresentationUniqueKey") + .HasColumnType("TEXT"); + + b.Property("ShowId") + .HasColumnType("TEXT"); + + b.Property("Size") + .HasColumnType("INTEGER"); + + b.Property("SortName") + .HasColumnType("TEXT"); + + b.Property("StartDate") + .HasColumnType("TEXT"); + + b.Property("Studios") + .HasColumnType("TEXT"); + + b.Property("Tagline") + .HasColumnType("TEXT"); + + b.Property("Tags") + .HasColumnType("TEXT"); + + b.Property("TopParentId") + .HasColumnType("TEXT"); + + b.Property("TotalBitrate") + .HasColumnType("INTEGER"); + + b.Property("Type") + .IsRequired() + .HasColumnType("TEXT"); + + b.Property("UnratedType") + .HasColumnType("TEXT"); + + b.Property("Width") + .HasColumnType("INTEGER"); + + b.HasKey("Id"); + + b.HasIndex("Name"); + + b.HasIndex("OwnerId"); + + b.HasIndex("ParentId"); + + b.HasIndex("Path"); + + b.HasIndex("PresentationUniqueKey"); + + b.HasIndex("SeasonId"); + + b.HasIndex("SeriesId"); + + b.HasIndex("SeriesName"); + + b.HasIndex("ExtraType", "OwnerId"); + + b.HasIndex("TopParentId", "Id"); + + b.HasIndex("Type", "CleanName"); + + b.HasIndex("Type", "TopParentId", "Id"); + + b.HasIndex("Type", "TopParentId", "PresentationUniqueKey"); + + b.HasIndex("Type", "TopParentId", "SortName"); + + b.HasIndex("Type", "TopParentId", "StartDate"); + + b.HasIndex("MediaType", "TopParentId", "IsVirtualItem", "PresentationUniqueKey"); + + b.HasIndex("TopParentId", "IsFolder", "IsVirtualItem", "DateCreated"); + + b.HasIndex("TopParentId", "MediaType", "IsVirtualItem", "DateCreated"); + + b.HasIndex("TopParentId", "Type", "IsVirtualItem", "DateCreated"); + + b.HasIndex("Type", "SeriesPresentationUniqueKey", "IsFolder", "IsVirtualItem"); + + b.HasIndex("Type", "SeriesPresentationUniqueKey", "ParentIndexNumber", "IndexNumber"); + + b.HasIndex("Type", "SeriesPresentationUniqueKey", "PresentationUniqueKey", "SortName"); + + b.HasIndex("IsFolder", "TopParentId", "IsVirtualItem", "PresentationUniqueKey", "DateCreated"); + + b.HasIndex("Type", "TopParentId", "IsVirtualItem", "PresentationUniqueKey", "DateCreated"); + + b.ToTable("BaseItems"); + + b.HasAnnotation("Sqlite:UseSqlReturningClause", false); + + b.HasData( + new + { + Id = new Guid("00000000-0000-0000-0000-000000000001"), + IsFolder = false, + IsInMixedFolder = false, + IsLocked = false, + IsMovie = false, + IsRepeat = false, + IsSeries = false, + IsVirtualItem = false, + Name = "This is a placeholder item for UserData that has been detached from its original item", + Type = "PLACEHOLDER" + }); + }); + + modelBuilder.Entity("Jellyfin.Database.Implementations.Entities.BaseItemImageInfo", b => + { + b.Property("Id") + .ValueGeneratedOnAdd() + .HasColumnType("TEXT"); + + b.Property("Blurhash") + .HasColumnType("BLOB"); + + b.Property("DateModified") + .HasColumnType("TEXT"); + + b.Property("Height") + .HasColumnType("INTEGER"); + + b.Property("ImageType") + .HasColumnType("INTEGER"); + + b.Property("ItemId") + .HasColumnType("TEXT"); + + b.Property("Path") + .IsRequired() + .HasColumnType("TEXT"); + + b.Property("Width") + .HasColumnType("INTEGER"); + + b.HasKey("Id"); + + b.HasIndex("ItemId", "ImageType"); + + b.ToTable("BaseItemImageInfos"); + + b.HasAnnotation("Sqlite:UseSqlReturningClause", false); + }); + + modelBuilder.Entity("Jellyfin.Database.Implementations.Entities.BaseItemMetadataField", b => + { + b.Property("Id") + .HasColumnType("INTEGER"); + + b.Property("ItemId") + .HasColumnType("TEXT"); + + b.HasKey("Id", "ItemId"); + + b.HasIndex("ItemId"); + + b.ToTable("BaseItemMetadataFields"); + + b.HasAnnotation("Sqlite:UseSqlReturningClause", false); + }); + + modelBuilder.Entity("Jellyfin.Database.Implementations.Entities.BaseItemProvider", b => + { + b.Property("ItemId") + .HasColumnType("TEXT"); + + b.Property("ProviderId") + .HasColumnType("TEXT"); + + b.Property("ProviderValue") + .IsRequired() + .HasColumnType("TEXT"); + + b.HasKey("ItemId", "ProviderId"); + + b.HasIndex("ProviderId", "ItemId", "ProviderValue"); + + b.ToTable("BaseItemProviders"); + + b.HasAnnotation("Sqlite:UseSqlReturningClause", false); + }); + + modelBuilder.Entity("Jellyfin.Database.Implementations.Entities.BaseItemTrailerType", b => + { + b.Property("Id") + .HasColumnType("INTEGER"); + + b.Property("ItemId") + .HasColumnType("TEXT"); + + b.HasKey("Id", "ItemId"); + + b.HasIndex("ItemId"); + + b.ToTable("BaseItemTrailerTypes"); + + b.HasAnnotation("Sqlite:UseSqlReturningClause", false); + }); + + modelBuilder.Entity("Jellyfin.Database.Implementations.Entities.Chapter", b => + { + b.Property("ItemId") + .HasColumnType("TEXT"); + + b.Property("ChapterIndex") + .HasColumnType("INTEGER"); + + b.Property("ImageDateModified") + .HasColumnType("TEXT"); + + b.Property("ImagePath") + .HasColumnType("TEXT"); + + b.Property("Name") + .HasColumnType("TEXT"); + + b.Property("StartPositionTicks") + .HasColumnType("INTEGER"); + + b.HasKey("ItemId", "ChapterIndex"); + + b.ToTable("Chapters"); + + b.HasAnnotation("Sqlite:UseSqlReturningClause", false); + }); + + modelBuilder.Entity("Jellyfin.Database.Implementations.Entities.CustomItemDisplayPreferences", b => + { + b.Property("Id") + .ValueGeneratedOnAdd() + .HasColumnType("INTEGER"); + + b.Property("Client") + .IsRequired() + .HasMaxLength(32) + .HasColumnType("TEXT"); + + b.Property("ItemId") + .HasColumnType("TEXT"); + + b.Property("Key") + .IsRequired() + .HasColumnType("TEXT"); + + b.Property("UserId") + .HasColumnType("TEXT"); + + b.Property("Value") + .HasColumnType("TEXT"); + + b.HasKey("Id"); + + b.HasIndex("UserId", "ItemId", "Client", "Key") + .IsUnique(); + + b.ToTable("CustomItemDisplayPreferences"); + + b.HasAnnotation("Sqlite:UseSqlReturningClause", false); + }); + + modelBuilder.Entity("Jellyfin.Database.Implementations.Entities.DisplayPreferences", b => + { + b.Property("Id") + .ValueGeneratedOnAdd() + .HasColumnType("INTEGER"); + + b.Property("ChromecastVersion") + .HasColumnType("INTEGER"); + + b.Property("Client") + .IsRequired() + .HasMaxLength(32) + .HasColumnType("TEXT"); + + b.Property("DashboardTheme") + .HasMaxLength(32) + .HasColumnType("TEXT"); + + b.Property("EnableNextVideoInfoOverlay") + .HasColumnType("INTEGER"); + + b.Property("IndexBy") + .HasColumnType("INTEGER"); + + b.Property("ItemId") + .HasColumnType("TEXT"); + + b.Property("ScrollDirection") + .HasColumnType("INTEGER"); + + b.Property("ShowBackdrop") + .HasColumnType("INTEGER"); + + b.Property("ShowSidebar") + .HasColumnType("INTEGER"); + + b.Property("SkipBackwardLength") + .HasColumnType("INTEGER"); + + b.Property("SkipForwardLength") + .HasColumnType("INTEGER"); + + b.Property("TvHome") + .HasMaxLength(32) + .HasColumnType("TEXT"); + + b.Property("UserId") + .HasColumnType("TEXT"); + + b.HasKey("Id"); + + b.HasIndex("UserId", "ItemId", "Client") + .IsUnique(); + + b.ToTable("DisplayPreferences"); + + b.HasAnnotation("Sqlite:UseSqlReturningClause", false); + }); + + modelBuilder.Entity("Jellyfin.Database.Implementations.Entities.HomeSection", b => + { + b.Property("Id") + .ValueGeneratedOnAdd() + .HasColumnType("INTEGER"); + + b.Property("DisplayPreferencesId") + .HasColumnType("INTEGER"); + + b.Property("Order") + .HasColumnType("INTEGER"); + + b.Property("Type") + .HasColumnType("INTEGER"); + + b.HasKey("Id"); + + b.HasIndex("DisplayPreferencesId"); + + b.ToTable("HomeSection"); + + b.HasAnnotation("Sqlite:UseSqlReturningClause", false); + }); + + modelBuilder.Entity("Jellyfin.Database.Implementations.Entities.ImageInfo", b => + { + b.Property("Id") + .ValueGeneratedOnAdd() + .HasColumnType("INTEGER"); + + b.Property("LastModified") + .HasColumnType("TEXT"); + + b.Property("Path") + .IsRequired() + .HasMaxLength(512) + .HasColumnType("TEXT"); + + b.Property("UserId") + .HasColumnType("TEXT"); + + b.HasKey("Id"); + + b.HasIndex("UserId") + .IsUnique(); + + b.ToTable("ImageInfos"); + + b.HasAnnotation("Sqlite:UseSqlReturningClause", false); + }); + + modelBuilder.Entity("Jellyfin.Database.Implementations.Entities.ItemDisplayPreferences", b => + { + b.Property("Id") + .ValueGeneratedOnAdd() + .HasColumnType("INTEGER"); + + b.Property("Client") + .IsRequired() + .HasMaxLength(32) + .HasColumnType("TEXT"); + + b.Property("IndexBy") + .HasColumnType("INTEGER"); + + b.Property("ItemId") + .HasColumnType("TEXT"); + + b.Property("RememberIndexing") + .HasColumnType("INTEGER"); + + b.Property("RememberSorting") + .HasColumnType("INTEGER"); + + b.Property("SortBy") + .IsRequired() + .HasMaxLength(64) + .HasColumnType("TEXT"); + + b.Property("SortOrder") + .HasColumnType("INTEGER"); + + b.Property("UserId") + .HasColumnType("TEXT"); + + b.Property("ViewType") + .HasColumnType("INTEGER"); + + b.HasKey("Id"); + + b.HasIndex("UserId"); + + b.ToTable("ItemDisplayPreferences"); + + b.HasAnnotation("Sqlite:UseSqlReturningClause", false); + }); + + modelBuilder.Entity("Jellyfin.Database.Implementations.Entities.ItemValue", b => + { + b.Property("ItemValueId") + .ValueGeneratedOnAdd() + .HasColumnType("TEXT"); + + b.Property("CleanValue") + .IsRequired() + .HasColumnType("TEXT"); + + b.Property("Type") + .HasColumnType("INTEGER"); + + b.Property("Value") + .IsRequired() + .HasColumnType("TEXT"); + + b.HasKey("ItemValueId"); + + b.HasIndex("Type", "CleanValue"); + + b.HasIndex("Type", "Value") + .IsUnique(); + + b.ToTable("ItemValues"); + + b.HasAnnotation("Sqlite:UseSqlReturningClause", false); + }); + + modelBuilder.Entity("Jellyfin.Database.Implementations.Entities.ItemValueMap", b => + { + b.Property("ItemValueId") + .HasColumnType("TEXT"); + + b.Property("ItemId") + .HasColumnType("TEXT"); + + b.HasKey("ItemValueId", "ItemId"); + + b.HasIndex("ItemId"); + + b.ToTable("ItemValuesMap"); + + b.HasAnnotation("Sqlite:UseSqlReturningClause", false); + }); + + modelBuilder.Entity("Jellyfin.Database.Implementations.Entities.KeyframeData", b => + { + b.Property("ItemId") + .HasColumnType("TEXT"); + + b.PrimitiveCollection("KeyframeTicks") + .HasColumnType("TEXT"); + + b.Property("TotalDuration") + .HasColumnType("INTEGER"); + + b.HasKey("ItemId"); + + b.ToTable("KeyframeData"); + + b.HasAnnotation("Sqlite:UseSqlReturningClause", false); + }); + + modelBuilder.Entity("Jellyfin.Database.Implementations.Entities.LinkedChildEntity", b => + { + b.Property("ParentId") + .HasColumnType("TEXT"); + + b.Property("ChildId") + .HasColumnType("TEXT"); + + b.Property("ChildType") + .HasColumnType("INTEGER"); + + b.Property("SortOrder") + .HasColumnType("INTEGER"); + + b.HasKey("ParentId", "ChildId"); + + b.HasIndex("ChildId", "ChildType"); + + b.HasIndex("ParentId", "ChildType"); + + b.HasIndex("ParentId", "SortOrder"); + + b.ToTable("LinkedChildren", (string)null); + + b.HasAnnotation("Sqlite:UseSqlReturningClause", false); + }); + + modelBuilder.Entity("Jellyfin.Database.Implementations.Entities.MediaSegment", b => + { + b.Property("Id") + .ValueGeneratedOnAdd() + .HasColumnType("TEXT"); + + b.Property("EndTicks") + .HasColumnType("INTEGER"); + + b.Property("ItemId") + .HasColumnType("TEXT"); + + b.Property("SegmentProviderId") + .IsRequired() + .HasColumnType("TEXT"); + + b.Property("StartTicks") + .HasColumnType("INTEGER"); + + b.Property("Type") + .HasColumnType("INTEGER"); + + b.HasKey("Id"); + + b.ToTable("MediaSegments"); + + b.HasAnnotation("Sqlite:UseSqlReturningClause", false); + }); + + modelBuilder.Entity("Jellyfin.Database.Implementations.Entities.MediaStreamInfo", b => + { + b.Property("ItemId") + .HasColumnType("TEXT"); + + b.Property("StreamIndex") + .HasColumnType("INTEGER"); + + b.Property("AspectRatio") + .HasColumnType("TEXT"); + + b.Property("AverageFrameRate") + .HasColumnType("REAL"); + + b.Property("BitDepth") + .HasColumnType("INTEGER"); + + b.Property("BitRate") + .HasColumnType("INTEGER"); + + b.Property("BlPresentFlag") + .HasColumnType("INTEGER"); + + b.Property("ChannelLayout") + .HasColumnType("TEXT"); + + b.Property("Channels") + .HasColumnType("INTEGER"); + + b.Property("Codec") + .HasColumnType("TEXT"); + + b.Property("CodecTag") + .HasColumnType("TEXT"); + + b.Property("CodecTimeBase") + .HasColumnType("TEXT"); + + b.Property("ColorPrimaries") + .HasColumnType("TEXT"); + + b.Property("ColorSpace") + .HasColumnType("TEXT"); + + b.Property("ColorTransfer") + .HasColumnType("TEXT"); + + b.Property("Comment") + .HasColumnType("TEXT"); + + b.Property("DvBlSignalCompatibilityId") + .HasColumnType("INTEGER"); + + b.Property("DvLevel") + .HasColumnType("INTEGER"); + + b.Property("DvProfile") + .HasColumnType("INTEGER"); + + b.Property("DvVersionMajor") + .HasColumnType("INTEGER"); + + b.Property("DvVersionMinor") + .HasColumnType("INTEGER"); + + b.Property("ElPresentFlag") + .HasColumnType("INTEGER"); + + b.Property("Hdr10PlusPresentFlag") + .HasColumnType("INTEGER"); + + b.Property("Height") + .HasColumnType("INTEGER"); + + b.Property("IsAnamorphic") + .HasColumnType("INTEGER"); + + b.Property("IsAvc") + .HasColumnType("INTEGER"); + + b.Property("IsDefault") + .HasColumnType("INTEGER"); + + b.Property("IsExternal") + .HasColumnType("INTEGER"); + + b.Property("IsForced") + .HasColumnType("INTEGER"); + + b.Property("IsHearingImpaired") + .HasColumnType("INTEGER"); + + b.Property("IsInterlaced") + .HasColumnType("INTEGER"); + + b.Property("KeyFrames") + .HasColumnType("TEXT"); + + b.Property("Language") + .HasColumnType("TEXT"); + + b.Property("Level") + .HasColumnType("REAL"); + + b.Property("NalLengthSize") + .HasColumnType("TEXT"); + + b.Property("Path") + .HasColumnType("TEXT"); + + b.Property("PixelFormat") + .HasColumnType("TEXT"); + + b.Property("Profile") + .HasColumnType("TEXT"); + + b.Property("RealFrameRate") + .HasColumnType("REAL"); + + b.Property("RefFrames") + .HasColumnType("INTEGER"); + + b.Property("Rotation") + .HasColumnType("INTEGER"); + + b.Property("RpuPresentFlag") + .HasColumnType("INTEGER"); + + b.Property("SampleRate") + .HasColumnType("INTEGER"); + + b.Property("StreamType") + .HasColumnType("INTEGER"); + + b.Property("TimeBase") + .HasColumnType("TEXT"); + + b.Property("Title") + .HasColumnType("TEXT"); + + b.Property("Width") + .HasColumnType("INTEGER"); + + b.HasKey("ItemId", "StreamIndex"); + + b.ToTable("MediaStreamInfos"); + + b.HasAnnotation("Sqlite:UseSqlReturningClause", false); + }); + + modelBuilder.Entity("Jellyfin.Database.Implementations.Entities.People", b => + { + b.Property("Id") + .ValueGeneratedOnAdd() + .HasColumnType("TEXT"); + + b.Property("Name") + .IsRequired() + .HasColumnType("TEXT"); + + b.Property("PersonType") + .HasColumnType("TEXT"); + + b.HasKey("Id"); + + b.HasIndex("Name"); + + b.ToTable("Peoples"); + + b.HasAnnotation("Sqlite:UseSqlReturningClause", false); + }); + + modelBuilder.Entity("Jellyfin.Database.Implementations.Entities.PeopleBaseItemMap", b => + { + b.Property("ItemId") + .HasColumnType("TEXT"); + + b.Property("PeopleId") + .HasColumnType("TEXT"); + + b.Property("Role") + .HasColumnType("TEXT"); + + b.Property("ListOrder") + .HasColumnType("INTEGER"); + + b.Property("SortOrder") + .HasColumnType("INTEGER"); + + b.HasKey("ItemId", "PeopleId", "Role"); + + b.HasIndex("PeopleId"); + + b.HasIndex("ItemId", "ListOrder"); + + b.HasIndex("ItemId", "SortOrder"); + + b.ToTable("PeopleBaseItemMap"); + + b.HasAnnotation("Sqlite:UseSqlReturningClause", false); + }); + + modelBuilder.Entity("Jellyfin.Database.Implementations.Entities.Permission", b => + { + b.Property("Id") + .ValueGeneratedOnAdd() + .HasColumnType("INTEGER"); + + b.Property("Kind") + .HasColumnType("INTEGER"); + + b.Property("Permission_Permissions_Guid") + .HasColumnType("TEXT"); + + b.Property("RowVersion") + .IsConcurrencyToken() + .HasColumnType("INTEGER"); + + b.Property("UserId") + .HasColumnType("TEXT"); + + b.Property("Value") + .HasColumnType("INTEGER"); + + b.HasKey("Id"); + + b.HasIndex("UserId", "Kind") + .IsUnique() + .HasFilter("[UserId] IS NOT NULL"); + + b.ToTable("Permissions"); + + b.HasAnnotation("Sqlite:UseSqlReturningClause", false); + }); + + modelBuilder.Entity("Jellyfin.Database.Implementations.Entities.Preference", b => + { + b.Property("Id") + .ValueGeneratedOnAdd() + .HasColumnType("INTEGER"); + + b.Property("Kind") + .HasColumnType("INTEGER"); + + b.Property("Preference_Preferences_Guid") + .HasColumnType("TEXT"); + + b.Property("RowVersion") + .IsConcurrencyToken() + .HasColumnType("INTEGER"); + + b.Property("UserId") + .HasColumnType("TEXT"); + + b.Property("Value") + .IsRequired() + .HasMaxLength(65535) + .HasColumnType("TEXT"); + + b.HasKey("Id"); + + b.HasIndex("UserId", "Kind") + .IsUnique() + .HasFilter("[UserId] IS NOT NULL"); + + b.ToTable("Preferences"); + + b.HasAnnotation("Sqlite:UseSqlReturningClause", false); + }); + + modelBuilder.Entity("Jellyfin.Database.Implementations.Entities.Security.ApiKey", b => + { + b.Property("Id") + .ValueGeneratedOnAdd() + .HasColumnType("INTEGER"); + + b.Property("AccessToken") + .IsRequired() + .HasColumnType("TEXT"); + + b.Property("DateCreated") + .HasColumnType("TEXT"); + + b.Property("DateLastActivity") + .HasColumnType("TEXT"); + + b.Property("Name") + .IsRequired() + .HasMaxLength(64) + .HasColumnType("TEXT"); + + b.HasKey("Id"); + + b.HasIndex("AccessToken") + .IsUnique(); + + b.ToTable("ApiKeys"); + + b.HasAnnotation("Sqlite:UseSqlReturningClause", false); + }); + + modelBuilder.Entity("Jellyfin.Database.Implementations.Entities.Security.Device", b => + { + b.Property("Id") + .ValueGeneratedOnAdd() + .HasColumnType("INTEGER"); + + b.Property("AccessToken") + .IsRequired() + .HasColumnType("TEXT"); + + b.Property("AppName") + .IsRequired() + .HasMaxLength(64) + .HasColumnType("TEXT"); + + b.Property("AppVersion") + .IsRequired() + .HasMaxLength(32) + .HasColumnType("TEXT"); + + b.Property("DateCreated") + .HasColumnType("TEXT"); + + b.Property("DateLastActivity") + .HasColumnType("TEXT"); + + b.Property("DateModified") + .HasColumnType("TEXT"); + + b.Property("DeviceId") + .IsRequired() + .HasMaxLength(256) + .HasColumnType("TEXT"); + + b.Property("DeviceName") + .IsRequired() + .HasMaxLength(64) + .HasColumnType("TEXT"); + + b.Property("IsActive") + .HasColumnType("INTEGER"); + + b.Property("UserId") + .HasColumnType("TEXT"); + + b.HasKey("Id"); + + b.HasIndex("AccessToken", "DateLastActivity"); + + b.HasIndex("DeviceId", "DateLastActivity"); + + b.HasIndex("UserId", "DeviceId"); + + b.ToTable("Devices"); + + b.HasAnnotation("Sqlite:UseSqlReturningClause", false); + }); + + modelBuilder.Entity("Jellyfin.Database.Implementations.Entities.Security.DeviceOptions", b => + { + b.Property("Id") + .ValueGeneratedOnAdd() + .HasColumnType("INTEGER"); + + b.Property("CustomName") + .HasColumnType("TEXT"); + + b.Property("DeviceId") + .IsRequired() + .HasColumnType("TEXT"); + + b.HasKey("Id"); + + b.HasIndex("DeviceId") + .IsUnique(); + + b.ToTable("DeviceOptions"); + + b.HasAnnotation("Sqlite:UseSqlReturningClause", false); + }); + + modelBuilder.Entity("Jellyfin.Database.Implementations.Entities.TrickplayInfo", b => + { + b.Property("ItemId") + .HasColumnType("TEXT"); + + b.Property("Width") + .HasColumnType("INTEGER"); + + b.Property("Bandwidth") + .HasColumnType("INTEGER"); + + b.Property("Height") + .HasColumnType("INTEGER"); + + b.Property("Interval") + .HasColumnType("INTEGER"); + + b.Property("ThumbnailCount") + .HasColumnType("INTEGER"); + + b.Property("TileHeight") + .HasColumnType("INTEGER"); + + b.Property("TileWidth") + .HasColumnType("INTEGER"); + + b.HasKey("ItemId", "Width"); + + b.ToTable("TrickplayInfos"); + + b.HasAnnotation("Sqlite:UseSqlReturningClause", false); + }); + + modelBuilder.Entity("Jellyfin.Database.Implementations.Entities.User", b => + { + b.Property("Id") + .ValueGeneratedOnAdd() + .HasColumnType("TEXT"); + + b.Property("AudioLanguagePreference") + .HasMaxLength(255) + .HasColumnType("TEXT"); + + b.Property("AuthenticationProviderId") + .IsRequired() + .HasMaxLength(255) + .HasColumnType("TEXT"); + + b.Property("CastReceiverId") + .HasMaxLength(32) + .HasColumnType("TEXT"); + + b.Property("DisplayCollectionsView") + .HasColumnType("INTEGER"); + + b.Property("DisplayMissingEpisodes") + .HasColumnType("INTEGER"); + + b.Property("EnableAutoLogin") + .HasColumnType("INTEGER"); + + b.Property("EnableLocalPassword") + .HasColumnType("INTEGER"); + + b.Property("EnableNextEpisodeAutoPlay") + .HasColumnType("INTEGER"); + + b.Property("EnableUserPreferenceAccess") + .HasColumnType("INTEGER"); + + b.Property("HidePlayedInLatest") + .HasColumnType("INTEGER"); + + b.Property("InternalId") + .HasColumnType("INTEGER"); + + b.Property("InvalidLoginAttemptCount") + .HasColumnType("INTEGER"); + + b.Property("LastActivityDate") + .HasColumnType("TEXT"); + + b.Property("LastLoginDate") + .HasColumnType("TEXT"); + + b.Property("LoginAttemptsBeforeLockout") + .HasColumnType("INTEGER"); + + b.Property("MaxActiveSessions") + .HasColumnType("INTEGER"); + + b.Property("MaxParentalRatingScore") + .HasColumnType("INTEGER"); + + b.Property("MaxParentalRatingSubScore") + .HasColumnType("INTEGER"); + + b.Property("MustUpdatePassword") + .HasColumnType("INTEGER"); + + b.Property("Password") + .HasMaxLength(65535) + .HasColumnType("TEXT"); + + b.Property("PasswordResetProviderId") + .IsRequired() + .HasMaxLength(255) + .HasColumnType("TEXT"); + + b.Property("PlayDefaultAudioTrack") + .HasColumnType("INTEGER"); + + b.Property("RememberAudioSelections") + .HasColumnType("INTEGER"); + + b.Property("RememberSubtitleSelections") + .HasColumnType("INTEGER"); + + b.Property("RemoteClientBitrateLimit") + .HasColumnType("INTEGER"); + + b.Property("RowVersion") + .IsConcurrencyToken() + .HasColumnType("INTEGER"); + + b.Property("SubtitleLanguagePreference") + .HasMaxLength(255) + .HasColumnType("TEXT"); + + b.Property("SubtitleMode") + .HasColumnType("INTEGER"); + + b.Property("SyncPlayAccess") + .HasColumnType("INTEGER"); + + b.Property("Username") + .IsRequired() + .HasMaxLength(255) + .HasColumnType("TEXT"); + + b.HasKey("Id"); + + b.HasIndex("Username") + .IsUnique(); + + b.ToTable("Users"); + + b.HasAnnotation("Sqlite:UseSqlReturningClause", false); + }); + + modelBuilder.Entity("Jellyfin.Database.Implementations.Entities.UserData", b => + { + b.Property("ItemId") + .HasColumnType("TEXT"); + + b.Property("UserId") + .HasColumnType("TEXT"); + + b.Property("CustomDataKey") + .HasColumnType("TEXT"); + + b.Property("AudioStreamIndex") + .HasColumnType("INTEGER"); + + b.Property("IsFavorite") + .HasColumnType("INTEGER"); + + b.Property("LastPlayedDate") + .HasColumnType("TEXT"); + + b.Property("Likes") + .HasColumnType("INTEGER"); + + b.Property("PlayCount") + .HasColumnType("INTEGER"); + + b.Property("PlaybackPositionTicks") + .HasColumnType("INTEGER"); + + b.Property("Played") + .HasColumnType("INTEGER"); + + b.Property("Rating") + .HasColumnType("REAL"); + + b.Property("RetentionDate") + .HasColumnType("TEXT"); + + b.Property("SubtitleStreamIndex") + .HasColumnType("INTEGER"); + + b.HasKey("ItemId", "UserId", "CustomDataKey"); + + b.HasIndex("ItemId", "UserId", "IsFavorite"); + + b.HasIndex("ItemId", "UserId", "LastPlayedDate"); + + b.HasIndex("ItemId", "UserId", "PlaybackPositionTicks"); + + b.HasIndex("ItemId", "UserId", "Played"); + + b.HasIndex("UserId", "IsFavorite", "ItemId"); + + b.HasIndex("UserId", "ItemId", "LastPlayedDate"); + + b.HasIndex("UserId", "Played", "ItemId"); + + b.ToTable("UserData"); + + b.HasAnnotation("Sqlite:UseSqlReturningClause", false); + }); + + modelBuilder.Entity("Jellyfin.Database.Implementations.Entities.AccessSchedule", b => + { + b.HasOne("Jellyfin.Database.Implementations.Entities.User", null) + .WithMany("AccessSchedules") + .HasForeignKey("UserId") + .OnDelete(DeleteBehavior.Cascade) + .IsRequired(); + }); + + modelBuilder.Entity("Jellyfin.Database.Implementations.Entities.AncestorId", b => + { + b.HasOne("Jellyfin.Database.Implementations.Entities.BaseItemEntity", "Item") + .WithMany("Parents") + .HasForeignKey("ItemId") + .OnDelete(DeleteBehavior.Cascade) + .IsRequired(); + + b.HasOne("Jellyfin.Database.Implementations.Entities.BaseItemEntity", "ParentItem") + .WithMany("Children") + .HasForeignKey("ParentItemId") + .OnDelete(DeleteBehavior.Cascade) + .IsRequired(); + + b.Navigation("Item"); + + b.Navigation("ParentItem"); + }); + + modelBuilder.Entity("Jellyfin.Database.Implementations.Entities.AttachmentStreamInfo", b => + { + b.HasOne("Jellyfin.Database.Implementations.Entities.BaseItemEntity", "Item") + .WithMany() + .HasForeignKey("ItemId") + .OnDelete(DeleteBehavior.Cascade) + .IsRequired(); + + b.Navigation("Item"); + }); + + modelBuilder.Entity("Jellyfin.Database.Implementations.Entities.BaseItemEntity", b => + { + b.HasOne("Jellyfin.Database.Implementations.Entities.BaseItemEntity", "Owner") + .WithMany("Extras") + .HasForeignKey("OwnerId") + .OnDelete(DeleteBehavior.NoAction); + + b.HasOne("Jellyfin.Database.Implementations.Entities.BaseItemEntity", "DirectParent") + .WithMany("DirectChildren") + .HasForeignKey("ParentId") + .OnDelete(DeleteBehavior.Cascade); + + b.Navigation("DirectParent"); + + b.Navigation("Owner"); + }); + + modelBuilder.Entity("Jellyfin.Database.Implementations.Entities.BaseItemImageInfo", b => + { + b.HasOne("Jellyfin.Database.Implementations.Entities.BaseItemEntity", "Item") + .WithMany("Images") + .HasForeignKey("ItemId") + .OnDelete(DeleteBehavior.Cascade) + .IsRequired(); + + b.Navigation("Item"); + }); + + modelBuilder.Entity("Jellyfin.Database.Implementations.Entities.BaseItemMetadataField", b => + { + b.HasOne("Jellyfin.Database.Implementations.Entities.BaseItemEntity", "Item") + .WithMany("LockedFields") + .HasForeignKey("ItemId") + .OnDelete(DeleteBehavior.Cascade) + .IsRequired(); + + b.Navigation("Item"); + }); + + modelBuilder.Entity("Jellyfin.Database.Implementations.Entities.BaseItemProvider", b => + { + b.HasOne("Jellyfin.Database.Implementations.Entities.BaseItemEntity", "Item") + .WithMany("Provider") + .HasForeignKey("ItemId") + .OnDelete(DeleteBehavior.Cascade) + .IsRequired(); + + b.Navigation("Item"); + }); + + modelBuilder.Entity("Jellyfin.Database.Implementations.Entities.BaseItemTrailerType", b => + { + b.HasOne("Jellyfin.Database.Implementations.Entities.BaseItemEntity", "Item") + .WithMany("TrailerTypes") + .HasForeignKey("ItemId") + .OnDelete(DeleteBehavior.Cascade) + .IsRequired(); + + b.Navigation("Item"); + }); + + modelBuilder.Entity("Jellyfin.Database.Implementations.Entities.Chapter", b => + { + b.HasOne("Jellyfin.Database.Implementations.Entities.BaseItemEntity", "Item") + .WithMany("Chapters") + .HasForeignKey("ItemId") + .OnDelete(DeleteBehavior.Cascade) + .IsRequired(); + + b.Navigation("Item"); + }); + + modelBuilder.Entity("Jellyfin.Database.Implementations.Entities.DisplayPreferences", b => + { + b.HasOne("Jellyfin.Database.Implementations.Entities.User", null) + .WithMany("DisplayPreferences") + .HasForeignKey("UserId") + .OnDelete(DeleteBehavior.Cascade) + .IsRequired(); + }); + + modelBuilder.Entity("Jellyfin.Database.Implementations.Entities.HomeSection", b => + { + b.HasOne("Jellyfin.Database.Implementations.Entities.DisplayPreferences", null) + .WithMany("HomeSections") + .HasForeignKey("DisplayPreferencesId") + .OnDelete(DeleteBehavior.Cascade) + .IsRequired(); + }); + + modelBuilder.Entity("Jellyfin.Database.Implementations.Entities.ImageInfo", b => + { + b.HasOne("Jellyfin.Database.Implementations.Entities.User", null) + .WithOne("ProfileImage") + .HasForeignKey("Jellyfin.Database.Implementations.Entities.ImageInfo", "UserId") + .OnDelete(DeleteBehavior.Cascade); + }); + + modelBuilder.Entity("Jellyfin.Database.Implementations.Entities.ItemDisplayPreferences", b => + { + b.HasOne("Jellyfin.Database.Implementations.Entities.User", null) + .WithMany("ItemDisplayPreferences") + .HasForeignKey("UserId") + .OnDelete(DeleteBehavior.Cascade) + .IsRequired(); + }); + + modelBuilder.Entity("Jellyfin.Database.Implementations.Entities.ItemValueMap", b => + { + b.HasOne("Jellyfin.Database.Implementations.Entities.BaseItemEntity", "Item") + .WithMany("ItemValues") + .HasForeignKey("ItemId") + .OnDelete(DeleteBehavior.Cascade) + .IsRequired(); + + b.HasOne("Jellyfin.Database.Implementations.Entities.ItemValue", "ItemValue") + .WithMany("BaseItemsMap") + .HasForeignKey("ItemValueId") + .OnDelete(DeleteBehavior.Cascade) + .IsRequired(); + + b.Navigation("Item"); + + b.Navigation("ItemValue"); + }); + + modelBuilder.Entity("Jellyfin.Database.Implementations.Entities.KeyframeData", b => + { + b.HasOne("Jellyfin.Database.Implementations.Entities.BaseItemEntity", "Item") + .WithMany() + .HasForeignKey("ItemId") + .OnDelete(DeleteBehavior.Cascade) + .IsRequired(); + + b.Navigation("Item"); + }); + + modelBuilder.Entity("Jellyfin.Database.Implementations.Entities.LinkedChildEntity", b => + { + b.HasOne("Jellyfin.Database.Implementations.Entities.BaseItemEntity", "Child") + .WithMany("LinkedChildOfEntities") + .HasForeignKey("ChildId") + .OnDelete(DeleteBehavior.NoAction) + .IsRequired(); + + b.HasOne("Jellyfin.Database.Implementations.Entities.BaseItemEntity", "Parent") + .WithMany("LinkedChildEntities") + .HasForeignKey("ParentId") + .OnDelete(DeleteBehavior.NoAction) + .IsRequired(); + + b.Navigation("Child"); + + b.Navigation("Parent"); + }); + + modelBuilder.Entity("Jellyfin.Database.Implementations.Entities.MediaStreamInfo", b => + { + b.HasOne("Jellyfin.Database.Implementations.Entities.BaseItemEntity", "Item") + .WithMany("MediaStreams") + .HasForeignKey("ItemId") + .OnDelete(DeleteBehavior.Cascade) + .IsRequired(); + + b.Navigation("Item"); + }); + + modelBuilder.Entity("Jellyfin.Database.Implementations.Entities.PeopleBaseItemMap", b => + { + b.HasOne("Jellyfin.Database.Implementations.Entities.BaseItemEntity", "Item") + .WithMany("Peoples") + .HasForeignKey("ItemId") + .OnDelete(DeleteBehavior.Cascade) + .IsRequired(); + + b.HasOne("Jellyfin.Database.Implementations.Entities.People", "People") + .WithMany("BaseItems") + .HasForeignKey("PeopleId") + .OnDelete(DeleteBehavior.Cascade) + .IsRequired(); + + b.Navigation("Item"); + + b.Navigation("People"); + }); + + modelBuilder.Entity("Jellyfin.Database.Implementations.Entities.Permission", b => + { + b.HasOne("Jellyfin.Database.Implementations.Entities.User", null) + .WithMany("Permissions") + .HasForeignKey("UserId") + .OnDelete(DeleteBehavior.Cascade); + }); + + modelBuilder.Entity("Jellyfin.Database.Implementations.Entities.Preference", b => + { + b.HasOne("Jellyfin.Database.Implementations.Entities.User", null) + .WithMany("Preferences") + .HasForeignKey("UserId") + .OnDelete(DeleteBehavior.Cascade); + }); + + modelBuilder.Entity("Jellyfin.Database.Implementations.Entities.Security.Device", b => + { + b.HasOne("Jellyfin.Database.Implementations.Entities.User", "User") + .WithMany() + .HasForeignKey("UserId") + .OnDelete(DeleteBehavior.Cascade) + .IsRequired(); + + b.Navigation("User"); + }); + + modelBuilder.Entity("Jellyfin.Database.Implementations.Entities.UserData", b => + { + b.HasOne("Jellyfin.Database.Implementations.Entities.BaseItemEntity", "Item") + .WithMany("UserData") + .HasForeignKey("ItemId") + .OnDelete(DeleteBehavior.Cascade) + .IsRequired(); + + b.HasOne("Jellyfin.Database.Implementations.Entities.User", "User") + .WithMany() + .HasForeignKey("UserId") + .OnDelete(DeleteBehavior.Cascade) + .IsRequired(); + + b.Navigation("Item"); + + b.Navigation("User"); + }); + + modelBuilder.Entity("Jellyfin.Database.Implementations.Entities.BaseItemEntity", b => + { + b.Navigation("Chapters"); + + b.Navigation("Children"); + + b.Navigation("DirectChildren"); + + b.Navigation("Extras"); + + b.Navigation("Images"); + + b.Navigation("ItemValues"); + + b.Navigation("LinkedChildEntities"); + + b.Navigation("LinkedChildOfEntities"); + + b.Navigation("LockedFields"); + + b.Navigation("MediaStreams"); + + b.Navigation("Parents"); + + b.Navigation("Peoples"); + + b.Navigation("Provider"); + + b.Navigation("TrailerTypes"); + + b.Navigation("UserData"); + }); + + modelBuilder.Entity("Jellyfin.Database.Implementations.Entities.DisplayPreferences", b => + { + b.Navigation("HomeSections"); + }); + + modelBuilder.Entity("Jellyfin.Database.Implementations.Entities.ItemValue", b => + { + b.Navigation("BaseItemsMap"); + }); + + modelBuilder.Entity("Jellyfin.Database.Implementations.Entities.People", b => + { + b.Navigation("BaseItems"); + }); + + modelBuilder.Entity("Jellyfin.Database.Implementations.Entities.User", b => + { + b.Navigation("AccessSchedules"); + + b.Navigation("DisplayPreferences"); + + b.Navigation("ItemDisplayPreferences"); + + b.Navigation("Permissions"); + + b.Navigation("Preferences"); + + b.Navigation("ProfileImage"); + }); +#pragma warning restore 612, 618 + } + } +} diff --git a/src/Jellyfin.Database/Jellyfin.Database.Providers.Sqlite/Migrations/20260308123920_AddTypeCleanNameIndex.cs b/src/Jellyfin.Database/Jellyfin.Database.Providers.Sqlite/Migrations/20260308123920_AddTypeCleanNameIndex.cs new file mode 100644 index 0000000000..3932e1c3e4 --- /dev/null +++ b/src/Jellyfin.Database/Jellyfin.Database.Providers.Sqlite/Migrations/20260308123920_AddTypeCleanNameIndex.cs @@ -0,0 +1,27 @@ +using Microsoft.EntityFrameworkCore.Migrations; + +#nullable disable + +namespace Jellyfin.Database.Providers.Sqlite.Migrations +{ + /// + public partial class AddTypeCleanNameIndex : Migration + { + /// + protected override void Up(MigrationBuilder migrationBuilder) + { + migrationBuilder.CreateIndex( + name: "IX_BaseItems_Type_CleanName", + table: "BaseItems", + columns: new[] { "Type", "CleanName" }); + } + + /// + protected override void Down(MigrationBuilder migrationBuilder) + { + migrationBuilder.DropIndex( + name: "IX_BaseItems_Type_CleanName", + table: "BaseItems"); + } + } +} diff --git a/src/Jellyfin.Database/Jellyfin.Database.Providers.Sqlite/Migrations/JellyfinDbModelSnapshot.cs b/src/Jellyfin.Database/Jellyfin.Database.Providers.Sqlite/Migrations/JellyfinDbModelSnapshot.cs index 17c6df4e9d..d67e3a2149 100644 --- a/src/Jellyfin.Database/Jellyfin.Database.Providers.Sqlite/Migrations/JellyfinDbModelSnapshot.cs +++ b/src/Jellyfin.Database/Jellyfin.Database.Providers.Sqlite/Migrations/JellyfinDbModelSnapshot.cs @@ -380,6 +380,8 @@ namespace Jellyfin.Server.Implementations.Migrations b.HasIndex("TopParentId", "Id"); + b.HasIndex("Type", "CleanName"); + b.HasIndex("Type", "TopParentId", "Id"); b.HasIndex("Type", "TopParentId", "PresentationUniqueKey"); -- cgit v1.2.3 From d5f4c624e31d135896561537e9d20c50100cf1ac Mon Sep 17 00:00:00 2001 From: Shadowghost Date: Mon, 30 Mar 2026 18:56:31 +0200 Subject: Do not return alternate versions by default --- .../Item/BaseItemRepository.QueryBuilding.cs | 7 +++++++ 1 file changed, 7 insertions(+) (limited to 'Jellyfin.Server.Implementations/Item/BaseItemRepository.QueryBuilding.cs') diff --git a/Jellyfin.Server.Implementations/Item/BaseItemRepository.QueryBuilding.cs b/Jellyfin.Server.Implementations/Item/BaseItemRepository.QueryBuilding.cs index 83f108bed0..22a03dafa7 100644 --- a/Jellyfin.Server.Implementations/Item/BaseItemRepository.QueryBuilding.cs +++ b/Jellyfin.Server.Implementations/Item/BaseItemRepository.QueryBuilding.cs @@ -439,6 +439,13 @@ public sealed partial class BaseItemRepository || (e.TopParentId.HasValue && f.ItemId == e.TopParentId.Value)))); } + // Exclude alternate versions from counts. Alternate versions have + // OwnerId set (pointing to their primary) but no ExtraType. + if (!filter.IncludeOwnedItems) + { + baseQuery = baseQuery.Where(e => e.OwnerId == null || e.ExtraType != null); + } + return baseQuery; } -- cgit v1.2.3 From 6fdfc6a61b705191a23cf05d6648ace5607ab197 Mon Sep 17 00:00:00 2001 From: Shadowghost Date: Mon, 30 Mar 2026 19:49:36 +0200 Subject: Fix version filters --- .../Item/BaseItemRepository.QueryBuilding.cs | 6 +++--- .../Item/BaseItemRepository.TranslateQuery.cs | 8 ++++---- 2 files changed, 7 insertions(+), 7 deletions(-) (limited to 'Jellyfin.Server.Implementations/Item/BaseItemRepository.QueryBuilding.cs') diff --git a/Jellyfin.Server.Implementations/Item/BaseItemRepository.QueryBuilding.cs b/Jellyfin.Server.Implementations/Item/BaseItemRepository.QueryBuilding.cs index 22a03dafa7..812b6ab59c 100644 --- a/Jellyfin.Server.Implementations/Item/BaseItemRepository.QueryBuilding.cs +++ b/Jellyfin.Server.Implementations/Item/BaseItemRepository.QueryBuilding.cs @@ -439,11 +439,11 @@ public sealed partial class BaseItemRepository || (e.TopParentId.HasValue && f.ItemId == e.TopParentId.Value)))); } - // Exclude alternate versions from counts. Alternate versions have - // OwnerId set (pointing to their primary) but no ExtraType. + // Exclude alternate versions and owned non-extra items from counts. + // Alternate versions have PrimaryVersionId set (pointing to their primary). if (!filter.IncludeOwnedItems) { - baseQuery = baseQuery.Where(e => e.OwnerId == null || e.ExtraType != null); + baseQuery = baseQuery.Where(e => e.PrimaryVersionId == null && (e.OwnerId == null || e.ExtraType != null)); } return baseQuery; diff --git a/Jellyfin.Server.Implementations/Item/BaseItemRepository.TranslateQuery.cs b/Jellyfin.Server.Implementations/Item/BaseItemRepository.TranslateQuery.cs index f7f48278db..c1c7e6cd95 100644 --- a/Jellyfin.Server.Implementations/Item/BaseItemRepository.TranslateQuery.cs +++ b/Jellyfin.Server.Implementations/Item/BaseItemRepository.TranslateQuery.cs @@ -721,10 +721,10 @@ public sealed partial class BaseItemRepository } else if (filter.OwnerIds.Length == 0 && filter.ExtraTypes.Length == 0 && !filter.IncludeOwnedItems) { - // Exclude alternate versions from general queries. Alternate versions have - // OwnerId set (pointing to their primary) but no ExtraType. - // Extras (trailers, etc.) also have OwnerId but DO have ExtraType set - keep those. - baseQuery = baseQuery.Where(e => e.OwnerId == null || e.ExtraType != null); + // Exclude alternate versions and owned non-extra items from general queries. + // Alternate versions have PrimaryVersionId set (pointing to their primary). + // Extras (trailers, etc.) have OwnerId set but also have ExtraType set - keep those. + baseQuery = baseQuery.Where(e => e.PrimaryVersionId == null && (e.OwnerId == null || e.ExtraType != null)); } if (filter.OwnerIds.Length > 0) -- cgit v1.2.3 From f806ae40187ff5d853fff7cdd72709eab39bc9ac Mon Sep 17 00:00:00 2001 From: Shadowghost Date: Sun, 19 Apr 2026 10:27:47 +0200 Subject: Fix too many SQL variables error on large libraries --- .../Library/UserDataManager.cs | 4 +++- .../Item/BaseItemRepository.QueryBuilding.cs | 21 +++++++-------------- .../Routines/FixIncorrectOwnerIdRelationships.cs | 2 +- .../Migrations/Routines/MigrateLinkedChildren.cs | 2 +- 4 files changed, 12 insertions(+), 17 deletions(-) (limited to 'Jellyfin.Server.Implementations/Item/BaseItemRepository.QueryBuilding.cs') diff --git a/Emby.Server.Implementations/Library/UserDataManager.cs b/Emby.Server.Implementations/Library/UserDataManager.cs index 9e138dbdaa..1281f1587f 100644 --- a/Emby.Server.Implementations/Library/UserDataManager.cs +++ b/Emby.Server.Implementations/Library/UserDataManager.cs @@ -219,7 +219,9 @@ namespace Emby.Server.Implementations.Library using var context = _repository.CreateDbContext(); var userDataArray = context.UserData .AsNoTracking() - .Where(e => allItemIds.Contains(e.ItemId) && allKeys.Contains(e.CustomDataKey) && e.UserId.Equals(user.Id)) + .Where(e => e.UserId.Equals(user.Id)) + .WhereOneOrMany(allItemIds, e => e.ItemId) + .WhereOneOrMany(allKeys, e => e.CustomDataKey) .ToArray(); var userDataByItem = userDataArray.GroupBy(e => e.ItemId).ToDictionary(g => g.Key, g => g.ToArray()); diff --git a/Jellyfin.Server.Implementations/Item/BaseItemRepository.QueryBuilding.cs b/Jellyfin.Server.Implementations/Item/BaseItemRepository.QueryBuilding.cs index 812b6ab59c..12bb1e95d4 100644 --- a/Jellyfin.Server.Implementations/Item/BaseItemRepository.QueryBuilding.cs +++ b/Jellyfin.Server.Implementations/Item/BaseItemRepository.QueryBuilding.cs @@ -62,29 +62,23 @@ public sealed partial class BaseItemRepository private IQueryable ApplyGroupingFilter(JellyfinDbContext context, IQueryable dbQuery, InternalItemsQuery filter) { - // This whole block is needed to filter duplicate entries on request - // for the time being it cannot be used because it would destroy the ordering - // this results in "duplicate" responses for queries that try to lookup individual series or multiple versions but - // for that case the invoker has to run a DistinctBy(e => e.PresentationUniqueKey) on their own - + // Collapse duplicates sharing a presentation key (e.g. alternate versions) by picking + // the min Id per group. Keep the grouped ids as an IQueryable sub-select; materializing + // to a List would inline one bound parameter per id and hit SQLite's variable cap. var enableGroupByPresentationUniqueKey = EnableGroupByPresentationUniqueKey(filter); - // Materialize GroupBy IDs first to split the complex expression tree. - // This runs the filter+GroupBy+Min as one simple SQL query, then the downstream - // Order/Paging/Navigations work on a flat WHERE Id IN (...) list, avoiding - // EF Core having to compile a deeply nested expression tree. if (enableGroupByPresentationUniqueKey && filter.GroupBySeriesPresentationUniqueKey) { - var groupedIds = dbQuery.GroupBy(e => new { e.PresentationUniqueKey, e.SeriesPresentationUniqueKey }).Select(e => e.Min(x => x.Id)).ToList(); + var groupedIds = dbQuery.GroupBy(e => new { e.PresentationUniqueKey, e.SeriesPresentationUniqueKey }).Select(e => e.Min(x => x.Id)); dbQuery = context.BaseItems.Where(e => groupedIds.Contains(e.Id)); } else if (enableGroupByPresentationUniqueKey) { - var groupedIds = dbQuery.GroupBy(e => e.PresentationUniqueKey).Select(e => e.Min(x => x.Id)).ToList(); + var groupedIds = dbQuery.GroupBy(e => e.PresentationUniqueKey).Select(e => e.Min(x => x.Id)); dbQuery = context.BaseItems.Where(e => groupedIds.Contains(e.Id)); } else if (filter.GroupBySeriesPresentationUniqueKey) { - var groupedIds = dbQuery.GroupBy(e => e.SeriesPresentationUniqueKey).Select(e => e.Min(x => x.Id)).ToList(); + var groupedIds = dbQuery.GroupBy(e => e.SeriesPresentationUniqueKey).Select(e => e.Min(x => x.Id)); dbQuery = context.BaseItems.Where(e => groupedIds.Contains(e.Id)); } else @@ -96,8 +90,7 @@ public sealed partial class BaseItemRepository { dbQuery = ApplyBoxSetCollapsing(context, dbQuery, filter.CollapseBoxSetItemTypes); - // Apply name-range filters after collapse so BoxSets are filtered by their own name, - // not by their children's names. + // Name filters run after collapse so BoxSets match by their own name, not a child's. dbQuery = ApplyNameFilters(dbQuery, filter); } diff --git a/Jellyfin.Server/Migrations/Routines/FixIncorrectOwnerIdRelationships.cs b/Jellyfin.Server/Migrations/Routines/FixIncorrectOwnerIdRelationships.cs index 8538f5d7dc..c743590e43 100644 --- a/Jellyfin.Server/Migrations/Routines/FixIncorrectOwnerIdRelationships.cs +++ b/Jellyfin.Server/Migrations/Routines/FixIncorrectOwnerIdRelationships.cs @@ -294,7 +294,7 @@ public class FixIncorrectOwnerIdRelationships : IAsyncMigrationRoutine // Batch-load all child items in a single query var childIds = alternateVersionLinks.Select(l => l.ChildId).Distinct().ToList(); var childItems = await context.BaseItems - .Where(b => childIds.Contains(b.Id)) + .WhereOneOrMany(childIds, b => b.Id) .ToDictionaryAsync(b => b.Id, cancellationToken) .ConfigureAwait(false); diff --git a/Jellyfin.Server/Migrations/Routines/MigrateLinkedChildren.cs b/Jellyfin.Server/Migrations/Routines/MigrateLinkedChildren.cs index f0e4803f18..129d443ca5 100644 --- a/Jellyfin.Server/Migrations/Routines/MigrateLinkedChildren.cs +++ b/Jellyfin.Server/Migrations/Routines/MigrateLinkedChildren.cs @@ -209,7 +209,7 @@ internal class MigrateLinkedChildren : IDatabaseMigrationRoutine var childIds = toInsert.Select(lc => lc.ChildId).Distinct().ToList(); var existingChildIds = context.BaseItems - .Where(b => childIds.Contains(b.Id)) + .WhereOneOrMany(childIds, b => b.Id) .Select(b => b.Id) .ToHashSet(); -- cgit v1.2.3 From fc866a64e063c9f04df3fab9a00846501c8d2b13 Mon Sep 17 00:00:00 2001 From: Shadowghost Date: Sun, 26 Apr 2026 17:55:19 +0200 Subject: Remove unnecessary materializations --- Jellyfin.Server.Implementations/Item/BaseItemMapper.cs | 5 ----- .../Item/BaseItemRepository.ByName.cs | 18 +++++++----------- .../Item/BaseItemRepository.QueryBuilding.cs | 4 ++-- .../Item/BaseItemRepository.TranslateQuery.cs | 9 --------- Jellyfin.Server.Implementations/Item/NextUpService.cs | 10 +++------- Jellyfin.Server.Implementations/Item/OrderMapper.cs | 2 +- 6 files changed, 13 insertions(+), 35 deletions(-) (limited to 'Jellyfin.Server.Implementations/Item/BaseItemRepository.QueryBuilding.cs') diff --git a/Jellyfin.Server.Implementations/Item/BaseItemMapper.cs b/Jellyfin.Server.Implementations/Item/BaseItemMapper.cs index 831e7c3354..67a233c41d 100644 --- a/Jellyfin.Server.Implementations/Item/BaseItemMapper.cs +++ b/Jellyfin.Server.Implementations/Item/BaseItemMapper.cs @@ -168,9 +168,6 @@ internal static class BaseItemMapper dto.ImageInfos = entity.Images.Select(e => MapImageFromEntity(e, appHost)).ToArray(); } - // dto.Type = entity.Type; - // dto.Data = entity.Data; - // dto.MediaType = Enum.TryParse(entity.MediaType); if (dto is IHasStartDate hasStartDate) { hasStartDate.StartDate = entity.StartDate.GetValueOrDefault(); @@ -354,8 +351,6 @@ internal static class BaseItemMapper }).ToArray() ?? []; } - // dto.Type = entity.Type; - // dto.Data = entity.Data; entity.MediaType = dto.MediaType.ToString(); if (dto is IHasStartDate hasStartDate) { diff --git a/Jellyfin.Server.Implementations/Item/BaseItemRepository.ByName.cs b/Jellyfin.Server.Implementations/Item/BaseItemRepository.ByName.cs index 907d8527aa..c4464008d4 100644 --- a/Jellyfin.Server.Implementations/Item/BaseItemRepository.ByName.cs +++ b/Jellyfin.Server.Implementations/Item/BaseItemRepository.ByName.cs @@ -133,8 +133,9 @@ public sealed partial class BaseItemRepository IsSeries = filter.IsSeries }); - // Materialize the matching CleanValues early. This splits one massive expression tree - // into two simpler queries, dramatically reducing EF Core expression compilation time. + // Keep this as an IQueryable sub-select. Materializing to a list would inline one + // bound parameter per CleanValue and hit SQLite's variable cap on libraries with + // high-cardinality value types (e.g. tens of thousands of artists). var matchingCleanValues = context.ItemValuesMap .Where(ivm => itemValueTypes.Contains(ivm.ItemValue.Type)) .Join( @@ -142,8 +143,7 @@ public sealed partial class BaseItemRepository ivm => ivm.ItemId, g => g.Id, (ivm, g) => ivm.ItemValue.CleanValue) - .Distinct() - .ToList(); + .Distinct(); var innerQuery = PrepareItemQuery(context, filter) .Where(e => e.Type == returnType) @@ -170,10 +170,8 @@ public sealed partial class BaseItemRepository ExcludeItemIds = filter.ExcludeItemIds }; - // Materialize the matching IDs first. This keeps the complex nested subquery - // (inner filter + ItemValues join + search + GroupBy) as a single simple SQL statement, - // and then the entity load with Includes uses a flat WHERE Id IN (...) list. - // This avoids EF having to compile the entire nested expression tree into the final query. + // Build the master query and collapse rows that share a PresentationUniqueKey + // (e.g. alternate versions) by picking the lowest Id per group. var masterQuery = TranslateQuery(innerQuery, context, outerQueryFilter); var orderedMasterQuery = ApplyOrder(masterQuery, filter, context) @@ -229,9 +227,7 @@ public sealed partial class BaseItemRepository var musicArtistTypeName = _itemTypeLookup.BaseItemKindNames[BaseItemKind.MusicArtist]; var audioTypeName = _itemTypeLookup.BaseItemKindNames[BaseItemKind.Audio]; var trailerTypeName = _itemTypeLookup.BaseItemKindNames[BaseItemKind.Trailer]; - - // Materialize the matching IDs to avoid nested subquery in the counts expression tree. - var itemIds = itemCountQuery.Select(e => e.Id).ToList(); + var itemIds = itemCountQuery.Select(e => e.Id); // Rewrite query to avoid SelectMany on navigation properties (which requires SQL APPLY, not supported on SQLite) // Instead, start from ItemValueMaps and join with BaseItems diff --git a/Jellyfin.Server.Implementations/Item/BaseItemRepository.QueryBuilding.cs b/Jellyfin.Server.Implementations/Item/BaseItemRepository.QueryBuilding.cs index 12bb1e95d4..02664621d4 100644 --- a/Jellyfin.Server.Implementations/Item/BaseItemRepository.QueryBuilding.cs +++ b/Jellyfin.Server.Implementations/Item/BaseItemRepository.QueryBuilding.cs @@ -432,8 +432,8 @@ public sealed partial class BaseItemRepository || (e.TopParentId.HasValue && f.ItemId == e.TopParentId.Value)))); } - // Exclude alternate versions and owned non-extra items from counts. - // Alternate versions have PrimaryVersionId set (pointing to their primary). + // Exclude alternate versions (have PrimaryVersionId set) and owned non-extra items. + // Extras (trailers, etc.) have OwnerId set but also have ExtraType set — keep those. if (!filter.IncludeOwnedItems) { baseQuery = baseQuery.Where(e => e.PrimaryVersionId == null && (e.OwnerId == null || e.ExtraType != null)); diff --git a/Jellyfin.Server.Implementations/Item/BaseItemRepository.TranslateQuery.cs b/Jellyfin.Server.Implementations/Item/BaseItemRepository.TranslateQuery.cs index 664befc2ef..d14b62c3a0 100644 --- a/Jellyfin.Server.Implementations/Item/BaseItemRepository.TranslateQuery.cs +++ b/Jellyfin.Server.Implementations/Item/BaseItemRepository.TranslateQuery.cs @@ -376,14 +376,6 @@ public sealed partial class BaseItemRepository baseQuery = baseQuery.Where(e => e.Peoples!.Any(f => f.People.Name == filter.Person)); } - if (!string.IsNullOrWhiteSpace(filter.MinSortName)) - { - // this does not makes sense. - // baseQuery = baseQuery.Where(e => e.SortName >= query.MinSortName); - // whereClauses.Add("SortName>=@MinSortName"); - // statement?.TryBind("@MinSortName", query.MinSortName); - } - if (!string.IsNullOrWhiteSpace(filter.ExternalSeriesId)) { baseQuery = baseQuery.Where(e => e.ExternalSeriesId == filter.ExternalSeriesId); @@ -407,7 +399,6 @@ public sealed partial class BaseItemRepository } } - // These are the same, for now var nameContains = filter.NameContains; if (!string.IsNullOrWhiteSpace(nameContains)) { diff --git a/Jellyfin.Server.Implementations/Item/NextUpService.cs b/Jellyfin.Server.Implementations/Item/NextUpService.cs index b25b347868..d78e246691 100644 --- a/Jellyfin.Server.Implementations/Item/NextUpService.cs +++ b/Jellyfin.Server.Implementations/Item/NextUpService.cs @@ -150,13 +150,9 @@ public class NextUpService : INextUpService .Where(id => id != Guid.Empty) .Distinct() .ToList(); - var lastWatchedEpisodes = new Dictionary(); - if (allLastWatchedIds.Count > 0) - { - var lwQuery = context.BaseItems.AsNoTracking().Where(e => allLastWatchedIds.Contains(e.Id)); - lwQuery = _queryHelpers.ApplyNavigations(lwQuery, filter); - lastWatchedEpisodes = lwQuery.ToDictionary(e => e.Id); - } + var lwQuery = context.BaseItems.AsNoTracking().Where(e => allLastWatchedIds.Contains(e.Id)); + lwQuery = _queryHelpers.ApplyNavigations(lwQuery, filter); + var lastWatchedEpisodes = lwQuery.ToDictionary(e => e.Id); Dictionary> specialsBySeriesKey = new(); if (includeSpecials) diff --git a/Jellyfin.Server.Implementations/Item/OrderMapper.cs b/Jellyfin.Server.Implementations/Item/OrderMapper.cs index aeea8db4d4..ada86c8b87 100644 --- a/Jellyfin.Server.Implementations/Item/OrderMapper.cs +++ b/Jellyfin.Server.Implementations/Item/OrderMapper.cs @@ -31,7 +31,7 @@ public static class OrderMapper { return (sortBy, query.User) switch { - (ItemSortBy.AirTime, _) => e => e.SortName, // TODO + (ItemSortBy.AirTime, _) => e => e.SortName, (ItemSortBy.Runtime, _) => e => e.RunTimeTicks, (ItemSortBy.Random, _) => e => EF.Functions.Random(), (ItemSortBy.DatePlayed, _) => e => e.UserData!.Where(f => f.UserId.Equals(query.User!.Id)).OrderBy(f => f.CustomDataKey).FirstOrDefault()!.LastPlayedDate, -- cgit v1.2.3 From d19449e6a5d66bc37ade831dd96a85152e98a533 Mon Sep 17 00:00:00 2001 From: Shadowghost Date: Sun, 26 Apr 2026 17:59:16 +0200 Subject: Use AsNoTracking() when only reading --- .../Item/BaseItemRepository.ByName.cs | 2 +- .../Item/BaseItemRepository.QueryBuilding.cs | 11 ++++++----- .../Item/BaseItemRepository.Querying.cs | 2 +- Jellyfin.Server.Implementations/Item/LinkedChildrenService.cs | 4 +++- 4 files changed, 11 insertions(+), 8 deletions(-) (limited to 'Jellyfin.Server.Implementations/Item/BaseItemRepository.QueryBuilding.cs') diff --git a/Jellyfin.Server.Implementations/Item/BaseItemRepository.ByName.cs b/Jellyfin.Server.Implementations/Item/BaseItemRepository.ByName.cs index c4464008d4..380c6e582c 100644 --- a/Jellyfin.Server.Implementations/Item/BaseItemRepository.ByName.cs +++ b/Jellyfin.Server.Implementations/Item/BaseItemRepository.ByName.cs @@ -197,7 +197,7 @@ public sealed partial class BaseItemRepository var masterIds = orderedMasterQuery.ToList(); var query = ApplyNavigations( - context.BaseItems.AsSingleQuery().Where(e => masterIds.Contains(e.Id)), + context.BaseItems.AsNoTracking().AsSingleQuery().Where(e => masterIds.Contains(e.Id)), filter); query = ApplyOrder(query, filter, context); diff --git a/Jellyfin.Server.Implementations/Item/BaseItemRepository.QueryBuilding.cs b/Jellyfin.Server.Implementations/Item/BaseItemRepository.QueryBuilding.cs index 02664621d4..a1f02be059 100644 --- a/Jellyfin.Server.Implementations/Item/BaseItemRepository.QueryBuilding.cs +++ b/Jellyfin.Server.Implementations/Item/BaseItemRepository.QueryBuilding.cs @@ -69,17 +69,17 @@ public sealed partial class BaseItemRepository if (enableGroupByPresentationUniqueKey && filter.GroupBySeriesPresentationUniqueKey) { var groupedIds = dbQuery.GroupBy(e => new { e.PresentationUniqueKey, e.SeriesPresentationUniqueKey }).Select(e => e.Min(x => x.Id)); - dbQuery = context.BaseItems.Where(e => groupedIds.Contains(e.Id)); + dbQuery = context.BaseItems.AsNoTracking().Where(e => groupedIds.Contains(e.Id)); } else if (enableGroupByPresentationUniqueKey) { var groupedIds = dbQuery.GroupBy(e => e.PresentationUniqueKey).Select(e => e.Min(x => x.Id)); - dbQuery = context.BaseItems.Where(e => groupedIds.Contains(e.Id)); + dbQuery = context.BaseItems.AsNoTracking().Where(e => groupedIds.Contains(e.Id)); } else if (filter.GroupBySeriesPresentationUniqueKey) { var groupedIds = dbQuery.GroupBy(e => e.SeriesPresentationUniqueKey).Select(e => e.Min(x => x.Id)); - dbQuery = context.BaseItems.Where(e => groupedIds.Contains(e.Id)); + dbQuery = context.BaseItems.AsNoTracking().Where(e => groupedIds.Contains(e.Id)); } else { @@ -142,7 +142,7 @@ public sealed partial class BaseItemRepository .Distinct(); var collapsedIds = nonCollapsibleIds.Union(collapsibleNotInBoxSet).Union(boxSetIds); - return context.BaseItems.Where(e => collapsedIds.Contains(e.Id)); + return context.BaseItems.AsNoTracking().Where(e => collapsedIds.Contains(e.Id)); } private static IQueryable ApplyBoxSetCollapsingAll( @@ -169,7 +169,7 @@ public sealed partial class BaseItemRepository .Distinct(); var collapsedIds = notInBoxSet.Union(boxSetIds); - return context.BaseItems.Where(e => collapsedIds.Contains(e.Id)); + return context.BaseItems.AsNoTracking().Where(e => collapsedIds.Contains(e.Id)); } private static IQueryable ApplyNameFilters(IQueryable dbQuery, InternalItemsQuery filter) @@ -368,6 +368,7 @@ public sealed partial class BaseItemRepository var allDescendantIds = DescendantQueryHelper.GetAllDescendantIds(context, ancestorId); var baseQuery = context.BaseItems + .AsNoTracking() .Where(b => allDescendantIds.Contains(b.Id) && !b.IsFolder && !b.IsVirtualItem); return ApplyAccessFiltering(context, baseQuery, filter); diff --git a/Jellyfin.Server.Implementations/Item/BaseItemRepository.Querying.cs b/Jellyfin.Server.Implementations/Item/BaseItemRepository.Querying.cs index 7ca3559324..69c8a3b811 100644 --- a/Jellyfin.Server.Implementations/Item/BaseItemRepository.Querying.cs +++ b/Jellyfin.Server.Implementations/Item/BaseItemRepository.Querying.cs @@ -87,7 +87,7 @@ public sealed partial class BaseItemRepository return Array.Empty(); } - var itemsById = ApplyNavigations(context.BaseItems.Where(e => orderedIds.Contains(e.Id)), filter) + var itemsById = ApplyNavigations(context.BaseItems.AsNoTracking().Where(e => orderedIds.Contains(e.Id)), filter) .AsSplitQuery() .AsEnumerable() .Select(w => DeserializeBaseItem(w, filter.SkipDeserialization)) diff --git a/Jellyfin.Server.Implementations/Item/LinkedChildrenService.cs b/Jellyfin.Server.Implementations/Item/LinkedChildrenService.cs index 19afe04f01..4d27cae218 100644 --- a/Jellyfin.Server.Implementations/Item/LinkedChildrenService.cs +++ b/Jellyfin.Server.Implementations/Item/LinkedChildrenService.cs @@ -62,7 +62,9 @@ public class LinkedChildrenService : ILinkedChildrenService { using var dbContext = _dbProvider.CreateDbContext(); - var artists = dbContext.BaseItems.Where(e => e.Type == _itemTypeLookup.BaseItemKindNames[BaseItemKind.MusicArtist]!) + var artists = dbContext.BaseItems + .AsNoTracking() + .Where(e => e.Type == _itemTypeLookup.BaseItemKindNames[BaseItemKind.MusicArtist]!) .Where(e => artistNames.Contains(e.Name)) .ToArray(); -- cgit v1.2.3 From a1f3da1819ed796ab255ca14d57593cf9c6b7480 Mon Sep 17 00:00:00 2001 From: Shadowghost Date: Sun, 26 Apr 2026 18:52:22 +0200 Subject: Reduce correlated EXISTS queries --- .../Item/BaseItemRepository.QueryBuilding.cs | 130 ++++++++++++--------- .../Item/BaseItemRepository.TranslateQuery.cs | 101 +++++++--------- 2 files changed, 121 insertions(+), 110 deletions(-) (limited to 'Jellyfin.Server.Implementations/Item/BaseItemRepository.QueryBuilding.cs') diff --git a/Jellyfin.Server.Implementations/Item/BaseItemRepository.QueryBuilding.cs b/Jellyfin.Server.Implementations/Item/BaseItemRepository.QueryBuilding.cs index a1f02be059..7570421e78 100644 --- a/Jellyfin.Server.Implementations/Item/BaseItemRepository.QueryBuilding.cs +++ b/Jellyfin.Server.Implementations/Item/BaseItemRepository.QueryBuilding.cs @@ -117,28 +117,44 @@ public sealed partial class BaseItemRepository // Only collapse specific item types, keep others untouched var collapsibleTypeNames = collapsibleTypes.Select(t => _itemTypeLookup.BaseItemKindNames[t]).ToList(); + // Categorize items in currentIds in a single pass to avoid multiple correlated EXISTS over BaseItems. + var categorized = context.BaseItems + .AsNoTracking() + .Where(bi => currentIds.Contains(bi.Id)) + .Select(bi => new + { + bi.Id, + IsCollapsible = collapsibleTypeNames.Contains(bi.Type), + IsBoxSet = bi.Type == boxSetTypeName + }); + + var collapsibleChildIds = categorized.Where(c => c.IsCollapsible).Select(c => c.Id); + + // Single JOIN: manual links to BoxSet parents, restricted to currentIds children. + var manualBoxSetLinks = context.LinkedChildren + .Where(lc => lc.ChildType == Database.Implementations.Entities.LinkedChildType.Manual + && currentIds.Contains(lc.ChildId)) + .Join( + context.BaseItems.Where(bs => bs.Type == boxSetTypeName), + lc => lc.ParentId, + bs => bs.Id, + (lc, bs) => new { lc.ChildId, lc.ParentId }); + + var childrenInBoxSet = manualBoxSetLinks.Select(x => x.ChildId).Distinct(); + // Items whose type is NOT collapsible (always kept in results) - var nonCollapsibleIds = currentIds - .Where(id => !context.BaseItems.Any(bi => bi.Id == id && collapsibleTypeNames.Contains(bi.Type))); - - // Collapsible items that are NOT in any box set (kept in results) - var collapsibleNotInBoxSet = currentIds - .Where(id => - context.BaseItems.Any(bi => bi.Id == id && collapsibleTypeNames.Contains(bi.Type)) - && !context.BaseItems.Any(bs => bs.Id == id && bs.Type == boxSetTypeName) - && !context.LinkedChildren.Any(lc => - lc.ChildId == id - && lc.ChildType == Jellyfin.Database.Implementations.Entities.LinkedChildType.Manual - && context.BaseItems.Any(bs => bs.Id == lc.ParentId && bs.Type == boxSetTypeName))); - - // Box set IDs containing at least one accessible collapsible child item - var boxSetIds = context.LinkedChildren - .Where(lc => - lc.ChildType == Jellyfin.Database.Implementations.Entities.LinkedChildType.Manual - && currentIds.Contains(lc.ChildId) - && context.BaseItems.Any(bi => bi.Id == lc.ChildId && collapsibleTypeNames.Contains(bi.Type)) - && context.BaseItems.Any(bs => bs.Id == lc.ParentId && bs.Type == boxSetTypeName)) - .Select(lc => lc.ParentId) + var nonCollapsibleIds = categorized.Where(c => !c.IsCollapsible).Select(c => c.Id); + + // Collapsible items that are not a BoxSet themselves and not a manual child of any BoxSet + var collapsibleNotInBoxSet = categorized + .Where(c => c.IsCollapsible && !c.IsBoxSet) + .Select(c => c.Id) + .Where(id => !childrenInBoxSet.Contains(id)); + + // BoxSet IDs containing at least one collapsible child item from currentIds + var boxSetIds = manualBoxSetLinks + .Where(x => collapsibleChildIds.Contains(x.ChildId)) + .Select(x => x.ParentId) .Distinct(); var collapsedIds = nonCollapsibleIds.Union(collapsibleNotInBoxSet).Union(boxSetIds); @@ -150,23 +166,25 @@ public sealed partial class BaseItemRepository IQueryable currentIds, string boxSetTypeName) { - // Items that are NOT box sets and NOT in any box set - var notInBoxSet = currentIds - .Where(id => - !context.BaseItems.Any(bs => bs.Id == id && bs.Type == boxSetTypeName) - && !context.LinkedChildren.Any(lc => - lc.ChildId == id - && lc.ChildType == Jellyfin.Database.Implementations.Entities.LinkedChildType.Manual - && context.BaseItems.Any(bs => bs.Id == lc.ParentId && bs.Type == boxSetTypeName))); - - // Box set IDs containing at least one accessible child item - var boxSetIds = context.LinkedChildren - .Where(lc => - lc.ChildType == Jellyfin.Database.Implementations.Entities.LinkedChildType.Manual - && currentIds.Contains(lc.ChildId) - && context.BaseItems.Any(bs => bs.Id == lc.ParentId && bs.Type == boxSetTypeName)) - .Select(lc => lc.ParentId) - .Distinct(); + // Single JOIN: manual links to BoxSet parents, restricted to currentIds children. + var manualBoxSetLinks = context.LinkedChildren + .Where(lc => lc.ChildType == Database.Implementations.Entities.LinkedChildType.Manual + && currentIds.Contains(lc.ChildId)) + .Join( + context.BaseItems.Where(bs => bs.Type == boxSetTypeName), + lc => lc.ParentId, + bs => bs.Id, + (lc, bs) => new { lc.ChildId, lc.ParentId }); + + var childrenInBoxSet = manualBoxSetLinks.Select(x => x.ChildId).Distinct(); + var boxSetIds = manualBoxSetLinks.Select(x => x.ParentId).Distinct(); + + // Items in currentIds that are not BoxSets themselves and not a manual child of any BoxSet + var notInBoxSet = context.BaseItems + .AsNoTracking() + .Where(e => currentIds.Contains(e.Id) && e.Type != boxSetTypeName) + .Select(e => e.Id) + .Where(id => !childrenInBoxSet.Contains(id)); var collapsedIds = notInBoxSet.Union(boxSetIds); return context.BaseItems.AsNoTracking().Where(e => collapsedIds.Contains(e.Id)); @@ -405,32 +423,36 @@ public sealed partial class BaseItemRepository e.InheritedParentalRatingValue != null || !unratedItemTypes.Contains(e.UnratedType)); } - // Apply excluded tags filtering (blocked tags) + // Apply excluded tags filtering (blocked tags). + // Pre-build the blocked-item-id set as a sub-select; then four index-seek Contains checks + // instead of one EXISTS over a 4-way OR predicate that defeats index seeks. if (filter.ExcludeInheritedTags.Length > 0) { var excludedTags = filter.ExcludeInheritedTags.Select(e => e.GetCleanValue()).ToArray(); + var blockedTagItemIds = context.ItemValuesMap + .Where(f => f.ItemValue.Type == ItemValueType.Tags && excludedTags.Contains(f.ItemValue.CleanValue)) + .Select(f => f.ItemId); + baseQuery = baseQuery.Where(e => - !context.ItemValuesMap.Any(f => - f.ItemValue.Type == ItemValueType.Tags - && excludedTags.Contains(f.ItemValue.CleanValue) - && (f.ItemId == e.Id - || (e.SeriesId.HasValue && f.ItemId == e.SeriesId.Value) - || e.Parents!.Any(p => f.ItemId == p.ParentItemId) - || (e.TopParentId.HasValue && f.ItemId == e.TopParentId.Value)))); + !blockedTagItemIds.Contains(e.Id) + && !(e.SeriesId.HasValue && blockedTagItemIds.Contains(e.SeriesId.Value)) + && !e.Parents!.Any(p => blockedTagItemIds.Contains(p.ParentItemId)) + && !(e.TopParentId.HasValue && blockedTagItemIds.Contains(e.TopParentId.Value))); } - // Apply included tags filtering (allowed tags - item must have at least one) + // Apply included tags filtering (allowed tags - item must have at least one). if (filter.IncludeInheritedTags.Length > 0) { var includeTags = filter.IncludeInheritedTags.Select(e => e.GetCleanValue()).ToArray(); + var allowedTagItemIds = context.ItemValuesMap + .Where(f => f.ItemValue.Type == ItemValueType.Tags && includeTags.Contains(f.ItemValue.CleanValue)) + .Select(f => f.ItemId); + baseQuery = baseQuery.Where(e => - context.ItemValuesMap.Any(f => - f.ItemValue.Type == ItemValueType.Tags - && includeTags.Contains(f.ItemValue.CleanValue) - && (f.ItemId == e.Id - || (e.SeriesId.HasValue && f.ItemId == e.SeriesId.Value) - || e.Parents!.Any(p => f.ItemId == p.ParentItemId) - || (e.TopParentId.HasValue && f.ItemId == e.TopParentId.Value)))); + allowedTagItemIds.Contains(e.Id) + || (e.SeriesId.HasValue && allowedTagItemIds.Contains(e.SeriesId.Value)) + || e.Parents!.Any(p => allowedTagItemIds.Contains(p.ParentItemId)) + || (e.TopParentId.HasValue && allowedTagItemIds.Contains(e.TopParentId.Value))); } // Exclude alternate versions (have PrimaryVersionId set) and owned non-extra items. diff --git a/Jellyfin.Server.Implementations/Item/BaseItemRepository.TranslateQuery.cs b/Jellyfin.Server.Implementations/Item/BaseItemRepository.TranslateQuery.cs index d14b62c3a0..9a57691fbd 100644 --- a/Jellyfin.Server.Implementations/Item/BaseItemRepository.TranslateQuery.cs +++ b/Jellyfin.Server.Implementations/Item/BaseItemRepository.TranslateQuery.cs @@ -461,20 +461,14 @@ public sealed partial class BaseItemRepository var seriesTypeName = _itemTypeLookup.BaseItemKindNames[BaseItemKind.Series]; var boxSetTypeName = _itemTypeLookup.BaseItemKindNames[BaseItemKind.BoxSet]; - // Series: played = all episodes played, unplayed = any episode unplayed - var seriesWithEpisodes = hasSeries + // Series: played = at least one episode AND all episodes played; unplayed = otherwise. + IQueryable playedSeriesIds = hasSeries ? context.BaseItems + .AsNoTracking() .Where(e => !e.IsFolder && !e.IsVirtualItem && e.SeriesId.HasValue) - .Select(e => e.SeriesId!.Value) - .Distinct() - : Enumerable.Empty().AsQueryable(); - - var seriesWithUnplayedEpisodes = hasSeries - ? context.BaseItems - .Where(e => !e.IsFolder && !e.IsVirtualItem && e.SeriesId.HasValue - && !e.UserData!.Any(ud => ud.UserId == userId && ud.Played)) - .Select(e => e.SeriesId!.Value) - .Distinct() + .GroupBy(e => e.SeriesId!.Value) + .Where(g => !g.Any(e => !e.UserData!.Any(ud => ud.UserId == userId && ud.Played))) + .Select(g => g.Key) : Enumerable.Empty().AsQueryable(); // BoxSet: played = all children played @@ -496,14 +490,14 @@ public sealed partial class BaseItemRepository if (isPlayed) { baseQuery = baseQuery.Where(e => - (e.Type == seriesTypeName && seriesWithEpisodes.Contains(e.Id) && !seriesWithUnplayedEpisodes.Contains(e.Id)) + (e.Type == seriesTypeName && playedSeriesIds.Contains(e.Id)) || (e.Type == boxSetTypeName && playedBoxSetIds.Contains(e.Id)) || (e.Type != seriesTypeName && e.Type != boxSetTypeName && playedItemIds.Contains(e.Id))); } else { baseQuery = baseQuery.Where(e => - (e.Type == seriesTypeName && (!seriesWithEpisodes.Contains(e.Id) || seriesWithUnplayedEpisodes.Contains(e.Id))) + (e.Type == seriesTypeName && !playedSeriesIds.Contains(e.Id)) || (e.Type == boxSetTypeName && !playedBoxSetIds.Contains(e.Id)) || (e.Type != seriesTypeName && e.Type != boxSetTypeName && !playedItemIds.Contains(e.Id))); } @@ -528,41 +522,33 @@ public sealed partial class BaseItemRepository var seriesTypeName = _itemTypeLookup.BaseItemKindNames[BaseItemKind.Series]; var isResumable = filter.IsResumable.Value; - // Series with at least one in-progress episode. - var seriesWithInProgressEpisodes = context.BaseItems - .Where(e => !e.IsFolder && !e.IsVirtualItem && e.SeriesId.HasValue - && e.UserData!.Any(ud => ud.UserId == userId && ud.PlaybackPositionTicks > 0)) - .Select(e => e.SeriesId!.Value) - .Distinct(); - - // Series with at least one played episode. - var seriesWithPlayedEpisodes = context.BaseItems - .Where(e => !e.IsFolder && !e.IsVirtualItem && e.SeriesId.HasValue - && e.UserData!.Any(ud => ud.UserId == userId && ud.Played)) - .Select(e => e.SeriesId!.Value) - .Distinct(); - - // Series with at least one unplayed episode. - var seriesWithUnplayedEpisodes = context.BaseItems - .Where(e => !e.IsFolder && !e.IsVirtualItem && e.SeriesId.HasValue - && !e.UserData!.Any(ud => ud.UserId == userId && ud.Played)) - .Select(e => e.SeriesId!.Value) - .Distinct(); + // Aggregate per series in a single GROUP BY pass, instead of three full scans. + var seriesEpisodeStats = context.BaseItems + .AsNoTracking() + .Where(e => !e.IsFolder && !e.IsVirtualItem && e.SeriesId.HasValue) + .GroupBy(e => e.SeriesId!.Value) + .Select(g => new + { + SeriesId = g.Key, + HasInProgress = g.Any(e => e.UserData!.Any(ud => ud.UserId == userId && ud.PlaybackPositionTicks > 0)), + HasPlayed = g.Any(e => e.UserData!.Any(ud => ud.UserId == userId && ud.Played)), + HasUnplayed = g.Any(e => !e.UserData!.Any(ud => ud.UserId == userId && ud.Played)) + }); + + // A series is resumable if it has an in-progress episode, + // or if it has both played and unplayed episodes (partially watched). + var resumableSeriesIds = seriesEpisodeStats + .Where(s => s.HasInProgress || (s.HasPlayed && s.HasUnplayed)) + .Select(s => s.SeriesId); // Non-series items: resumable if PlaybackPositionTicks > 0 var resumableItemIds = context.UserData .Where(ud => ud.UserId == userId && ud.PlaybackPositionTicks > 0) .Select(ud => ud.ItemId); - // A series is resumable if it has an in-progress episode, - // or if it has both played and unplayed episodes (partially watched). baseQuery = baseQuery.Where(e => - (e.Type == seriesTypeName - && (seriesWithInProgressEpisodes.Contains(e.Id) - || (seriesWithPlayedEpisodes.Contains(e.Id) && seriesWithUnplayedEpisodes.Contains(e.Id))) - == isResumable) - || (e.Type != seriesTypeName - && resumableItemIds.Contains(e.Id) == isResumable)); + (e.Type == seriesTypeName && resumableSeriesIds.Contains(e.Id) == isResumable) + || (e.Type != seriesTypeName && resumableItemIds.Contains(e.Id) == isResumable)); } else { @@ -1024,31 +1010,34 @@ public sealed partial class BaseItemRepository .Where(e => e.SeriesPresentationUniqueKey == filter.SeriesPresentationUniqueKey); } + // Pre-build the blocked-item-id set as a sub-select if (filter.ExcludeInheritedTags.Length > 0) { var excludedTags = filter.ExcludeInheritedTags.Select(e => e.GetCleanValue()).ToArray(); + var blockedTagItemIds = context.ItemValuesMap + .Where(f => f.ItemValue.Type == ItemValueType.Tags && excludedTags.Contains(f.ItemValue.CleanValue)) + .Select(f => f.ItemId); + baseQuery = baseQuery.Where(e => - !context.ItemValuesMap.Any(f => - f.ItemValue.Type == ItemValueType.Tags - && excludedTags.Contains(f.ItemValue.CleanValue) - && (f.ItemId == e.Id - || (e.SeriesId.HasValue && f.ItemId == e.SeriesId.Value) - || e.Parents!.Any(p => f.ItemId == p.ParentItemId) - || (e.TopParentId.HasValue && f.ItemId == e.TopParentId.Value)))); + !blockedTagItemIds.Contains(e.Id) + && !(e.SeriesId.HasValue && blockedTagItemIds.Contains(e.SeriesId.Value)) + && !e.Parents!.Any(p => blockedTagItemIds.Contains(p.ParentItemId)) + && !(e.TopParentId.HasValue && blockedTagItemIds.Contains(e.TopParentId.Value))); } if (filter.IncludeInheritedTags.Length > 0) { var includeTags = filter.IncludeInheritedTags.Select(e => e.GetCleanValue()).ToArray(); var isPlaylistOnlyQuery = includeTypes.Length == 1 && includeTypes.FirstOrDefault() == BaseItemKind.Playlist; + var allowedTagItemIds = context.ItemValuesMap + .Where(f => f.ItemValue.Type == ItemValueType.Tags && includeTags.Contains(f.ItemValue.CleanValue)) + .Select(f => f.ItemId); + baseQuery = baseQuery.Where(e => - context.ItemValuesMap.Any(f => - f.ItemValue.Type == ItemValueType.Tags - && includeTags.Contains(f.ItemValue.CleanValue) - && (f.ItemId == e.Id - || (e.SeriesId.HasValue && f.ItemId == e.SeriesId.Value) - || e.Parents!.Any(p => f.ItemId == p.ParentItemId) - || (e.TopParentId.HasValue && f.ItemId == e.TopParentId.Value))) + allowedTagItemIds.Contains(e.Id) + || (e.SeriesId.HasValue && allowedTagItemIds.Contains(e.SeriesId.Value)) + || e.Parents!.Any(p => allowedTagItemIds.Contains(p.ParentItemId)) + || (e.TopParentId.HasValue && allowedTagItemIds.Contains(e.TopParentId.Value)) // A playlist should be accessible to its owner regardless of allowed tags || (isPlaylistOnlyQuery && e.Data!.Contains($"OwnerUserId\":\"{filter.User!.Id:N}\""))); -- cgit v1.2.3 From 00b08c0b32b3c8fa36330d72e4a25c7b157de4e3 Mon Sep 17 00:00:00 2001 From: Shadowghost Date: Sun, 3 May 2026 13:26:30 +0200 Subject: Omit BoxSet related materialization --- .../Item/BaseItemRepository.QueryBuilding.cs | 45 ++++++++-------------- .../Item/BaseItemRepository.TranslateQuery.cs | 17 ++++---- .../Persistence/IItemQueryHelpers.cs | 13 +++++++ 3 files changed, 37 insertions(+), 38 deletions(-) (limited to 'Jellyfin.Server.Implementations/Item/BaseItemRepository.QueryBuilding.cs') diff --git a/Jellyfin.Server.Implementations/Item/BaseItemRepository.QueryBuilding.cs b/Jellyfin.Server.Implementations/Item/BaseItemRepository.QueryBuilding.cs index 7570421e78..d6ddf8f5c8 100644 --- a/Jellyfin.Server.Implementations/Item/BaseItemRepository.QueryBuilding.cs +++ b/Jellyfin.Server.Implementations/Item/BaseItemRepository.QueryBuilding.cs @@ -495,53 +495,49 @@ public sealed partial class BaseItemRepository && (lc.Child.InheritedParentalRatingSubValue ?? 0) <= maxSubScore))))); } - private Dictionary GetPlayedAndTotalCountBatch(IReadOnlyList folderIds, User user) + /// + public IQueryable GetFullyPlayedFolderIdsQuery(JellyfinDbContext context, IQueryable folderIds, User user) { + ArgumentNullException.ThrowIfNull(context); ArgumentNullException.ThrowIfNull(folderIds); ArgumentNullException.ThrowIfNull(user); - if (folderIds.Count == 0) - { - return new Dictionary(); - } - - using var dbContext = _dbProvider.CreateDbContext(); - var folderIdsArray = folderIds.ToArray(); var filter = new InternalItemsQuery(user); var userId = user.Id; - var leafItems = dbContext.BaseItems + var leafItems = context.BaseItems + .AsNoTracking() .Where(b => !b.IsFolder && !b.IsVirtualItem); - leafItems = ApplyAccessFiltering(dbContext, leafItems, filter); + leafItems = ApplyAccessFiltering(context, leafItems, filter); var playedLeafItems = leafItems .Select(b => new { b.Id, Played = b.UserData!.Any(ud => ud.UserId == userId && ud.Played) }); - var ancestorLeaves = dbContext.AncestorIds - .WhereOneOrMany(folderIdsArray, a => a.ParentItemId) + var ancestorLeaves = context.AncestorIds + .Where(a => folderIds.Contains(a.ParentItemId)) .Join( playedLeafItems, a => a.ItemId, b => b.Id, (a, b) => new { FolderId = a.ParentItemId, b.Id, b.Played }); - var linkedLeaves = dbContext.LinkedChildren - .WhereOneOrMany(folderIdsArray, lc => lc.ParentId) + var linkedLeaves = context.LinkedChildren + .Where(lc => folderIds.Contains(lc.ParentId)) .Join( playedLeafItems, lc => lc.ChildId, b => b.Id, (lc, b) => new { FolderId = lc.ParentId, b.Id, b.Played }); - var linkedFolderLeaves = dbContext.LinkedChildren - .WhereOneOrMany(folderIdsArray, lc => lc.ParentId) + var linkedFolderLeaves = context.LinkedChildren + .Where(lc => folderIds.Contains(lc.ParentId)) .Join( - dbContext.BaseItems.Where(b => b.IsFolder), + context.BaseItems.Where(b => b.IsFolder), lc => lc.ChildId, b => b.Id, (lc, b) => new { lc.ParentId, FolderChildId = b.Id }) .Join( - dbContext.AncestorIds, + context.AncestorIds, x => x.FolderChildId, a => a.ParentItemId, (x, a) => new { x.ParentId, DescendantId = a.ItemId }) @@ -551,18 +547,11 @@ public sealed partial class BaseItemRepository b => b.Id, (x, b) => new { FolderId = x.ParentId, b.Id, b.Played }); - var results = ancestorLeaves + return ancestorLeaves .Union(linkedLeaves) .Union(linkedFolderLeaves) .GroupBy(x => x.FolderId) - .Select(g => new - { - FolderId = g.Key, - Total = g.Select(x => x.Id).Distinct().Count(), - Played = g.Where(x => x.Played).Select(x => x.Id).Distinct().Count() - }) - .ToDictionary(x => x.FolderId, x => (x.Played, x.Total)); - - return results; + .Where(g => g.Select(x => x.Id).Distinct().Count() == g.Where(x => x.Played).Select(x => x.Id).Distinct().Count()) + .Select(g => g.Key); } } diff --git a/Jellyfin.Server.Implementations/Item/BaseItemRepository.TranslateQuery.cs b/Jellyfin.Server.Implementations/Item/BaseItemRepository.TranslateQuery.cs index 9a57691fbd..0abe981af8 100644 --- a/Jellyfin.Server.Implementations/Item/BaseItemRepository.TranslateQuery.cs +++ b/Jellyfin.Server.Implementations/Item/BaseItemRepository.TranslateQuery.cs @@ -471,16 +471,13 @@ public sealed partial class BaseItemRepository .Select(g => g.Key) : Enumerable.Empty().AsQueryable(); - // BoxSet: played = all children played - IEnumerable playedBoxSetIds = []; - if (hasBoxSet) - { - var boxSetIds = baseQuery.Where(e => e.Type == boxSetTypeName).Select(e => e.Id).ToList(); - var playedCounts = GetPlayedAndTotalCountBatch(boxSetIds, filter.User!); - playedBoxSetIds = playedCounts - .Where(kvp => kvp.Value.Total > 0 && kvp.Value.Played == kvp.Value.Total) - .Select(kvp => kvp.Key); - } + // BoxSet: played = all children played. + IQueryable playedBoxSetIds = hasBoxSet + ? GetFullyPlayedFolderIdsQuery( + context, + baseQuery.Where(e => e.Type == boxSetTypeName).Select(e => e.Id), + filter.User!) + : Enumerable.Empty().AsQueryable(); // Non-folder items: check UserData directly var playedItemIds = context.UserData diff --git a/MediaBrowser.Controller/Persistence/IItemQueryHelpers.cs b/MediaBrowser.Controller/Persistence/IItemQueryHelpers.cs index 45fa92c90b..2e29cbdbba 100644 --- a/MediaBrowser.Controller/Persistence/IItemQueryHelpers.cs +++ b/MediaBrowser.Controller/Persistence/IItemQueryHelpers.cs @@ -78,6 +78,19 @@ public interface IItemQueryHelpers InternalItemsQuery filter, Guid ancestorId); + /// + /// Builds an of folder IDs whose descendants are all played + /// for the given user. Composable into outer queries to avoid an extra DB roundtrip. + /// + /// The database context the resulting query is bound to. + /// A query yielding candidate folder IDs. + /// The user for access filtering and played status. + /// An of fully-played folder IDs. + IQueryable GetFullyPlayedFolderIdsQuery( + JellyfinDbContext context, + IQueryable folderIds, + User user); + /// /// Deserializes a into a . /// -- cgit v1.2.3