在LINQ Oracle的CONNECT BY的SQL Server版本显示层次结构 [英] SQL Server version of Oracle's CONNECT BY in LINQ to show hierachy

问题描述

我已经成功地通过以下2以前的答案的这里和这里和调整得到我需要的结果。 ？但是办怎么我在LINQ做到这一点。

下面是我所使用的是虚拟数据库做一个例子：

  NOT CREATE TABLE职工（
雇员INT IDENTITY（1,1）PRIMARY KEY，
系INT NOT NULL，
 EmployeeName VARCHAR（40） NULL，
 PeckingOrder INT NOT NULL，
 HigherDepartment INT NULL）
 
 INSERT INTO员工（系，EmployeeName，PeckingOrder，HigherDepartment）
值（1，巴特 1，NULL），（2，'荷马'，1,1），（2，'玛吉'，2，NULL），
（3，'莉莎'，1,2），（3，'玛吉'，2,2），（3，'圣诞老人助手'，3,1）
 
系雇员EmployeeName PeckingOrder HigherDepartment 
 1 1 1巴特NULL 
 2 2 1荷马1 
 3 2玛吉2 NULL 
 4 3丽莎1 2 
 5 3玛吉2 
 6 3圣诞老人助手3 1 
  

这是用于返回heirachy的SQL：

 带N（水平，PeckingOrder，部门，EmployeeName，HigherDepartment）AS 
（SELECT 1，PeckingOrder，部门，EmployeeName，HigherDepartment 
 FROM Test.dbo.Employee 
 WHERE部门= 3 
 UNION ALL 
选择n.level + 1，nplus1.PeckingOrder，nplus1.Department，nplus1.EmployeeName，nplus1.HigherDepartment 
从Test.dbo.Employee作为NPLUS1 
 JOIN N于n.HigherDepartment = nplus1.Department）
选择MAX（水平）AS级，PeckingOrder，部门，EmployeeName，HigherDepartment 
从n个
 GROUP BY PeckingOrder，部门，EmployeeName，HigherDepartment 
 ORDER BY MAX（水平）DESC，PeckingOrder ASC 
 
级PeckingOrder部EmployeeName HigherDepartment 
 3 1 1巴特NULL 
 2 1 2荷马1 
 2 2 2玛吉NULL 
 1 1 3丽莎2 
 1 2 3玛吉2 
 1 3 3圣诞老人的助手1 
  

解决方案

您可以使用的executeQuery ：

 类YourRow 
 {
公众诠释级{搞定;设置;} 
公众诠释PeckingOrder {搞定;设置;使用} 
：
} 
 
（VAR DB =新LinqDataContext（））
 {
无功名单= db.ExecuteQuery< YourRow> ;具有N（水平，PeckingOrder，部门，EmployeeName，HigherDepartment）AS 
（SELECT 1，PeckingOrder，部门，EmployeeName，HigherDepartment 
：$ b（
 @
 $ b; 
} 
  

也许更好，创建一个包含查询视图，并使用LINQ从视图读取。

I have successfully simulated an Oracle CONNECT BY statement in SQL Server 2008 by following these 2 previous answers here and here and adjusting to get the results I need. But how do I do this in LINQ?

Here is an example of what I am doing using a dummy database:

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

and this is the SQL used to return the heirachy:

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

解决方案

You could use 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
...
";
}

Or perhaps better, create a view that contains the query, and use LINQ to read from the view.

这篇关于在LINQ Oracle的CONNECT BY的SQL Server版本显示层次结构的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！