Entity Framework - querying a many-to-many relationship table

Question

I have a many-to-many relationship defined like so:

Employees
--------------
EmployeeID (PK)

Roles
--------------
RoleID (PK)

EmployeeRoles
--------------
EmployeeID (PK, FK)
RoleID (PK, FK)

I'm trying to get a list of Employees, given a list or RoleIDs:

private MyDBEntities _entities;

public SqlEmployeesRepository(MyDBEntities entities)
{            
    _entities = entities;
}

public IQueryable<Employee> GetEmployeesForRoles(int[] roleIds)
{
    // get employees
}

But if I try and do _entities.EmployeeRoles, there is no EmployeeRoles object. My edmx looks like this:

enter image description here

So it's recognizing the relationship between the two tables, but it's not creating an entity object for EmployeeRoles.

How can I get a distinct list of Employees given a list of role id's?

BrokenGlass · Accepted Answer · 2011-09-08 20:07:18Z

32

The relationship between the tables Role and Employee is represented as a navigation property - each Employees property in the Role entity will only contain the Employees that have this particular role.

Putting it the other way round - every Employee's Roles property only contains the roles that particular employee has.

Given a set of roles roleIds to look for you can use this to get the list of employees that have a role within that set:

public IQueryable<Employee> GetEmployeesForRoles(int[] roleIds)
{
    var employees = _entities.Employees
                             .Where( x=> x.Roles.Any(r => roleIds.Contains(r.RoleID)))
   return employees;
}

Edit:

The other way to get the employees is to attack the problem from the other side of the relationship (starting with the role, not the employee). This is most likely not as efficient as the first approach, since we have to de-duplicate employees (otherwise employees with i.e. two roles would show up twice):

public IQueryable<Employee> GetEmployeesForRoles(int[] roleIds)
{
    var employees = _entities.Roles
                             .Where( r => roleIds.Contains(r.RoleID))
                             .SelectMany( x=> x.Employees)
                             .Distinct()
   return employees;
}

edited Sep 8, 2011 at 20:07

answered Sep 8, 2011 at 19:34

BrokenGlass

161k28 gold badges297 silver badges339 bronze badges

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

2 Comments

Slauma Over a year ago

Lol, I was just going to post this as an alternative to your first solution (with Distinct etc.), but now it's no alternative anymore. Can you leave your first solution as another option in your answer. It was an interesting way, or was something wrong with it?

BrokenGlass Over a year ago

@Slauma: Yes it is an alternative - but this seemed more straight-forward after looking at the problem for so I scratched the first approach - let me dig it up again ;-)

Kenneth J · Accepted Answer · 2011-09-08 19:38:39Z

3

Maybe?

var results = from r in db.Roles
              where roleIds.Contains(r.Id)
              select r.Employees;

answered Sep 8, 2011 at 19:38

Kenneth J

4,89612 gold badges43 silver badges56 bronze badges

Collectives™ on Stack Overflow

Entity Framework - querying a many-to-many relationship table

2 Answers 2

2 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related