Server版本显示层次结构

Server版本显示层次结构

本文介绍了在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版本显示层次结构的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持!

07-25 23:34