Getting objects using LINQ and stored procedure

Question

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 = DataContext.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?

Tim · Accepted Answer · 2011-05-25 20:13:40Z

2

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();

edited May 25, 2011 at 20:13

answered May 25, 2011 at 20:01

Tim

15.3k1 gold badge48 silver badges70 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

jeroenh Over a year ago

+1, but I think you need to put the departmentEmployees in an array or List<int>

Tim Over a year ago

@jeroenh - I don't think you need to do that in this case. Could be wrong though.

Brian Mains · Accepted Answer · 2011-05-25 20:00:37Z

1

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.

answered May 25, 2011 at 20:00

Brian Mains

50.8k35 gold badges155 silver badges260 bronze badges

Comments

Gabe · Accepted Answer · 2011-05-25 20:18:07Z

1

You should also be able to do something like this:

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

answered May 25, 2011 at 20:18

Gabe

50.6k29 gold badges147 silver badges182 bronze badges

Collectives™ on Stack Overflow

Getting objects using LINQ and stored procedure

3 Answers 3

2 Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

2 Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related