1

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; }
}
7
  • Can you please post your Employee model? I think it would be much simpler, if this class was self-referencing with a Parent employee, the manager. And an ICollection<Employee> of subordinates. Commented Mar 8, 2019 at 15:34
  • @Marco, I've updated the answer. Commented Mar 8, 2019 at 15:47
  • You should already have everything in place to get a complete hierachy using context.Employees.Include(emp => emp.Slaves).Where(...).ToList() Commented Mar 8, 2019 at 15:50
  • This gives only "direct" subordinates (only one level) but I need to get all the subordinates down all the hierarchy (all levels) Commented Mar 8, 2019 at 15:58
  • What type database are you using? Does database allow sharing? Commented Mar 8, 2019 at 16:15

1 Answer 1

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);
            }
        }
    }
Sign up to request clarification or add additional context in comments.

2 Comments

As far as I understand your solution will result in many database round-trips. Though in my case the hierarchy is not so deep, I would prefer to use IQueryable<T> to build a whole query and run it with just one round-trip to the database.
It depends when you load employees. If you use the database for the list then you are correct. If you query the database and fill the employee list in c# then you only do one query to the database. In either case at each level you have to do a query to get all the slaves of the manager.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.