实体框架层次结构 [英] Entity Framework HierarchyId Workarounds
问题描述
EF 5.0
我正在研究一个原型，以一起测试层次结构和实体框架。我有以下模式：
创建表dbo.Employee
（
EmployeeId int identity not null，
名称nvarchar（100）not null，
节点hierarchyid not null，
NodePath作为Node.ToString（）持久化，
级别AS Node.GetLevel（）持久化，
ManagerNode作为Node.GetAncestor（1）持久化，
ManagerNodePath作为Node.GetAncestor（1）.ToString（）持久化
）;
更改表dbo.Employee
添加约束EmployeePK主键NonClustered（EmployeeId）;
Go
- Enforce Hierarchy
更改表dbo.Employee
添加约束EmployeeManagerNodeNodeFK外键（ManagerNode）参考Employee（Node）;
Go
在dbo.Employee（Node）上创建唯一的聚簇索引EmployeeDepthFirstIndex;
Go
在dbo.Employee（Level，Node）上创建NonClustered Index EmployeeBreathFirstIndex;
Go
从我的阅读中，目前不支持hierarchyid数据类型在EF中，但有些已经建议了解决方法，例如创建计算列（Node.ToString（）），我已经在上面做了。
有没有办法设置EF，以便识别父/子关系，所以我可以有效地拥有下属集合？例如
Employee.Subordinates
我唯一可以想到的是创建一个具有FK的ManagerId列，但是我有效地将层次结构存储在两个位置。
感谢任何帮助！
EF6现在是开源，所以很容易添加HierarcyID支持。我也添加了它。
您可以从codeplex下载修改后的源代码和已签名的dll：
http ：//entityframework.codeplex.com/SourceControl/network/forks/zgabi/efhierarchyidrc1 （有时叉名更改）
或从NuGet： https://www.nuget.org/packages/EntityFrameworkWithHierarchyId/
当前的EF6处于RC1状态，但我会将修改合并到每个后续版本的EF6
我有以下模型：
public class Employee
{
public int EmployeeId {get;组; }
[必需，最大长度（100）]
public string Name {get;组; }
[必需]
public HierarchyId Node {get;组; }
public IQueryable< Employee> GetSubordinates（MyContext context）
{
return context.Employees.Where（o => Node == o.Node.GetAncestor（1））;
}
}
public class MyContextInitializer：CreateDatabaseIfNotExists< MyContext>
{
protected override void Seed（MyContext context）
{
context.Database.ExecuteSqlCommand（
ALTER TABLE [dbo]。[Employees] ADD [ManagerNode] AS（[Node]。[GetAncestor]（（1）））PERSISTED）;
context.Database.ExecuteSqlCommand（
ALTER TABLE [dbo]。[Employees] ADD CONSTRAINT [UK_EmployeeNode] UNIQUE NONCLUSTERED（Node））;
context.Database.ExecuteSqlCommand（
ALTER TABLE [dbo]。[Employees] WITH CHECK ADD CONSTRAINT [EmployeeManagerNodeNodeFK]+
FOREIGN KEY（[ManagerNode]）REFERENCES [dbo]。 [员工]（[节点]））;
context.Employees.Add（new Employee {Name =Root，Node = new HierarchyId（/）}）;
context.Employees.Add（new Employee {Name =Emp1，Node = new HierarchyId（/ 1 /）}）;
context.Employees.Add（new Employee {Name =Emp2，Node = new HierarchyId（/ 2 /）}）;
context.Employees.Add（new Employee {Name =Emp3，Node = new HierarchyId（/ 1/1 /）}）;
context.Employees.Add（new Employee {Name =Emp4，Node = new HierarchyId（/ 1/1/1 /）}）;
context.Employees.Add（new Employee {Name =Emp5，Node = new HierarchyId（/ 2/1 /）}）;
context.Employees.Add（new Employee {Name =Emp6，Node = new HierarchyId（/ 1/2 /）}）;
}
}
public class MyContext：DbContext
{
public DbSet< Employee>员工{get;组;
}
生成的数据库：
（
[EmployeeId] [int] IDENTITY（1,1）NOT NULL，
[名称] [nvarchar] ]（100）NOT NULL，
[Node] [hierarchyid] NOT NULL，
[ManagerNode] AS（[Node]。[GetAncestor]（（1）））PERSISTED，
CONSTRAINT [ PK_dbo.Employees] PRIMARY KEY CLUSTERED
（
[EmployeeId] ASC
）WITH（PAD_INDEX = OFF，STATISTICS_NORECOMPUTE = OFF，IGNORE_DUP_KEY = OFF，ALLOW_ROW_LOCKS = ON，ALLOW_PAGE_LOCKS = ON）ON [PRIMARY ]
CONSTRAINT [UK_EmployeeNode] UNIQUE NONCLUSTERED
（
[Node] ASC
）WITH（PAD_INDEX = OFF，STATISTICS_NORECOMPUTE = OFF，IGNORE_DUP_KEY = OFF，ALLOW_ROW_LOCKS = ON，ALLOW_PAGE_LOCKS = ON] ON [PRIMARY]
）ON [PRIMARY]
ALTER TABLE [dbo]。[Employees] WITH CHECK ADD CONSTRAINT [EmployeeManagerNodeNodeFK] FOREIGN KEY（[ManagerNode]）
参考[dbo]。[Employees]（[Node]）
Exa mple获取Emp1员工的子节点：
using（var c = new MyContext（））
{
var firstItem = c.Employees.Single（o => o.Node == new HierarchyId（/ 1 /））;
foreach（var table1 in firstItem.GetSubordinates（c））
{
Console.WriteLine（table1.EmployeeId ++ table1.Name）;
}
}
结果：
4 Emp3
7 Emp6
EF 5.0
I am working on a prototype to test hierarchyid and entity framework together. I have the following schema:
Create Table dbo.Employee
(
EmployeeId int identity not null,
Name nvarchar(100) not null,
Node hierarchyid not null,
NodePath as Node.ToString() persisted,
Level AS Node.GetLevel() persisted,
ManagerNode as Node.GetAncestor(1) persisted,
ManagerNodePath as Node.GetAncestor(1).ToString() persisted
);
Alter Table dbo.Employee
Add Constraint EmployeePK Primary Key NonClustered (EmployeeId);
Go
--Enforce Hierarchy
Alter Table dbo.Employee
Add Constraint EmployeeManagerNodeNodeFK Foreign Key (ManagerNode) References Employee(Node);
Go
Create Unique Clustered Index EmployeeDepthFirstIndex on dbo.Employee(Node);
Go
Create NonClustered Index EmployeeBreathFirstIndex on dbo.Employee(Level, Node);
Go
From my reading, the hierarchyid datatype isn't currently supported in EF, but some have suggested workarounds such as creating calculated columns (Node.ToString()) which I have done above.
Is there a way to setup EF so that it recognizes the Parent/Child relationship so I can effectively have a subordinates collection? e.g.
Employee.Subordinates
The only thing I can think of is to create a ManagerId column w/ a FK, but then I am effectively storing the hierarchy in two places.
Thanks for any help!
EF6 is now open source, so it is easy to add HierarcyID support. I have added it, too. You can download the modifed source and the complied/signed dlls from codeplex: http://entityframework.codeplex.com/SourceControl/network/forks/zgabi/efhierarchyidrc1 (sometimes the fork name changes) Or from NuGet: https://www.nuget.org/packages/EntityFrameworkWithHierarchyId/ Currenty EF6 is in RC1 state, but I'll merge the modifications to every later releases of EF6.
I have the following model:
public class Employee
{
public int EmployeeId { get; set; }
[Required, MaxLength(100)]
public string Name { get; set; }
[Required]
public HierarchyId Node { get; set; }
public IQueryable<Employee> GetSubordinates(MyContext context)
{
return context.Employees.Where(o => Node == o.Node.GetAncestor(1));
}
}
public class MyContextInitializer : CreateDatabaseIfNotExists<MyContext>
{
protected override void Seed(MyContext context)
{
context.Database.ExecuteSqlCommand(
"ALTER TABLE [dbo].[Employees] ADD [ManagerNode] AS ([Node].[GetAncestor]((1))) PERSISTED");
context.Database.ExecuteSqlCommand(
"ALTER TABLE [dbo].[Employees] ADD CONSTRAINT [UK_EmployeeNode] UNIQUE NONCLUSTERED (Node)");
context.Database.ExecuteSqlCommand(
"ALTER TABLE [dbo].[Employees] WITH CHECK ADD CONSTRAINT [EmployeeManagerNodeNodeFK] " +
"FOREIGN KEY([ManagerNode]) REFERENCES [dbo].[Employees] ([Node])");
context.Employees.Add(new Employee { Name = "Root", Node = new HierarchyId("/") });
context.Employees.Add(new Employee { Name = "Emp1", Node = new HierarchyId("/1/") });
context.Employees.Add(new Employee { Name = "Emp2", Node = new HierarchyId("/2/") });
context.Employees.Add(new Employee { Name = "Emp3", Node = new HierarchyId("/1/1/") });
context.Employees.Add(new Employee { Name = "Emp4", Node = new HierarchyId("/1/1/1/") });
context.Employees.Add(new Employee { Name = "Emp5", Node = new HierarchyId("/2/1/") });
context.Employees.Add(new Employee { Name = "Emp6", Node = new HierarchyId("/1/2/") });
}
}
public class MyContext : DbContext
{
public DbSet<Employee> Employees { get; set; }
}
Generated database:
CREATE TABLE [dbo].[Employees](
[EmployeeId] [int] IDENTITY(1,1) NOT NULL,
[Name] [nvarchar](100) NOT NULL,
[Node] [hierarchyid] NOT NULL,
[ManagerNode] AS ([Node].[GetAncestor]((1))) PERSISTED,
CONSTRAINT [PK_dbo.Employees] PRIMARY KEY CLUSTERED
(
[EmployeeId] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY],
CONSTRAINT [UK_EmployeeNode] UNIQUE NONCLUSTERED
(
[Node] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
ALTER TABLE [dbo].[Employees] WITH CHECK ADD CONSTRAINT [EmployeeManagerNodeNodeFK] FOREIGN KEY([ManagerNode])
REFERENCES [dbo].[Employees] ([Node])
Example to get the child nodes of Emp1 employee:
using (var c = new MyContext())
{
var firstItem = c.Employees.Single(o => o.Node == new HierarchyId("/1/"));
foreach (var table1 in firstItem.GetSubordinates(c))
{
Console.WriteLine(table1.EmployeeId + " " + table1.Name);
}
}
result:
4 Emp3
7 Emp6
这篇关于实体框架层次结构的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
