Inner join query in EF6

Question

I Need some help with a query for my MVC Application.

I created this query in SQL:

SELECT Projects.ID, Projects.ProjectName, Employees.ID
FROM Projects INNER JOIN (Employees INNER JOIN ProjectEmployees ON Employees.ID = ProjectEmployees.EmployeeId) ON Projects.ID = ProjectEmployees.ProjectId
WHERE (((Employees.ID)=2));

It should look like that:

How can I rebuild that using perahps LinQ or other suitable Format for my MVC-Application.

Thanks, Carsten

Answer 1

Addition at the end after comment

It seems you've designed a straightforward many-to-many relation between Employees and Projects: Every Employee works on zero or more Projects, and every Project has is done by zero or more Employees.

If you've followed the entity framework coding conventions, you will have something similar to:

class Employee
{
    public int Id {get; set;}
    public string Name {get; set;}
    ... // other Employee properties

    // every Employee works on zero or more Projects, many-to-many
    public virtual ICollection<Project> Projects {get; set;}
}

class Project
{
    public int Id {get; set;}
    public string Name {get; set;}
    ... // other Project properties

    // every Project is done by zero or more Employees, many-to-many
    public virtual ICollection<Employee> Employees {get; set;}
}

For completeness the DbContext:

class DbContext
{
    public DbSet<Employee> Employees {get; set;}
    public DbSet<Project> Projects {get; set;}
}

This is all that entity framework needs to know to detect your many-to-many relationship. Even though the junction table is not mentioned, entity framework is smart enough to know that this kind of relation needs a junction table and will create it for you.

But how can I do a (group-)join if I don't have access to the junction table?

Answer: Don't do the (group-)joins yourself, use the virtual ICollection.

Requirement: Give me (some properties of) the projects that Employee with Id [2] works on.

const int employeeId = 2;
var employeeWithHisProjects = dbContext.Employees
    .Where(employee => employee.Id == 2)
    .Select(employee => new
    {
        // For efficiency, select only the Employee properties that you plan to use:
        Id = employee.Id,
        Name = employee.Name,
        ...

        Projects = employees.Projects

            // Only if you don't want all Projects of Employee[2]:
            // for example: Projects with DueDate before today:
            .Where(project => project.DueDate <= today) 

            .Select(project => new
            {
                // again: only the project properties that you plan to use.
                Id = project.Id,
                Name = project.Name
                ...
             })
             .ToList(),
    });

In words: from the table of Employees, keep only the Employees that have Id equal to 2. From the remaining Employees, take the Id, the name and ...; from his Projects, keep only those Projects with a DueDate on or before today. From the remaining Projects take the Id, the Name and ...

Entity framework knows your many-to-many relation and is smart enough to do the proper (group-)join for you.

See how natural this feels, especially if you compare this with the inner joins you proposed?

Small Difference between Inner Join and GroupJoin

Note there is a small difference though. If you leave out the Where Employee.Id == 2, the result of the inner join would be:

Employee  Project
   2         A
   2         B
   3         A
   4         C
   3         C
   2         C
   4         B

Using the ICollection, or doing a GroupJoin you get something like

• Employee[2] with his Projects A, B, C
• Employee[3] with his Projects A, C
• Employee[4] with his Projects B, C
• Employee[5] without any Projects

The GroupJoin gives a more natural result. The selected properties of Employee [2] are only transferred once, even though he has 3 Projects.

More important: you also see Employee[5], even though he hasn't got any Projects!

How to Add Employees and Projects

You can Add a Project without any Employees working on it. You can add Employees who don't work on any Project yet.

var addedProject = dbContext.Projects.Add(new Project()
{
     // Id is filled when you SaveChanges
     Name = "Improve Entity FrameWork",
     ...

     // no one is working on this project yet, therefore no need to mention Employees
     // or if you want, assign null, or empty collection
     Employees = new Employee[],
});
var addedEmployee = dbContext.Employees.Add(new Employee()
{
    // Id is filled when you SaveChanges
    Name = "John Doe",
    ...

    // John is not working on any Project yet
});

If you want to add an Employee that already works on several projects:

// Select 3 projects suitable for new employees:
var projectsForNewEmployee = dbContext.Projects
    .Where(project => project.Difficulty == Difficulty.LearningStage)
    .OrderBy(project => project.Priority)
    .Take(3);

var employeeWithSomeProjects = dbContext.Employees.Add(new Employee()
{
    Name = "Max Rabe",
    ...

    Projects = projectsForNewEmployee.ToList(),
});

Suppose we want to add Employee "Mary" to Project "Project X":

var projectX = dbContext.Projects
    .Where(project => project.Name == "Project X")
    .FirstOrDefault();
var mary = dbContext.Employees
    .Where(employee => employee.Name == "Mary")
    .FirstOrDefault(),

// Mary gets a new Project:
mary.Projects.Add(projectX);
dbContext.SaveChanges();

You could also add Mary to projectX instead:

projectX.Employees.Add(mary);
dbContext.SaveChanges();

It seems that this is more work than just add a new row to the junction table. But remember, before you can add a row to the junction table you need to query the Id of projectX and the Id of Mary, so even in that case you would need two queries and one Add

Since I detected that I can use the ICollections, I seldom use the junction table anymore. If you really want to use it, add it to your DbContext. Use fluent API to tell that this is the junction table for the many-to-many relation between Projects and Employees.

One final example: Create a new Project and let the three youngest Employees that have not many projects working on it:

var youngEmployeesWithFewProjects = dbContext.Employees
    .Where(employee => employee.Projects.Count <= 3)
    .OrderByDesending(employee => employee.Birthday)
    .Take(3);

var addedProject = dbContext.Projects.Add(new Project()
{
    Name = "Secret Project",
    ...

    Employees = youngEmployeesWithFewProjects.ToList(),
});

Answer 2

Using LINQ it will look like this

            from p in Projects
            join pe in ProjectEmployees on p.ID equals pe.ProjectId 
            join e in Employees on pe.EmployeeID equals e.ID 
            where e.ID = 2
            select new {
                ProjectId = p.ID,
                ProjectName = p.ProjectName,
                EmployeeId = e.ID
            }