How to query many to many relation using Entity Framework

Question

I have the following models in my application:

public class Employee
{
    public int PersonId { get; set; }
    public string  FirstName { get; set; }
    public string LastName { get; set; }
    public int Benefits { get; set; }
}

public class Department
{
    public int DeptID { get; set; }
    public string DeptName { get; set; }
}

public class DeptEmp
{
    public int PersonID { get; set; }
    public int DeptID { get; set; }
}

I want to create a query, using Entity Framework, to select all columns from employee with a condition that it retrieves only those employees that PersonId has a relation with DeptId in the DeptEmp class and DepartId from Department has a relation with DeptId in the DeptEmp.

I have written the following LINQ statement:

var selectEmployees = from e in Employee
                      join d in DeptEmp on e.PersonId equals d.PersonId
                      join dd in Depatment on d.DeptId equals dd.DeptId
                      select new
                         {
                             e.FirstName,
                             e.LastName,
                             e.Benefits
                         };

but it is not working. Am I missing anything?

Please define 'it is not working'. We can't troubleshoot the issue unless you tell us *what it currently does* and *what it should do*. — Der Kommissar, May 23 '17 at 11:20
Thank you but it is showing records more than what is inside the Employee table. — , May 23 '17 at 12:08
You haven't define any relations,ie collections in the roots, parent properties in the children. If you had, the child objects would be available through the navigation properties — Panagiotis Kanavos, May 23 '17 at 12:08
If you had a `Department` property on each employee, you could get all employees and their departments simply by loading the employees. If you had an `Employees` collection on `Department`, you could get a single department's employees simply by loading the department. There is seldom any good reason to use JOIN when using an ORM — Panagiotis Kanavos, May 23 '17 at 12:10
Possible duplicate of [How to create a many-to-many mapping in Entity Framework?](https://stackoverflow.com/questions/19342908/how-to-create-a-many-to-many-mapping-in-entity-framework) — Panagiotis Kanavos, May 23 '17 at 12:11
In the most basic use case for many-to-many relations, you should not manually manage the mapping table (`DeptEmp`). Entity Framework will handle it for you. Manually managing this is only necessary if you add more columns on the `DeptEmp` level (e.g. a date to track when the employee has started working in this specific department). But this is not the case for your example. When not managing this manually, you can simply find the associated entities in the navigational property (`myDepartment.Employees` and `myEmployee.Departments`) — Flater, May 23 '17 at 13:28

score 0 · Accepted Answer · answered May 23 '17 at 13:37

Entity framework works on a "use standards or else" basis. It is fairly easy if you use standards, if not you have to provide lots of information about your deviations.

For instance, entity framework expects a primary key of Employee as Id or EmployeeId. If you decide to use a different primary key (PersonId), you'll have to tell entity framework that this is your primary key.

The same is with your many-to-many relationship. If you use the defaults it is fairly easy, otherwise you'll need attributes or fluent API to inform about the deviations from the defaults.

Default many-to-many in your Employee / Department model would be:

See also Entity Framework Tutorial Configure many-to-many

public class Employee
{
    public int EmployeeId{ get; set; }

    public string  FirstName { get; set; }
    public string LastName { get; set; }
    public int Benefits { get; set; }

    // an employee has many departments:
    public virtual ICollection<Department> Departments { get; set; }
}

public class Department
{
    public int DeptartmentId { get; set; }
    public string DeptName { get; set; }

    // an department has many employees
    public virtual ICollection<Employee> Employees{ get; set; }
}

public MyDbContext : DbContext
{
    public DbSet<Employee> Employees {get; set;}
    public DbSet<Department> Departments {get; set;}
}

If you make a simple console application with these classes you'll see that it creates also a many-to-many table. You'll seldom need this table, but if you really need it, you could add it to the DbContext.

I want ... to select all columns from employee with the condition that it retrieves only those employees that PersonId has a relation with DeptId

I assume that this means that given a DeptId you want all properties from all employees working in this DeptId:

using (var dbContext = new MyDbContext(...))
{
    var myDepartment = dbContext.Departments
        .Where(department => department.DepartmentId == DeptId)
        .SingleOrDefault();
    // I know there is at utmost one, because it is a primary key

    if (myDepartment == null) ShowDepartmentMissing(...);

    var employeesOfDepartment = myDepartment.Employees
        .Select(employee => new
        {
            FirstName = employee.FirstName,
            LastName = employee.LastName,
            Benefits = employee.Benefits,
        });

Thank you for the support. I have a status column in DeptEmp table and want to filter based on that column. — , May 24 '17 at 03:48
Hafizulla, isn't that a bit strange? I can imagine that a Department has a status, or that an Employee has a status. But how would you describe the status of a DeptEmp? What would that mean? — Harald Coppoolse, May 24 '17 at 08:21
You are right Harald Coppoolse but it was an example. I have some bigger table that has many columns. But I need help in how to filter data based on a column that is in DeptEmp other than primary keys like status. — , May 24 '17 at 08:49

How to query many to many relation using Entity Framework

1 Answers1