Entity Framework HierarchyId Workarounds

Question

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!

Answer 1

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

Answer 2

Using varbinary(892) instead of hierarchyid. EF recognizes varbinary returning byte array. You can convert byte array to SqlHierarchyid type and use hyrarchy pod functions. With this workaround you can use hierarchyid functions even in other databases. See http://www.casavillar.com.br/blog with more details and links to nugget and github where you will find samples including MySql