正在寻找一种更好的方法来通过ID获取项目集合

Question

我有一个非常标准的设置，部门和员工之间有一个多对多的关系。

Departments
---------------
DepartmentID
Name
Employees
---------------
EmployeeID
Name
DepartmentEmployees
-------------------
DepartmentID
EmployeeID

给定一个部门，我想返回该部门的雇员列表。我有:

public partial class Department
{
    public List<Employee> GetEmployees()
    {
        int[] employeeIds = MyDBDataContext.DepartmentEmployees.
                            Where(de => de.DepartmentID == this.DepartmentID).
                            Select(de => de.EmployeeID.Value).ToArray();
        List<Employee> employees = (from x in MyDBDataContext.Employees
                                    where employeeIds.Contains(x.EmployeeID)
                                    select x).ToList();
        return employees;
    }
}

这工作得很好，但是我不喜欢调用两个数据库。还有其他方法可以用LINQ做到这一点吗?

Answer 1

为什么不使用Join呢?

var employees = (from e in MyDBDataContext.Employees
                 join de in MyDBDataContext.DepartmentEmployees
                     on e.EmployeeID equals de.EmployeeID
                 where de.DepartmentID == this.DepartmentID
                 select e).ToList();