CODEWITHSUNDEEP
c#asp.netlinqlinq-to-sql
Steven
Steven

Reputation: 18859

Looking for a better way to get a collection of items by ID

I have a pretty standard setup, with a many-to-many relationship between Departments and Employees.

Departments
---------------
DepartmentID
Name

Employees
---------------
EmployeeID
Name

DepartmentEmployees
-------------------
DepartmentID
EmployeeID

Given a Department, I'd like to return a List of Employees for that Department. Here's what I have:

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;
    }
}

This works fine, but I don't like making two database calls. Any other way to do this with LINQ?

Upvotes: 0

Views: 73

Answers (1)

Justin Niessner
Justin Niessner

Reputation: 245429

Why not use a 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();

Upvotes: 2

Related Questions