给定一个雇员，我怎么能建立一个LINQ to SQL查询找出所有员工的祖先？每个雇员具有相关联的SupervisorId（见下文）。

实体定义：

样品表数据：

例如，对祖先的雇员6（弗兰克·布莱克）的查询应该返回李四，鲍勃·史密斯，乔·布洛格斯和头町。

，我可以缓存所有员工的名单，以提高性能。

更新：

我已经创建了下面的粗略的方法来完成任务。它穿过employee.Supervisor关系一路根节点。然而，这将发出一个数据库要求每个员工。任何人有一个更简洁的或更好的性能的方法？谢谢你。

 私人列表＆LT;员工＆GT; GetAncestors（INT雇员）
{
    名单＆LT;员工＆GT;环境管理计划=新的名单，其中，员工＆GT;（）;
    使用（L2STestDataContext DC =新L2STestDataContext（））
    {
        员工EMP = dc.Employees.FirstOrDefault（P =＆GT; p.EmployeeId ==雇员）;
        如果（EMP！= NULL）
        {
            而（emp.Supervisor！= NULL）
            {
                emps.Add（emp.Supervisor）;
                EMP = emp.Supervisor;
            }
        }
    }
    返回环境管理计划;
}
 

解决方案

首先，欢迎您使用分层查询我的 LINQ扩展方法项目。我认为可以帮助简化您的code。

这里的问题是，这将创建一个数据库调用为层次结构中的每个节点。在您的例子中，你将有5个往返到数据库。

我会去不同的路径，并创建一个存储过程来做到这一点对我来说，回到整套员工的对象。既然你断开对象返回他们（处理上下文）之前，你可以简单地创建存储过程的结果集的新对象。

Given an EmployeeId, how can I construct a Linq to Sql query to find all of the ancestors of the employee? Each EmployeeId has an associated SupervisorId (see below).

Entity Definition:

Sample Table Data:

For example, a query of the ancestors for EmployeeId 6 (Frank Black) should return Jane Doe, Bob Smith, Joe Bloggs, and Head Honcho.

If necessary, I can cache the list of all employees to improve performance.

UPDATE:

I've created the following crude method to accomplish the task. It traverses the employee.Supervisor relationship all the way to the root node. However, this will issue one database call for each employee. Anyone have a more succinct or more performant method? Thanks.

private List<Employee> GetAncestors(int EmployeeId)
{
    List<Employee> emps = new List<Employee>();
    using (L2STestDataContext dc = new L2STestDataContext())
    {
        Employee emp = dc.Employees.FirstOrDefault(p => p.EmployeeId == EmployeeId);
        if (emp != null)
        {
            while (emp.Supervisor != null)
            {
                emps.Add(emp.Supervisor);
                emp = emp.Supervisor;
            }
        }
    }
    return emps;
}

First of all, you're welcome to use the hierarchical queries in my LINQ Extension Methods project. I think may help simplify your code.

The problem here is that this will create a database call for each node in the hierarchy. In the case of your example, you will have 5 round-trips to the database.

I'd go a different path and create a stored-procedure to do that for me and return the whole set of Employee objects. Since you're disconnecting the objects before returning them (disposing of the context), you could simply create new object from the stored procedure's result-set.