Hafizullah Mahmudi Hafizullah Mahmudi - 1 year ago 96
C# Question

How to query many to many relation using Entity Framework

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?

Answer Source

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,
        });
Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download