kick it on DotNetKicks.com

Unfortunately, there is still no way to use the new hierarchyID-dataTpe in LinqToSQL or Entity Framework.  (Microsoft 12.5.2009)

Because the data type is very practically, fast and over and over again hierarchy appears within comments, menu structures etc., I want to use them nevertheless.

For this I provide first of all a table which has a ContentID (Integer value wich refers to my content),  the HierarchyID of course and assoziationTypeID, because this table is for different Content (Comments has ID=1, Menu ID=2 etc.).

CREATE TABLE [dbo].[t_C_ContentHierarchy](
    [d_C_ContentHierarchyID] [int] IDENTITY(1,1) NOT NULL,
    [d_ContentID] [int] NOT NULL,
    [d_hierarchyID] [hierarchyid] NOT NULL,
    [d_I_assoziationTypeID] [int] NOT NULL,
 CONSTRAINT [PK_t_C_UserContentHirarchy] PRIMARY KEY CLUSTERED
(
    [d_C_ContentHierarchyID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]


Because I cannot access this table with LinqToSQL, I provide (like with the full text search) a table value function which I can use again in Linq:

CREATE FUNCTION [dbo].[contentHierarchy_getHierachyList_By_ContentID]
(
    @ContentID int
)
RETURNS TABLE
AS
RETURN
(
    SELECT d_ContentID
            ,d_HierarchyID.ToString() as d_HierarchyID_ToString
            ,d_HierarchyID.GetLevel() as d_HierarchyLevel
    FROM   t_C_ContentHierarchy
    Where d_HierarchyID.IsDescendantOf((Select Top 1 d_HierarchyID
                                            from t_C_ContentHierarchy
                                            where d_ContentID= @ContentID)
                                        )=1
)

And if I have added the function to my DataContext, I can access in LinqtoSQL in the following manner:

1    Dim searchID As Integer = 123

2    Dim db As New dbDataContext

3    Dim p = From p1 In db.t_ContentEntry _

4         Join p2 In db.contentHierarchy_getHierachyList_By_ContentID(searchID) _

5                         On p1.d_UserContentID Equals p2.d_ContentID _

6                Select New With { _

7                             p1.d_Headline, _

8                             p2.d_HierarchyID_ToString, _

9                             p2.d_HierarchyLevel _

10                             }

Now we need a few more Stored Procedures in the SQL server around the necessary changes in the Hierarchy table to carry out:
   1. Add new entries
       * If the Parent entry does not exist, this is provided on Root
        (See below: contentHierarchy_AddHierarchyEntry)

    2. Delete entries with the option to reparent the Childs to another node
         (See below: contentHierarchy_DeleteNodeAndDescendants)

   3. Move a complete Sub hierarchy to other hierarchy.
        (See below: contentHierarchy_ReparentDescendantNodes)

Absolutely this is still able of enlargement, however, for the moment it fulfils my needs.
If somebody has suggestions or remarks ... Please commtent this post!

-- =============================================
-- Author:        Chris Cluss
-- Create date: 5/30/2009
-- =============================================

CREATE PROC [dbo].[contentHierarchy_AddHierarchyEntry]( @Parent_ContentID int, @Child_ContentID int, @assoziationTypeID int, @reparentDescendantsChildIfExists bit) AS BEGIN SET NOCOUNT ON; DECLARE @hParent hierarchyid DECLARE @lc hierarchyid DECLARE @ReturnValue int --Überprüfen ob der Parent-Eintrag schon exstiert --Ansonsten erstellen if (SELECT COUNT(*) FROM t_C_Contenthierarchy WHERE d_ContentID = @Parent_ContentID AND d_I_assoziationTypeID=@assoziationTypeID )=0 BEGIN SET @hParent=hierarchyid::GetRoot() SELECT @lc = max(d_hierarchyID) FROM t_C_Contenthierarchy WHERE d_hierarchyID.GetAncestor(1) =@hParent ; INSERT t_C_Contenthierarchy (d_ContentID, d_hierarchyID, d_I_assoziationTypeID) VALUES(@Parent_ContentID, @hParent.GetDescendant(@lc, NULL), @assoziationTypeID) ; END SELECT @hParent = d_hierarchyID FROM t_C_Contenthierarchy WHERE d_ContentID = @Parent_ContentID AND d_I_assoziationTypeID=@assoziationTypeID ; --Überprüfen ob der Parent-Child Eintrag schon exstiert IF(Select COUNT(*) FROM t_C_ContentHierarchy WHERE d_hierarchyID.GetAncestor(1) = @hParent AND d_ContentID=@Child_ContentID AND d_I_assoziationTypeID=@assoziationTypeID)=0 BEGIN -- Wenn der ChildEintrag schon existiert, -- wird der ganze Baum umgehängt wenn @reparentDescendantsChildIfExists=1 if (Select COUNT(*) FROM t_C_Contenthierarchy WHERE d_ContentID=@Child_ContentID AND d_I_assoziationTypeID=@assoziationTypeID)>0 AND @reparentDescendantsChildIfExists=1 BEGIN EXEC contentHierarchy_ReparentDescendantNodes @ContentID = @Child_ContentID, @NewParent_ContentID = @Parent_ContentID, @assoziationTypeID = @assoziationTypeID , @reparentChildNodesOnly=0 RETURN 1 END ELSE BEGIN -- Der Childeintrag wird erstellt aber da doppelte Einträge für -- d_ContentID entstehen können müssen wir dies deshalb ausschließen: IF(Select COUNT(*) FROM t_C_Contenthierarchy WHERE d_ContentID=@Child_ContentID AND d_I_assoziationTypeID=@assoziationTypeID)=0 BEGIN SELECT @lc = max(d_hierarchyID) FROM t_C_Contenthierarchy WHERE d_hierarchyID.GetAncestor(1) = @hParent ; INSERT t_C_Contenthierarchy (d_ContentID, d_hierarchyID, d_I_assoziationTypeID) VALUES(@Child_ContentID, @hParent.GetDescendant(@lc, NULL), @assoziationTypeID) RETURN 1 END ELSE BEGIN RETURN 0 END END END END
 

-- =============================================
-- Author:        Chris Cluss
-- Create date: 5/30/2009
-- =============================================
CREATE PROCEDURE [dbo].[contentHierarchy_DeleteNodeAndDescendants](
    @ContentID int,
    @assoziationTypeID int
)
AS
BEGIN       
    Declare @hCurrent hierarchyid

    SELECT @hCurrent=d_hierarchyID
        FROM t_C_ContentHierarchy
       WHERE d_ContentID = @ContentID
         AND d_I_assoziationTypeID=@assoziationTypeID
   
      Delete From t_C_ContentHierarchy
             Where d_hierarchyID.IsDescendantOf(@hCurrent)=1                       
END


Und zum Schluß:

-- =============================================
-- Author:        Chris Cluss
-- Create date: 5/30/2009
-- =============================================
ALTER PROCEDURE [dbo].[contentHierarchy_ReparentDescendantNodes](
    @ContentID int,
    @NewParent_ContentID int,
    @assoziationTypeID int,
    @reparentChildNodesOnly bit=0
)
AS
BEGIN
    SET NOCOUNT ON;
   
    DECLARE @ReturnValue int;
   
    Declare @hCurrent hierarchyid
    Declare @hNewParent hierarchyid
   
    -- Erstmal die alte und die neue Parent-HierarchyID ermittlen  
    SELECT @hCurrent=d_hierarchyID
        FROM t_C_ContentHierarchy
       WHERE d_ContentID = @ContentID
         AND d_I_assoziationTypeID=@assoziationTypeID
         
    if @reparentChildNodesOnly=1
    BEGIN
       
        -- Declare the variables to store the values returned by FETCH.
        DECLARE @ChildContentID int

        DECLARE contentHierarchyCursor CURSOR FOR
                SELECT  d_ContentID       
                    FROM t_C_ContentHierarchy
                    WHERE d_hierarchyID.IsDescendantOf(@hCurrent) =1
                        AND d_hierarchyID.GetLevel()=@hCurrent.GetLevel()+1

                OPEN contentHierarchyCursor


                FETCH NEXT FROM contentHierarchyCursor INTO @ChildContentID

                WHILE @@FETCH_STATUS = 0
                BEGIN
                                       
                    EXEC    contentHierarchy_ReparentDescendantNodes
                            @ContentID = @ChildContentID,
                            @NewParent_ContentID = @NewParent_ContentID,
                            @assoziationTypeID = @assoziationTypeID,
                            @reparentChildNodesOnly = 0

                                     
                    FETCH NEXT FROM contentHierarchyCursor INTO @ChildContentID
                END

                CLOSE contentHierarchyCursor
                DEALLOCATE contentHierarchyCursor
                       
        RETURN 0
    END

    --- Der neue Parent soll Child werden???
    --- Das geht nicht!
    IF  @hNewParent.IsDescendantOf(@hCurrent)=1
    BEGIN
        Return 0
    END
     
    SELECT @hNewParent=d_hierarchyID
        FROM t_C_ContentHierarchy
       WHERE d_ContentID = @NewParent_ContentID
         AND d_I_assoziationTypeID=@assoziationTypeID

    if @hCurrent.GetAncestor(1) = @hNewParent
        OR @hCurrent=@hNewParent
    BEGIN
        PRINT ('NEWPARENT IS ALREADY PARENT')
        RETURN -1
    END

    --neue Position suchen
    DECLARE @lc hierarchyID
    SELECT @lc = max(d_hierarchyID)
        FROM t_C_ContentHierarchy
       WHERE d_hierarchyID.GetAncestor(1) = @hNewParent ;


    update t_C_ContentHierarchy       
        SET d_hierarchyID=
            hierarchyid::Parse(
                            @hNewParent.GetDescendant(@lc, NULL).ToString()
                                    + RIGHT(d_hierarchyID.ToString()
                                            ,CASE WHEN LEN(d_hierarchyID.ToString())-LEN(@hCurrent.ToString())>0
                                                  THEN LEN(d_hierarchyID.ToString())-LEN(@hCurrent.ToString())
                                                  ELSE 0
                                              END       
                                            )
                                ).ToString()
        Where d_hierarchyID.IsDescendantOf(@hCurrent)=1   
       
        Select @ReturnValue= COUNT(*)
            From t_C_ContentHierarchy
            where d_hierarchyID.IsDescendantOf(@hNewParent)=1
       
        RETURN @ReturnValue   
END

 
kick it on DotNetKicks.com