Don't use raw SQL

5 files changed, 161 insertions, 168 deletions

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);
-    }
-}