Linq to Sql - 查找祖先的分层查询
给定 EmployeeId,如何构建 Linq to Sql 查询来查找该员工的所有祖先? 每个 EmployeeId 都有一个关联的 SupervisorId(见下文)。
例如,查询 EmployeeId 6 (Frank Black) 的祖先应返回 Jane Doe、Bob Smith、Joe Bloggs 和 Head Honcho。
如果有必要,我可以缓存所有员工的列表以提高性能。
更新:
我创建了以下粗略方法来完成任务。 它遍历员工.主管关系一直到根节点。 但是,这将为每位员工发出一次数据库调用。 有人有更简洁或更高效的方法吗? 谢谢。
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;
}
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).
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;
}
如果你对这篇内容有疑问,欢迎到本站社区发帖提问 参与讨论,获取更多帮助,或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱,如果其他用户或者作者回复了您的评论,将不能在第一时间通知您!
发布评论
评论(2)
首先,欢迎您在我的 LINQ 扩展方法项目中使用分层查询。 我认为可能有助于简化您的代码。
这里的问题是,这将为层次结构中的每个节点创建一个数据库调用。 在您的示例中,您将需要 5 次往返数据库。
我会选择不同的路径并创建一个存储过程来为我执行此操作并返回整个 Employee 对象集。 由于您在返回对象之前断开对象的连接(处理上下文),因此您可以简单地从存储过程的结果集创建新对象。
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.避免加载整个 Employee 表(但遍历深度有限)的简单解决方案是......
最终,您应该使用 公用表表达式来扁平化层次结构,但 LINQ to SQL 目前不支持此功能。 您可以考虑编写自己的扩展方法(类似于 Omer 库中的扩展方法,但使用 IQueryable 而不是 IEnumerable 来支持服务器端执行)。
A simple solution that avoids loading the whole Employee table (but has a limited traversal depth) is...
Ultimately, you should use a common table expression to flatten the hierarchy, but LINQ to SQL doesn't currently support this. You could look into writing your own extension method (like the one in Omer's library but using IQueryable instead of IEnumerable to support server-side execution).