Don't use raw SQL

6 files changed, 172 insertions, 183 deletions

diff --git a/Jellyfin.Server.Implementations/Item/BaseItemRepository.cs b/Jellyfin.Server.Implementations/Item/BaseItemRepository.cs
index e961f34a85..421519b191 100644
--- a/Jellyfin.Server.Implementations/Item/BaseItemRepository.cs
+++ b/Jellyfin.Server.Implementations/Item/BaseItemRepository.cs
@@ -74,7 +74,6 @@ public sealed class BaseItemRepository
     private readonly IItemTypeLookup _itemTypeLookup;
     private readonly IServerConfigurationManager _serverConfigurationManager;
     private readonly ILogger<BaseItemRepository> _logger;
-    private readonly IDescendantQueryProvider _descendantQueryProvider;
 
     private static readonly IReadOnlyList<ItemValueType> _getAllArtistsValueTypes = [ItemValueType.Artist, ItemValueType.AlbumArtist];
     private static readonly IReadOnlyList<ItemValueType> _getArtistValueTypes = [ItemValueType.Artist];
@@ -95,21 +94,18 @@ public sealed class BaseItemRepository
     /// <param name="itemTypeLookup">The static type lookup.</param>
     /// <param name="serverConfigurationManager">The server Configuration manager.</param>
     /// <param name="logger">System logger.</param>
-    /// <param name="databaseProvider">The database provider for database-specific operations.</param>
     public BaseItemRepository(
         IDbContextFactory<JellyfinDbContext> dbProvider,
         IServerApplicationHost appHost,
         IItemTypeLookup itemTypeLookup,
         IServerConfigurationManager serverConfigurationManager,
-        ILogger<BaseItemRepository> logger,
-        IJellyfinDatabaseProvider databaseProvider)
+        ILogger<BaseItemRepository> logger)
     {
         _dbProvider = dbProvider;
         _appHost = appHost;
         _itemTypeLookup = itemTypeLookup;
         _serverConfigurationManager = serverConfigurationManager;
         _logger = logger;
-        _descendantQueryProvider = databaseProvider.DescendantQueryProvider;
     }
 
     /// <inheritdoc />
@@ -125,7 +121,7 @@ public sealed class BaseItemRepository
 
         var date = (DateTime?)DateTime.UtcNow;
 
-        var descendantIds = ids.SelectMany(f => _descendantQueryProvider.GetAllDescendantIds(context, f)).ToHashSet();
+        var descendantIds = ids.SelectMany(f => DescendantQueryHelper.GetAllDescendantIds(context, f)).ToHashSet();
         foreach (var id in ids)
         {
             descendantIds.Add(id);
@@ -3160,7 +3156,7 @@ public sealed class BaseItemRepository
         if (!string.IsNullOrWhiteSpace(filter.HasNoAudioTrackWithLanguage))
         {
             var lang = filter.HasNoAudioTrackWithLanguage;
-            var foldersWithAudio = _descendantQueryProvider.GetFolderIdsMatching(context, new HasMediaStreamType(MediaStreamTypeEntity.Audio, lang));
+            var foldersWithAudio = DescendantQueryHelper.GetFolderIdsMatching(context, new HasMediaStreamType(MediaStreamTypeEntity.Audio, lang));
 
             baseQuery = baseQuery
                 .Where(e =>
@@ -3171,7 +3167,7 @@ public sealed class BaseItemRepository
         if (!string.IsNullOrWhiteSpace(filter.HasNoInternalSubtitleTrackWithLanguage))
         {
             var lang = filter.HasNoInternalSubtitleTrackWithLanguage;
-            var foldersWithSubtitles = _descendantQueryProvider.GetFolderIdsMatching(context, new HasMediaStreamType(MediaStreamTypeEntity.Subtitle, lang, IsExternal: false));
+            var foldersWithSubtitles = DescendantQueryHelper.GetFolderIdsMatching(context, new HasMediaStreamType(MediaStreamTypeEntity.Subtitle, lang, IsExternal: false));
 
             baseQuery = baseQuery
                 .Where(e =>
@@ -3182,7 +3178,7 @@ public sealed class BaseItemRepository
         if (!string.IsNullOrWhiteSpace(filter.HasNoExternalSubtitleTrackWithLanguage))
         {
             var lang = filter.HasNoExternalSubtitleTrackWithLanguage;
-            var foldersWithSubtitles = _descendantQueryProvider.GetFolderIdsMatching(context, new HasMediaStreamType(MediaStreamTypeEntity.Subtitle, lang, IsExternal: true));
+            var foldersWithSubtitles = DescendantQueryHelper.GetFolderIdsMatching(context, new HasMediaStreamType(MediaStreamTypeEntity.Subtitle, lang, IsExternal: true));
 
             baseQuery = baseQuery
                 .Where(e =>
@@ -3193,7 +3189,7 @@ public sealed class BaseItemRepository
         if (!string.IsNullOrWhiteSpace(filter.HasNoSubtitleTrackWithLanguage))
         {
             var lang = filter.HasNoSubtitleTrackWithLanguage;
-            var foldersWithSubtitles = _descendantQueryProvider.GetFolderIdsMatching(context, new HasMediaStreamType(MediaStreamTypeEntity.Subtitle, lang));
+            var foldersWithSubtitles = DescendantQueryHelper.GetFolderIdsMatching(context, new HasMediaStreamType(MediaStreamTypeEntity.Subtitle, lang));
 
             baseQuery = baseQuery
                 .Where(e =>
@@ -3204,7 +3200,7 @@ public sealed class BaseItemRepository
         if (filter.HasSubtitles.HasValue)
         {
             var hasSubtitles = filter.HasSubtitles.Value;
-            var foldersWithSubtitles = _descendantQueryProvider.GetFolderIdsMatching(context, new HasSubtitles());
+            var foldersWithSubtitles = DescendantQueryHelper.GetFolderIdsMatching(context, new HasSubtitles());
             if (hasSubtitles)
             {
                 baseQuery = baseQuery
@@ -3224,7 +3220,7 @@ public sealed class BaseItemRepository
         if (filter.HasChapterImages.HasValue)
         {
             var hasChapterImages = filter.HasChapterImages.Value;
-            var foldersWithChapterImages = _descendantQueryProvider.GetFolderIdsMatching(context, new HasChapterImages());
+            var foldersWithChapterImages = DescendantQueryHelper.GetFolderIdsMatching(context, new HasChapterImages());
             if (hasChapterImages)
             {
                 baseQuery = baseQuery
@@ -3593,7 +3589,7 @@ public sealed class BaseItemRepository
 
         if (recursive)
         {
-            var descendantIds = _descendantQueryProvider.GetAllDescendantIds(dbContext, id);
+            var descendantIds = DescendantQueryHelper.GetAllDescendantIds(dbContext, id);
 
             return dbContext.BaseItems
                     .Where(e => descendantIds.Contains(e.Id) && !e.IsFolder && !e.IsVirtualItem)
@@ -3640,7 +3636,7 @@ public sealed class BaseItemRepository
         ArgumentNullException.ThrowIfNull(filter.User);
         using var dbContext = _dbProvider.CreateDbContext();
 
-        var allDescendantIds = _descendantQueryProvider.GetAllDescendantIds(dbContext, parentId);
+        var allDescendantIds = DescendantQueryHelper.GetAllDescendantIds(dbContext, parentId);
         var baseQuery = dbContext.BaseItems
             .Where(b => allDescendantIds.Contains(b.Id) && !b.IsFolder && !b.IsVirtualItem);
         baseQuery = ApplyAccessFiltering(dbContext, baseQuery, filter);
@@ -3735,7 +3731,7 @@ public sealed class BaseItemRepository
         Guid ancestorId)
     {
         // Use recursive CTE to get all descendants (hierarchical and linked)
-        var allDescendantIds = _descendantQueryProvider.GetAllDescendantIds(context, ancestorId);
+        var allDescendantIds = DescendantQueryHelper.GetAllDescendantIds(context, ancestorId);
 
         var baseQuery = context.BaseItems
             .Where(b => allDescendantIds.Contains(b.Id) && !b.IsFolder && !b.IsVirtualItem);
diff --git a/src/Jellyfin.Database/Jellyfin.Database.Implementations/DescendantQueryHelper.cs b/src/Jellyfin.Database/Jellyfin.Database.Implementations/DescendantQueryHelper.cs
new file mode 100644
index 0000000000..e6fa6ca458
--- /dev/null
+++ b/src/Jellyfin.Database/Jellyfin.Database.Implementations/DescendantQueryHelper.cs
@@ -0,0 +1,161 @@
+using System;
+using System.Collections.Generic;
+using System.Linq;
+using Jellyfin.Database.Implementations.Entities;
+using Jellyfin.Database.Implementations.MatchCriteria;
+
+namespace Jellyfin.Database.Implementations;
+
+/// <summary>
+/// Provides methods for querying item hierarchies using iterative traversal.
+/// Uses AncestorIds and LinkedChildren tables for parent-child traversal.
+/// </summary>
+public static class DescendantQueryHelper
+{
+    /// <summary>
+    /// Gets a queryable of all descendant IDs for a parent item.
+    /// Traverses AncestorIds and LinkedChildren to find all descendants.
+    /// </summary>
+    /// <param name="context">Database context.</param>
+    /// <param name="parentId">Parent item ID.</param>
+    /// <returns>Queryable of descendant item IDs.</returns>
+    public static IQueryable<Guid> GetAllDescendantIds(JellyfinDbContext context, Guid parentId)
+    {
+        ArgumentNullException.ThrowIfNull(context);
+
+        var descendants = TraverseHierarchyDown(context, [parentId]);
+
+        descendants.Remove(parentId);
+
+        return descendants.AsQueryable();
+    }
+
+    /// <summary>
+    /// Gets a queryable of all folder IDs that have any descendant matching the specified criteria.
+    /// Can be used in LINQ .Contains() expressions.
+    /// </summary>
+    /// <param name="context">Database context.</param>
+    /// <param name="criteria">The matching criteria to apply.</param>
+    /// <returns>Queryable of folder IDs.</returns>
+    public static IQueryable<Guid> GetFolderIdsMatching(JellyfinDbContext context, FolderMatchCriteria criteria)
+    {
+        ArgumentNullException.ThrowIfNull(context);
+        ArgumentNullException.ThrowIfNull(criteria);
+        var matchingItemIds = criteria switch
+        {
+            HasSubtitles => context.MediaStreamInfos
+                .Where(ms => ms.StreamType == MediaStreamTypeEntity.Subtitle)
+                .Select(ms => ms.ItemId)
+                .Distinct()
+                .ToHashSet(),
+            HasChapterImages => context.Chapters
+                .Where(c => c.ImagePath != null)
+                .Select(c => c.ItemId)
+                .Distinct()
+                .ToHashSet(),
+            HasMediaStreamType m => GetMatchingMediaStreamItemIds(context, m),
+            _ => throw new ArgumentOutOfRangeException(nameof(criteria), $"Unknown criteria type: {criteria.GetType().Name}")
+        };
+
+        var ancestors = TraverseHierarchyUp(context, matchingItemIds);
+
+        return ancestors.AsQueryable();
+    }
+
+    private static HashSet<Guid> GetMatchingMediaStreamItemIds(JellyfinDbContext context, HasMediaStreamType criteria)
+    {
+        var query = context.MediaStreamInfos
+            .Where(ms => ms.StreamType == criteria.StreamType && ms.Language == criteria.Language);
+
+        if (criteria.IsExternal.HasValue)
+        {
+            var isExternal = criteria.IsExternal.Value;
+            query = query.Where(ms => ms.IsExternal == isExternal);
+        }
+
+        return query.Select(ms => ms.ItemId).Distinct().ToHashSet();
+    }
+
+    /// <summary>
+    /// Traverses DOWN the hierarchy from parent folders to find all descendants.
+    /// </summary>
+    private static HashSet<Guid> TraverseHierarchyDown(JellyfinDbContext context, ICollection<Guid> startIds)
+    {
+        var visited = new HashSet<Guid>(startIds);
+        var folderStack = new HashSet<Guid>(startIds);
+
+        while (folderStack.Count != 0)
+        {
+            var currentFolders = folderStack.ToArray();
+            folderStack.Clear();
+
+            var directChildren = context.AncestorIds
+                .WhereOneOrMany(currentFolders, e => e.ParentItemId)
+                .Select(e => e.ItemId)
+                .ToArray();
+
+            var linkedChildren = context.LinkedChildren
+                .WhereOneOrMany(currentFolders, e => e.ParentId)
+                .Select(e => e.ChildId)
+                .ToArray();
+
+            var allChildren = directChildren.Concat(linkedChildren).Distinct().ToArray();
+
+            if (allChildren.Length == 0)
+            {
+                break;
+            }
+
+            var childFolders = context.BaseItems
+                .WhereOneOrMany(allChildren, e => e.Id)
+                .Where(e => e.IsFolder)
+                .Select(e => e.Id)
+                .ToHashSet();
+
+            foreach (var childId in allChildren)
+            {
+                if (visited.Add(childId) && childFolders.Contains(childId))
+                {
+                    folderStack.Add(childId);
+                }
+            }
+        }
+
+        return visited;
+    }
+
+    /// <summary>
+    /// Traverses UP the hierarchy from items to find all ancestor folders.
+    /// </summary>
+    private static HashSet<Guid> TraverseHierarchyUp(JellyfinDbContext context, ICollection<Guid> startIds)
+    {
+        var ancestors = new HashSet<Guid>();
+        var itemStack = new HashSet<Guid>(startIds);
+
+        while (itemStack.Count != 0)
+        {
+            var currentItems = itemStack.ToArray();
+            itemStack.Clear();
+
+            var ancestorParents = context.AncestorIds
+                .WhereOneOrMany(currentItems, e => e.ItemId)
+                .Select(e => e.ParentItemId)
+                .ToArray();
+
+            var linkedParents = context.LinkedChildren
+                .WhereOneOrMany(currentItems, e => e.ChildId)
+                .Select(e => e.ParentId)
+                .ToArray();
+
+            foreach (var parentId in ancestorParents.Concat(linkedParents))
+            {
+                if (ancestors.Add(parentId))
+                {
+                    itemStack.Add(parentId);
+                }
+            }
+        }
+
+        return ancestors;
+    }
+}
diff --git a/src/Jellyfin.Database/Jellyfin.Database.Implementations/IDescendantQueryProvider.cs b/src/Jellyfin.Database/Jellyfin.Database.Implementations/IDescendantQueryProvider.cs
deleted file mode 100644
index 9e3d510b9c..0000000000
--- a/src/Jellyfin.Database/Jellyfin.Database.Implementations/IDescendantQueryProvider.cs
+++ /dev/null
@@ -1,30 +0,0 @@
-using System;
-using System.Linq;
-using Jellyfin.Database.Implementations.MatchCriteria;
-
-namespace Jellyfin.Database.Implementations;
-
-/// <summary>
-/// Provider interface for descendant queries using recursive CTEs.
-/// Each database provider implements this with provider-specific SQL.
-/// </summary>
-public interface IDescendantQueryProvider
-{
-    /// <summary>
-    /// Gets a queryable of all descendant IDs for a parent item.
-    /// Uses recursive CTE to traverse AncestorIds and LinkedChildren infinitely.
-    /// </summary>
-    /// <param name="context">Database context.</param>
-    /// <param name="parentId">Parent item ID.</param>
-    /// <returns>Queryable of descendant item IDs.</returns>
-    IQueryable<Guid> GetAllDescendantIds(JellyfinDbContext context, Guid parentId);
-
-    /// <summary>
-    /// Gets a queryable of all folder IDs that have any descendant matching the specified criteria.
-    /// Uses recursive CTE for infinite depth traversal. Can be used in LINQ .Contains() expressions.
-    /// </summary>
-    /// <param name="context">Database context.</param>
-    /// <param name="criteria">The matching criteria to apply.</param>
-    /// <returns>Queryable of folder IDs.</returns>
-    IQueryable<Guid> GetFolderIdsMatching(JellyfinDbContext context, FolderMatchCriteria criteria);
-}
diff --git a/src/Jellyfin.Database/Jellyfin.Database.Implementations/IJellyfinDatabaseProvider.cs b/src/Jellyfin.Database/Jellyfin.Database.Implementations/IJellyfinDatabaseProvider.cs
index f52a68c684..27dbeaba6a 100644
--- a/src/Jellyfin.Database/Jellyfin.Database.Implementations/IJellyfinDatabaseProvider.cs
+++ b/src/Jellyfin.Database/Jellyfin.Database.Implementations/IJellyfinDatabaseProvider.cs
@@ -18,12 +18,6 @@ public interface IJellyfinDatabaseProvider
     IDbContextFactory<JellyfinDbContext>? DbContextFactory { get; set; }
 
     /// <summary>
-    /// Gets the descendant query provider for this database type.
-    /// Used for recursive CTE queries to find all descendants of an item.
-    /// </summary>
-    IDescendantQueryProvider DescendantQueryProvider { get; }
-
-    /// <summary>
     /// Initialises jellyfins EFCore database access.
     /// </summary>
     /// <param name="options">The EFCore database options.</param>
diff --git a/src/Jellyfin.Database/Jellyfin.Database.Providers.Sqlite/SqliteDatabaseProvider.cs b/src/Jellyfin.Database/Jellyfin.Database.Providers.Sqlite/SqliteDatabaseProvider.cs
index 94c470e6cb..da63df8e29 100644
--- a/src/Jellyfin.Database/Jellyfin.Database.Providers.Sqlite/SqliteDatabaseProvider.cs
+++ b/src/Jellyfin.Database/Jellyfin.Database.Providers.Sqlite/SqliteDatabaseProvider.cs
@@ -40,9 +40,6 @@ public sealed class SqliteDatabaseProvider : IJellyfinDatabaseProvider
     public IDbContextFactory<JellyfinDbContext>? DbContextFactory { get; set; }
 
     /// <inheritdoc/>
-    public IDescendantQueryProvider DescendantQueryProvider { get; } = new SqliteDescendantQueryProvider();
-
-    /// <inheritdoc/>
     public void Initialise(DbContextOptionsBuilder options, DatabaseConfigurationOptions databaseConfiguration)
     {
         static T? GetOption<T>(ICollection<CustomDatabaseOption>? options, string key, Func<string, T> converter, Func<T>? defaultValue = null)
diff --git a/src/Jellyfin.Database/Jellyfin.Database.Providers.Sqlite/SqliteDescendantQueryProvider.cs b/src/Jellyfin.Database/Jellyfin.Database.Providers.Sqlite/SqliteDescendantQueryProvider.cs
deleted file mode 100644
index 756f750bf9..0000000000
--- a/src/Jellyfin.Database/Jellyfin.Database.Providers.Sqlite/SqliteDescendantQueryProvider.cs
+++ /dev/null
@@ -1,129 +0,0 @@
-using System;
-using System.Linq;
-using Jellyfin.Database.Implementations;
-using Jellyfin.Database.Implementations.Entities;
-using Jellyfin.Database.Implementations.MatchCriteria;
-using Microsoft.EntityFrameworkCore;
-
-namespace Jellyfin.Database.Providers.Sqlite;
-
-/// <summary>
-/// SQLite implementation of descendant queries using optimized ancestor lookups.
-/// Uses AncestorIds and LinkedChildren tables for efficient parent-child traversal.
-/// </summary>
-public class SqliteDescendantQueryProvider : IDescendantQueryProvider
-{
-    /// <summary>
-    /// Recursive CTE fragment that traverses UP the tree from matching items to find all ancestor folders.
-    /// Expects a preceding CTE named "MatchingItems" with an ItemId column.
-    /// </summary>
-    private const string AllAncestorsCte = """
-        AllAncestors AS (
-            SELECT a.ParentItemId AS AncestorId
-            FROM AncestorIds a
-            WHERE a.ItemId IN (SELECT ItemId FROM MatchingItems)
-            UNION
-            SELECT lc.ParentId AS AncestorId
-            FROM LinkedChildren lc
-            WHERE lc.ChildId IN (SELECT ItemId FROM MatchingItems)
-            UNION
-            SELECT a.ParentItemId AS AncestorId
-            FROM AllAncestors aa
-            INNER JOIN AncestorIds a ON a.ItemId = aa.AncestorId
-            UNION
-            SELECT lc.ParentId AS AncestorId
-            FROM AllAncestors aa
-            INNER JOIN LinkedChildren lc ON lc.ChildId = aa.AncestorId
-        )
-        SELECT DISTINCT AncestorId AS Value FROM AllAncestors
-        """;
-
-    /// <inheritdoc />
-    public IQueryable<Guid> GetAllDescendantIds(JellyfinDbContext context, Guid parentId)
-    {
-        ArgumentNullException.ThrowIfNull(context);
-
-        var sql = """
-            WITH RECURSIVE AllDescendants AS (
-                SELECT ItemId FROM AncestorIds WHERE ParentItemId = {0}
-                UNION
-                SELECT ChildId AS ItemId FROM LinkedChildren WHERE ParentId = {0}
-                UNION ALL
-                SELECT a.ItemId
-                FROM AllDescendants d
-                INNER JOIN BaseItems b ON b.Id = d.ItemId AND b.IsFolder = 1
-                INNER JOIN AncestorIds a ON a.ParentItemId = d.ItemId
-                UNION ALL
-                SELECT lc.ChildId AS ItemId
-                FROM AllDescendants d
-                INNER JOIN BaseItems b ON b.Id = d.ItemId AND b.IsFolder = 1
-                INNER JOIN LinkedChildren lc ON lc.ParentId = d.ItemId
-            )
-            SELECT DISTINCT ItemId AS Value FROM AllDescendants
-            """;
-
-        return context.Database.SqlQueryRaw<Guid>(sql, parentId);
-    }
-
-    /// <inheritdoc />
-    public IQueryable<Guid> GetFolderIdsMatching(JellyfinDbContext context, FolderMatchCriteria criteria)
-    {
-        ArgumentNullException.ThrowIfNull(context);
-        ArgumentNullException.ThrowIfNull(criteria);
-
-        return criteria switch
-        {
-            HasSubtitles => GetFolderIdsWithSubtitles(context),
-            HasChapterImages => GetFolderIdsWithChapterImages(context),
-            HasMediaStreamType m => GetFolderIdsWithMediaStream(context, m.StreamType, m.Language, m.IsExternal),
-            _ => throw new ArgumentOutOfRangeException(nameof(criteria), $"Unknown criteria type: {criteria.GetType().Name}")
-        };
-    }
-
-    private IQueryable<Guid> GetFolderIdsWithSubtitles(JellyfinDbContext context)
-    {
-        var sql = $"""
-            WITH RECURSIVE MatchingItems AS (
-                SELECT DISTINCT ms.ItemId FROM MediaStreamInfos ms WHERE ms.StreamType = 2
-            ),
-            {AllAncestorsCte}
-            """;
-
-        return context.Database.SqlQueryRaw<Guid>(sql);
-    }
-
-    private IQueryable<Guid> GetFolderIdsWithChapterImages(JellyfinDbContext context)
-    {
-        var sql = $"""
-            WITH RECURSIVE MatchingItems AS (
-                SELECT DISTINCT c.ItemId FROM Chapters c WHERE c.ImagePath IS NOT NULL
-            ),
-            {AllAncestorsCte}
-            """;
-
-        return context.Database.SqlQueryRaw<Guid>(sql);
-    }
-
-    private IQueryable<Guid> GetFolderIdsWithMediaStream(JellyfinDbContext context, MediaStreamTypeEntity streamType, string language, bool? isExternal)
-    {
-        ArgumentNullException.ThrowIfNull(language);
-
-        var streamTypeInt = (int)streamType;
-        var externalCondition = isExternal switch
-        {
-            true => " AND ms.IsExternal = 1",
-            false => " AND ms.IsExternal = 0",
-            null => string.Empty
-        };
-
-        var sql = $$"""
-            WITH RECURSIVE MatchingItems AS (
-                SELECT DISTINCT ms.ItemId FROM MediaStreamInfos ms
-                WHERE ms.StreamType = {0} AND ms.Language = {1}{{externalCondition}}
-            ),
-            {{AllAncestorsCte}}
-            """;
-
-        return context.Database.SqlQueryRaw<Guid>(sql, streamTypeInt, language);
-    }
-}