CODEWITHSUNDEEP
.netsql-server-2008entity-framework
Paul Alan Taylor
Paul Alan Taylor

Reputation: 10680

How do you model a tree hierarchy in Entity Framework?

I have been tasked with building a new system that models a virtual filesystem. My client has mandated that this be built using Entity Framework. I have handled node-like data structures before, but never with Entity Framework.

What is the best approach to build a hierarchical class in Entity Framework? By hierarchical, I mean that a class can have a parent of the same type, and can have zero or more children of the same type.

I'm using SQL Server 2008 and Entity Framework 4.0. Should I use the built-in hierarchy data type, or do I go the ParentID route? Suggestions would be most welcome.

Upvotes: 3

Views: 2923

Answers (2)

Oded Tal
Oded Tal

Reputation: 106

I had the same problem. I found that the best way to work with hierarchyid datatype and still be using EF 4.0 is building a view over the the hierarchy table.

Since the view is not updateable I created a delete, insert and add stored procedures and mapped them to the entity mapping in the ORM. This is working really good.

Lets say you have this table:

CREATE TABLE [dbo].[NodeHierarchy]
(
[Node] hierarchyid NOT NULL,
[NodeId] int NOT NULL,
[Level] AS ([Node].[GetLevel]()) PERSISTED,
[Lineage] AS ([Node].[ToString]()) PERSISTED,
[RootNode] AS ([Node].[GetAncestor]([Node].[GetLevel]() - 1)) PERSISTED,
[ParentNode] AS ([Node].[GetAncestor](1)) PERSISTED
)

Now you create this view over it:

CREATE VIEW [dbo].[NodeHierarchyView]
AS
SELECT   ch.NodeId AS [NodeId],
       ch.Node.ToString() AS [Lineage],
 ch.[Level] AS [Level],
 chr.Node.ToString() AS [RootLineage],
 chr.NodeId AS [RootNodeId],
 chp.Node.ToString() As [ParentLineage],
 chp.NodeId AS [ParentNodeId]
FROM     dbo.NodeHierarchy ch
 LEFT OUTER JOIN NodeHierarchy chr ON
      ch.RootNode = chr.Node
 LEFT OUTER JOIN CompanyHierarchy chp ON
      ch.ParentNode = chp.Node

Now I can create an entity in the model over the view and use Linq-to-Entities and get good performace and neat code.

This is the add stored procedure I use:

CREATE PROCEDURE [dbo].[AddNode]
@NodeId int,
@ParentNodeId int
AS
DECLARE @NewNode hierarchyid;
DECLARE @ParnetLineage nvarchar(4000);

SELECT  @ParnetLineage = Lineage
FROM    NodeHierarchy
WHERE   NodeId = @ParentNodeId

IF @ParnetLineage IS NULL
BEGIN
    SET @ParnetLineage = N'/';
END

SET TRANSACTION ISOLATION LEVEL SERIALIZABLE
BEGIN TRANSACTION
    SET @NewNode = CAST(@ParnetLineage + CAST(@NodeId AS nvarchar (4000)) + N'/' AS hierarchyid);

    INSERT NodeHierarchy (Node, NodeId)
    VALUES (@NewNode, @NodeId)
COMMIT

SELECT @NodeId AS [NewNodeId]
RETURN 0

I created all the needed indexes and constraints over the tables. In my solution the view is displaying data from other tables and the procedures manipulate these tables as well.

Oded

Upvotes: 8

Ladislav Mrnka
Ladislav Mrnka

Reputation: 364409

You must go with ParentID because hierarchyid data type is not supported by EF (you can also check described workaround). Anyway be prepared to write stored procedures because loading hierarchies with EF is usually tough.

Upvotes: 3

Related Questions