实体框架 - 查询多对多关系表 [英] Entity Framework - querying a many-to-many relationship table

问题描述

我有如下定义的多对多关系：

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

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

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

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

我正在尝试获取员工列表，给定列表或RoleID：

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
}

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

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

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

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

如何获得一个不同的雇员名单，给出角色ID的列表？

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

推荐答案

表格之间的关系角色和员工被表示为导航属性 - Role 实体中的每个 Employees 属性将只包含具有此特定角色的员工

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.

给定一组角色 roleIds 寻找你可以使用它来获取有该集合中的角色：

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

编辑：

另一种获得员工的方法是从关系的另一边攻击问题（从角色开始，而不是员工）。这很可能不如第一种方法那么有效，因为我们必须重新雇用员工（否则，两名角色的员工会出现两次）：

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

这篇关于实体框架 - 查询多对多关系表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！