LINQ中Oracle的CONNECT BY的SQL Server版本显示层次结构

Question

遵循以下两个先前的答案并进行调整以获取所需的结果，我已经成功地在SQL Server
2008中模拟了Oracle CONNECT BY语句。但是如何在LINQ中做到这一点？

这是我正在使用虚拟数据库的示例：

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

这是用于返回heirachy的SQL：

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

Answer 1

您可以使用ExecuteQuery：

class YourRow
{
    public int level {get; set;}
    public int PeckingOrder {get; set;}
    ...
}

using (var db = new LinqDataContext())
{
    var list = db.ExecuteQuery<YourRow>(
@"
WITH n(level, PeckingOrder, Department, EmployeeName, HigherDepartment) AS 
    (SELECT 1, PeckingOrder, Department, EmployeeName, HigherDepartment
...
";
}

也许更好的做法是，创建一个包含查询的视图，然后使用LINQ从该视图读取。