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