Recursive query in EntityFramework throws NotSupportedException

Question

I want to build a recursive query in EntityFramework 6.2.0 to get an employee and all his "direct" (one level) and all other subordinates down all the hierarchy.

My point was to use List<IQueryable<T>> to build a whole query and then run it just one time with one rount-trip to the database.

Here is my attempt to do it:

private static List<IQueryable<Employee>> queryables = new List<IQueryable<Employee>>();

static void Main(string[] args)
{
    using (var db = new EmployeeContext())
    {
        IQueryable<Employee> managers = db.Employee.Where(x => x.Id == 1);
        GetSlaves(managers);

        // the System.NotSupportedException occurs in this line
        IQueryable<Employee> employees = queryables.Aggregate(Queryable.Union);

        // but throws here
        var res = (
            from e in employees
            join d in db.EmployeeDoc on e.Id equals d.EmployeeId
            select new { e.Id, e.EmployeeName, d.DocNumber }).ToList();
    }

    Console.ReadLine();
}

static void GetSlaves(IQueryable<Employee> managers)
{
    if (managers != null)
    {
        queryables.Add(managers);

        foreach (var m in managers)
        {
            Console.WriteLine($"{m.Id} {m.EmployeeName} {m.Position}");
            GetSlaves(m.Slaves.AsQueryable());
        }
    }
}

But I get an System.NotSupportedException: 'Unable to create a constant value of type 'EF6.Employee'. Only primitive types or enumeration types are supported in this context.'

The above C# code was an attempt to replace the following SQL code:

declare @managerId int = 1

;with Employees(Id, EmployeeName)
as
(
    select e.Id, e.EmployeeName from Employee as e
    where Id = @managerId
    union all 
    select e.Id, e.EmployeeName from Employee as e
    inner join  Employees as em on e.ManagerId = em.Id
)

select e.Id, e.EmployeeName, d.DocNumber
from Employees e
inner join EmployeeDocuments d on e.Id = d.EmployeeId

UPDATED:

Here is a SQL script that I use:

create table Employee
(
    Id int not null identity(1,1) primary key,
    EmployeeName varchar(20),
    Position varchar(30),
    ManagerId int constraint FK_Employee foreign key references Employee(Id)
)

insert into Employee (EmployeeName, Position, ManagerId) values
('John', 'CEO', NULL),
('Marry', 'Head of sales division', 1),
('Mike', 'Head of HR division', 1),
('Jack', 'Sales manager', 2),
('Olivia', 'Sales manager', 2),
('Sophia', 'Sales manager', 2),
('Nadya', 'HR manager', 3),
('Tim', 'HR manager', 3),
('Jim', 'Salesman', 4),
('Sergey', 'Salesman', 4),
('Dmitry', 'Salesman', 5),
('Irina', 'Salesman', 5),
('William', 'Assistant', 8)

select * from Employee

Create table EmployeeDocuments
(
    Id int not null identity(1,1) primary key,
    DocNumber varchar(20),
    EmployeeId int not null constraint FK_Docs_Employee foreign key references Employee(Id)
)

insert into EmployeeDocuments (DocNumber, EmployeeId) values
('1/2019-01-15', 1), ('3/2019-02-25', 3), ('4/2019-01-31', 4), ('9/2019-02-28', 9)

select * from EmployeeDocuments

Here is Employee class:

[Table("Employee")]
public partial class Employee
{
    public Employee()
    {
        Slaves = new HashSet<Employee>();
    }

    public int Id { get; set; }

    [StringLength(20)]
    public string EmployeeName { get; set; }

    [StringLength(30)]
    public string Position { get; set; }

    public int? ManagerId { get; set; }

    public virtual ICollection<Employee> Slaves { get; set; }

    public virtual Employee Manager { get; set; }

    public virtual ICollection<EmployeeDoc> EmployeeDocs { get; set; }
}

Answer 1

The error is due to the query being static. Not sure why you need a query when the code is very simple. See below

    class Program
    {
        static void Main(string[] args)
        {
            Employee.GetSlaves();

        }
    }
    public partial class Employee
    {
        public static List<Employee> employees = new List<Employee>() {
            new Employee() {Id = 1, EmployeeName =  "John",  Position =  "CEO", ManagerId = null},
            new Employee() {Id = 2,EmployeeName =  "Marry", Position =  "Head of sales division", ManagerId = 1},
            new Employee() {Id = 3,EmployeeName =  "Mike", Position =  "Head of HR division", ManagerId = 1},
            new Employee() {Id = 4,EmployeeName =  "Jack", Position =  "Sales manager", ManagerId = 2},
            new Employee() {Id = 5,EmployeeName =  "Olivia", Position =  "Sales manager", ManagerId = 2},
            new Employee() {Id = 6,EmployeeName =  "Sophia", Position =  "Sales manager", ManagerId = 2},
            new Employee() {Id = 7,EmployeeName =  "Nadya", Position =  "HR manager", ManagerId = 3},
            new Employee() {Id = 8,EmployeeName =  "Tim", Position =  "HR manager", ManagerId = 3},
            new Employee() {Id = 9,EmployeeName =  "Jim", Position =  "Salesman", ManagerId = 4},
            new Employee() {Id = 10,EmployeeName =  "Sergey", Position =  "Salesman", ManagerId = 4},
            new Employee() {Id = 11,EmployeeName =  "Dmitry", Position =  "Salesman", ManagerId = 5},
            new Employee() {Id = 12,EmployeeName =  "Irina", Position =  "Salesman", ManagerId = 5},
            new Employee() {Id = 13,EmployeeName =  "William", Position =  "Assistant", ManagerId = 8}
        };

        public static void GetSlaves()
        {
            Employee ceo = employees.Where(x => x.ManagerId == null).First();
            GetSlavesRecursive(ceo);
        }

        public int Id { get; set; }


        public string EmployeeName { get; set; }


        public string Position { get; set; }

        public int? ManagerId { get; set; }

        public virtual ICollection<Employee> Slaves { get; set; }

        public virtual Employee Manager { get; set; }

        //public virtual ICollection<EmployeeDoc> EmployeeDocs { get; set; }

        static void GetSlavesRecursive(Employee manager)
        { 
            manager.Slaves  = employees.Where(x => x.ManagerId == manager.Id).ToList();
            foreach (Employee slave in manager.Slaves)
            {
                GetSlavesRecursive(slave);
            }
        }
    }