c# - EntityFramework 中的递归查询抛出 NotSupportedException
问题描述
我想在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());
}
}
}
但我得到一个System.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);
}
}
}
推荐阅读
- php - 从 mysql select 中计算 id 的出现并将重复项与 php 组合(不在 mysql 查询中,distinct,grouby)
- android - 覆盖 Dagger 模块中的依赖项
- xcode - 快速安装 pod 2
- sql - Ignite 无法创建以日期为主键的表
- excel - Excel 扇形图组行
- java - Java:优先队列
> 比较器和实现 - express - res.redirect() 除了使用 CTRL-R 之外不起作用:缓存问题?
- android - 房间数据库效率低下
- c# - 选择性地限制特定模块的属性访问
- php - 如何在 Laravel 测试中代表另一个会话发出 HTTP 请求?