Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

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)

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?

like image 655
Steven Avatar asked Sep 08 '11 19:09

Steven


People also ask

How do you create a many-to-many relationship in EF core?

Many-to-many. Many-to-many relationships require a collection navigation property on both sides. They will be discovered by convention like other types of relationships. The way this relationship is implemented in the database is by a join table that contains foreign keys to both Post and Tag .

How do I use lazy loading in Entity Framework?

Lazy loading means delaying the loading of related data, until you specifically request for it. When using POCO entity types, lazy loading is achieved by creating instances of derived proxy types and then overriding virtual properties to add the loading hook. Lazy loading is pretty much the default.

What is IEntityTypeConfiguration?

IEntityTypeConfiguration<TEntity> InterfaceAllows configuration for an entity type to be factored into a separate class, rather than in-line in OnModelCreating(ModelBuilder).


2 Answers

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;
}
like image 64
BrokenGlass Avatar answered Oct 30 '22 09:10

BrokenGlass


Maybe?

var results = from r in db.Roles
              where roleIds.Contains(r.Id)
              select r.Employees;
like image 43
Kenneth J Avatar answered Oct 30 '22 10:10

Kenneth J