SQL Server-Version von CONNECT BY von Oracle in LINQ zur Anzeige der Hierarchie
Ich habe eine Oracle CONNECT BY-Anweisung in SQL Server 2008 erfolgreich simuliert, indem ich diese beiden vorherigen Antworten befolgt habeHie undHie und anpassen, um die Ergebnisse zu erhalten, die ich brauche. Aber wie mache ich das in LINQ?
Hier ist ein Beispiel für die Verwendung einer Dummy-Datenbank:
CREATE TABLE Employee(
EmployeeID INT IDENTITY(1,1) PRIMARY KEY,
Department INT NOT NULL,
EmployeeName VARCHAR(40) NOT NULL,
PeckingOrder INT NOT NULL,
HigherDepartment INT NULL)
INSERT INTO Employee (Department,EmployeeName,PeckingOrder,HigherDepartment)
VALUES (1,'Bart',1,NULL),(2,'Homer',1,1),(2,'Marge',2,NULL),
(3,'Lisa',1,2),(3,'Maggie',2,2),(3,'Santas Helper',3,1)
EmployeeID Department EmployeeName PeckingOrder HigherDepartment
1 1 Bart 1 NULL
2 2 Homer 1 1
3 2 Marge 2 NULL
4 3 Lisa 1 2
5 3 Maggie 2 2
6 3 Santas Helper 3 1
und dies ist der SQL-Code, mit dem die Erbe zurückgegeben wird:
WITH n(level, PeckingOrder, Department, EmployeeName, HigherDepartment) AS
(SELECT 1, PeckingOrder, Department, EmployeeName, HigherDepartment
FROM Test.dbo.Employee
WHERE Department = 3
UNION ALL
SELECT n.level + 1, nplus1.PeckingOrder, nplus1.Department, nplus1.EmployeeName, nplus1.HigherDepartment
FROM Test.dbo.Employee as nplus1
JOIN n ON n.HigherDepartment = nplus1.Department)
SELECT MAX(level) AS level, PeckingOrder, Department, EmployeeName, HigherDepartment
FROM n
GROUP BY PeckingOrder, Department, EmployeeName, HigherDepartment
ORDER BY MAX(level) DESC, PeckingOrder ASC
level PeckingOrder Department EmployeeName HigherDepartment
3 1 1 Bart NULL
2 1 2 Homer 1
2 2 2 Marge NULL
1 1 3 Lisa 2
1 2 3 Maggie 2
1 3 3 Santas Helper 1