实体框架 - 查询多对多关系表

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:

我正在尝试获取一个Employees列表，给出一个列表或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:

但是，如果我尝试执行_entities.EmployeeRoles，则没有EmployeeRoles对象。我的edmx看起来像这样：

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

所以它识别了两个表之间的关系，但它没有为EmployeeRoles创建一个实体对象。

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

如何获得角色ID列表的员工列表？

2 个解决方案

#1

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.

表Role和Employee之间的关系表示为导航属性 - Role实体中的每个Employees属性仅包含具有此特定角色的Employees。

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

反过来说 - 每个Employee的Roles属性只包含特定员工的角色。

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:

给定一组要查找的角色ID，您可以使用此角色获取在该集合中具有角色的员工列表：

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

#2

Maybe?

也许？

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