EF Core Inner Join

Summary: in this tutorial, you will learn how to use the inner join to query data from two or more tables in EF Core.

Introduction to the EF Core Inner Join

A department can have one or more employees while an employee belongs to one department. The relationship between the department and the employee is a one-to-many relationship.

The Department entity maps to the Departments table while the Employee entity maps to the Employees table.

Here’s the database diagram of the Departments and Employees tables:

The Department and Employee entities are as follows:

public class Department
{
    public int Id { get; set; }
    public required string Name { get; set; }
}
namespace HR;


public class Employee
{
    public int Id { get; set; }
    public required string FirstName {  get; set; } 
    public required string LastName {  get; set; }
    public required decimal Salary { get; set; }
    public required DateTime JoinedDate { get; set; }
    
    // Foreign key property to the Department
    public int DepartmentId  {   get; set; }

    // Reference navigation to Department
    public Department Department { get; set; } = null!;

    // Reference navigation to EmployeeProfile
    public EmployeeProfile? Profile  { get; set; }

    // collection navigation to Employee
    public List<Skill> Skills { get; set; } = new();
}
Code language: C# (cs)

When querying the employee data, you may want to get the departments that belong to the employees. To do that, you use the Include() method of the Employees DbSet.

For example, the following program uses the Include() method to get the employees with their departments and sort the employees by their first names:

using HR;
using static System.Console;
using Microsoft.EntityFrameworkCore;

using var context = new HRContext();

// Get employees & departments
var employees = context.Employees.Include(e => e.Department)
                                 .OrderBy(e => e.FirstName)   
                                 .ToList();

foreach (var e in employees)
{
    WriteLine($"{e.FirstName} {e.LastName} - {e.Department.Name}");
}Code language: C# (cs)

Output:

Abigail Adams - Marketing
Addison Hill - Engineering
Aiden Wright - Marketing
Alexander Green - Finance
Alexander Young - Finance
Amelia Scott - Operations
...Code language: C# (cs)

Behind the scenes, EF Core generates a SQL statement that uses an inner join to query data from the Employees and Departments tables:

SELECT 
  [e].[Id], 
  [e].[DepartmentId], 
  [e].[FirstName], 
  [e].[JoinedDate], 
  [e].[LastName], 
  [e].[Salary],
  [d].[Id], 
  [d].[Name]
FROM 
  [Employees] AS [e] 
  INNER JOIN [Departments] AS [d] ON [e].[DepartmentId] = [d].[Id] 
ORDER BY 
  [e].[FirstName]Code language: C# (cs)

The SQL statement joins the Employees with the Departments table by matching the values in the DepartmentId column of the Employees table with the values in the Id column of the Department table.

EF Core Inner Join in a many-to-many relationship

An employee may have many skills, and a skill can be possessed by many employees. The relationship between the employee and the skill is a many-to-many relationship.

The following shows the Skill entity class:

public class Skill
{
    public int Id  { get; set; }
    public required string Title  {   get;set;   }

    // collection navigation to Employee
    public List<Employee> Employees { get; set; } = new();
}Code language: C# (cs)

Here’s the database diagram that models the many-to-many relationship between Employee and Skill entities:

To get employees with their skills, you can use the Include() method of the Employees DbSet as follows:

using HR;
using static System.Console;
using Microsoft.EntityFrameworkCore;

using var context = new HRContext();

var employees = context.Employees.Include(e => e.Skills)
                                 .OrderBy(e => e.FirstName)
                                 .ToList();


foreach (var e in employees)
{
    
    WriteLine($"{e.FirstName} {e.LastName}");

    foreach (var skill in e.Skills)
    {
        WriteLine($"- {skill.Title}");
    }
}Code language: C# (cs)

Output:

Abigail Adams
- Marketing Strategy
- Market Segmentation
Addison Hill
- Product Design
- Quality Assurance
Aiden Wright
- Market Trend Analysis
- Brand Development
...Code language: C# (cs)

In this example, EF Core first joins the EmployeeSkill table with the Skills table using an inner join in a subquery. And then it joins the Employees table with the result set returned by the subquery using a left join. It means that the query will return all employees whether they have skills or not.

SELECT 
  [e].[Id], 
  [e].[DepartmentId], 
  [e].[FirstName], 
  [e].[JoinedDate], 
  [e].[LastName], 
  [e].[Salary],
  [t].[EmployeesId], 
  [t].[SkillsId], 
  [t].[Id], 
  [t].[Title] 
FROM 
  [Employees] AS [e] 
  LEFT JOIN (
    SELECT 
      [e0].[EmployeesId], 
      [e0].[SkillsId], 
      [s].[Id], 
      [s].[Title] 
    FROM 
      [EmployeeSkill] AS [e0] 
      INNER JOIN [Skills] AS [s] ON [e0].[SkillsId] = [s].[Id]
  ) AS [t] ON [e].[Id] = [t].[EmployeesId] 
ORDER BY 
  [e].[FirstName], 
  [e].[Id], 
  [t].[EmployeesId], 
  [t].[SkillsId]Code language: C# (cs)

Using multiple joins

To get the employees and their departments and skills, you can use multiple Include() methods in the same query:

using HR;
using static System.Console;
using Microsoft.EntityFrameworkCore;

using var context = new HRContext();

var employees = context.Employees.Include(e => e.Department)
                                 .Include(e => e.Skills)
                                 .OrderBy(e => e.FirstName)
                                 .ToList();


foreach (var e in employees)
{
    
    WriteLine($"{e.FirstName} {e.LastName} - {e.Department.Name}");

    foreach (var skill in e.Skills)
    {
        WriteLine($"- {skill.Title}");
    }
}Code language: C# (cs)

Output:

Abigail Adams - Marketing
- Marketing Strategy
- Market Segmentation
Addison Hill - Engineering
- Product Design
- Quality Assurance
Aiden Wright - Marketing
- Market Trend Analysis
- Brand DevelopmentCode language: C# (cs)

EF Core generates an SQL statement that has multiple join clauses. First, it joins the Employees table with the Departments table using an inner join. And then it joins with a subquery that includes EmployeeSkill and Skills tables using a left join:

SELECT 
  [e].[Id], 
  [e].[DepartmentId], 
  [e].[FirstName], 
  [e].[JoinedDate], 
  [e].[LastName], 
  [e].[Salary],
  [d].[Id], 
  [d].[Name], 
  [t].[EmployeesId], 
  [t].[SkillsId], 
  [t].[Id], 
  [t].[Title] 
FROM 
  [Employees] AS [e] 
  INNER JOIN [Departments] AS [d] ON [e].[DepartmentId] = [d].[Id] 
  LEFT JOIN (
    SELECT 
      [e0].[EmployeesId], 
      [e0].[SkillsId], 
      [s].[Id], 
      [s].[Title] 
    FROM 
      [EmployeeSkill] AS [e0] 
      INNER JOIN [Skills] AS [s] ON [e0].[SkillsId] = [s].[Id]
  ) AS [t] ON [e].[Id] = [t].[EmployeesId] 
ORDER BY 
  [e].[FirstName], 
  [e].[Id], 
  [d].[Id], 
  [t].[EmployeesId], 
  [t].[SkillsId]Code language: C# (cs)

Summary

• Use DbSet Include() method to form an inner join (or left join) in EF Core.