实体框架 - 查询一个多一对多的关系表 [英] 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)

我试图让员工的名单，给定一个列表或RoleIDs：

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?

推荐答案

表角色与员工之间的关系重新presented作为导航性能 - 每个员工在角色属性实体将只包含有此特定角色的员工。

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屋！