Getting objects using LINQ and stored procedure

I have a stored procedure that is returning data in this format:

EmployeeID | DepartmentID
---------------------
1 | 1
2 | 1
3 | 2
4 | 4
5 | 4

I'm getting the results like so:

List<spResult> results = DataCont开发者_JAVA技巧ext.sp().ToList();

I'd like to get a list of Employees for a certain Department, based on the data returned from the stored procedure. Something like:

int departmentId = 1;

List<Employee> employees = (from e in DataContext.Employees
                            //where...
                            select e).ToList();

How do I format my where clause to get the EmployeeIDs from the result set that have the given DepartmentID?

---

How about:

List<spResult> results = DataContext.sp().ToList();

int departmentId = 1;

var departmentEmployees = from de in results
                          where de.DepartmentId == departmentId
                          select de.EmployeeID;

List<Employee> employees = (from e in DataContext.Employees
                            where departmentEmployees.Contains(e.ID)                            
                            select e).ToList();

---

You could get a subset of keys:

var empKeys = results.Where(i => i.DepartmentID = departmentID);

And then use this list in the query like:

List<Employee> employees = (from e in DataContext.Employees
                            where empKeys.Contains(e.EmployeeID)
                            select h).ToList();

HTH.

---

You should also be able to do something like this:

List<Employee> employees = DataContext.Employees.Where(e => empKeys.Contains(e.EmployeeID)).ToList()