Как получить цепочку руководителей вверх по иерархии в базе данных

Что проверяют

Знаете ли вы рекурсивные CTE и умеете ли применить их к таблице, где сотрудник ссылается на руководителя. От C#-разработчика ждут ещё и способ выполнить это из кода. Следом спрашивают про защиту от циклов и альтернативные способы хранить иерархию.

Как отвечать

Таблица Employees(Id, ManagerId), где ManagerId ссылается на ту же таблицу. Рекурсивный CTE состоит из двух частей, объединённых UNION ALL:

• якорь: сам сотрудник, SELECT Id, ManagerId, 0 AS Level FROM Employees WHERE Id = @id;
• рекурсивная часть: соединение таблицы с самим CTE по условию e.Id = chain.ManagerId, уровень плюс один. Рекурсия останавливается, когда ManagerId очередного руководителя равен NULL;
• результат: WHERE Level > 0 ORDER BY Level, чтобы убрать самого сотрудника и получить цепочку снизу вверх.

Синтаксис зависит от СУБД. В SQL Server пишут просто WITH chain AS (...), слова RECURSIVE там нет, и запрос с ним не выполнится. В PostgreSQL и MySQL 8, наоборот, нужен WITH RECURSIVE. SQL Server по умолчанию обрывает рекурсию на глубине 100, лимит меняется через OPTION (MAXRECURSION n).

Из C#: LINQ рекурсивный CTE не строит, поэтому в EF Core выполняют сырой SQL через FromSql или Database.SqlQuery<T> с интерполированными параметрами, которые уходят в запрос параметрами, а не склейкой строк. Вариант попроще: Dapper. Драйвер сейчас Microsoft.Data.SqlClient, старый System.Data.SqlClient устарел. Индекс по ManagerId обязателен.

Если иерархию читают часто, назовите альтернативы: тип hierarchyid в SQL Server с методами GetAncestor и IsDescendantOf, материализованный путь, таблица замыкания.

Где спотыкаются

Путают направление соединения. Условие e.ManagerId = chain.Id ведёт вниз, к подчинённым, а для цепочки начальников нужно наоборот.

Не защищаются от циклов. Если из-за ошибки в данных A подчиняется B, а B подчиняется A, запрос крутится до лимита рекурсии и падает. Помогает ограничение по уровню или накопленный путь с проверкой.

Грузят руководителей в цикле, запросом на каждый уровень. Для глубокой иерархии это десяток обращений к базе вместо одного.