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

问题描述

我已经成功地通过以下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版本显示层次结构的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！