更新时间:2023-12-04 18:51:10
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;组;
}
生成的数据库:
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