Leider gibt es noch keinen Weg, in LinqToSQL oder Entity Framework den neuen Datentyp HierarchyID in MSSQL-Server 2008 zu nutzen. (Microsoft am 12.5.2009)
Da der Datentyp sehr praktisch, schnell und immer wieder Hierarchien bei Kommentaren, Menüstrukturen usw. auftreten, will ich ihn natürlich trotzdem nutzen.
Hierfür erstelle ich erst einmal eine Tabelle, die eine ContentID enthät, welche die Integer-ID meines Contents enthält, die HierarchyID natürlich und eine assoziationTypeID, da ich diese Tabelle für unterschiedlichen Content nutzen möchte.
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]
Da ich auf diese Tabelle ja mit LinqToSQL nicht zugreifen kann, erstelle ich mir wie bei der Volltextsuche eine Tabellenwert-Funktion , die ich wiederum in Linq verwenden kann:
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
)
Und wenn ich dann die Funktion zu meinem DataContext hinzugefügt habe, kann ich in LinqtoSQL auf folgende Weise zugreifen:
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 }
Allerdings benötigen wir noch ein paar Stored Procedures im SQL-Server um die notwendigen Änderungen an der Hierarchy-Tabelle vorzunehmen:
1. Hinzufügen von Einträgen
* Falls der Parent-Eintrag nicht existiert, wird dieser auf Root erstellt
(Siehe unten: contentHierarchy_AddHierarchyEntry)
2. Löschen von Einträgen mit der Option, die Childs an einen anderen Knoten anzuhängen
(Siehe unten: contentHierarchy_DeleteNodeAndDescendants)
3. Umhängen von kompletten Sub-Hierarchien an andere Hierarchien.
(Siehe unten: contentHierarchy_ReparentDescendantNodes)
Sicherlich ist dies noch erweiterungsfähig aber erst mal erfüllt es meine Bedürfnisse.
Wenn jemand Anregungen oder Anmerkungen hat... nur her damit!
-- =============================================
-- 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