title: Entity Framework HierarchyId Workarounds
body: <p>EF 5.0</p>
  
  <p>I am working on a prototype to test hierarchyid and entity framework together. I have the following schema:</p>
  
  <pre><code>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
  </code></pre>
  
  <p>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. </p>
  
  <p>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.</p>
  
  <pre><code>Employee.Subordinates
  </code></pre>
  
  <p>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. </p>
  
  <p>Thanks for any help!</p>