Блог

Я хочу создать рекурсивный запрос в EntityFramework 6.2.0, чтобы получить сотрудника и всех его «прямых» (одного уровня) и всех других подчиненных по всей иерархии.

Я хотел использовать List<IQueryable<T>> для построения всего запроса, а затем запустить его только один раз с одним обходом к базе данных.

Вот моя попытка сделать это:

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

Но я получаю систему.NotSupportedException: ‘Не удается создать постоянное значение типа ‘EF6.Employee’. В этом контексте поддерживаются только примитивные типы или типы перечислений.’

Приведенный выше код C # был попыткой заменить следующий код SQL:

 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
  

ОБНОВЛЕНО:

Вот SQL-скрипт, который я использую:

 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
  

Вот Employee класс:

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

Ошибка связана со статическим запросом. Не уверен, зачем вам нужен запрос, когда код очень прост. Смотрите ниже

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