Entity Framework focuses on working with the data using the objects of business domain entities, without going in detail of the structure of database, or how the data is stored in it. A Join is used to retrieve data from one or more tables based on the mutual column present in both tables. Allow the developer to collate data from multiple tables that are linked through a reference.
What is Entity Framework Join
The main purpose of using joins in Entity Framework is to fetch data from one or more tables. The join tables can be in same data source or could be in different data source.
Let us take an example of the hard-coded data of Student and Department entities. In real time, you can get the data from your required database.
Each student is assigned in a department, but you will notice one thing that one student is not assigned to any department, which means the records does not have a matching department ID.
using System;
using System.Collections.Generic;
namespace MyTestApp
{
public class Division
{
public int ID { get; set; }
public string DivisionName { get; set; }
// Declare a list of division with sample data
public static List GetDivision()
{
return new List
()
{
new Division{ ID = 1, DivisionName = "DivisionName 1"},
new Division{ ID = 2, DivisionName = "DivisionName 2"},
new Division{ ID = 3, DivisionName = "DivisionName 3"},
};
}
public class Department
{
public int ID { get; set; }
public string DepartmentName { get; set; }
public int DivisionId { get; set; }
// Declare a list of departments with sample data
public static List GetDepartment()
{
return new List()
{
new Department { ID = 1, DepartmentName = "Department Name 1, DivisionId =1},
new Department { ID = 2, DepartmentName = "Department Name 2, DivisionId =1},
new Department { ID = 3, DepartmentName = "Department Name 3, DivisionId =1},
new Department { ID = 4, DepartmentName = "Department Name 4, DivisionId =3}
};
}
public class Student
{
public int ID { get; set; }
public string Name { get; set; }
public int DepartmentId { get; set; }
// Declare a list of students and their departments with sample data
public static List GetAllStudents()
{
return new List()
{
new Student { ID = 1, Name = "Student Name 1", DepartmentId = 1},
new Student { ID = 2, Name = "Student Name 2", DepartmentId =2},
new Student { ID = 3, Name = "Student Name 3", DepartmentId = 3},
new Student { ID = 4, Name = "Student Name 4", DepartmentId = 0},
new Student { ID = 5, Name = "Student Name 5", DepartmentId = 4},
new Student { ID = 6, Name = "Student Name 6", DepartmentId = 3}
};
}
}
}
}
Entity Framework Left Outer Join
Entity Framework does not have a straight implementation of Left Outer Joins. The DefaultIfEmpty() function helps in order to achieve the fetching of data using left outer joins.
The first step is to perform inner join by using a group join. After that, you need to include each element of the first result set that is your left data source irrespective of whether the iterated element has any matching data in the second or right data source.
using System.Linq;
using System;
namespace MyTestApp
{
public class Program
{
public static void Main(string[] args)
{
// Fetching list in var object type
var result = from student in Student.GetAllStudents()
join dept in Department.GetDepartment()
on student.DepartmentId equals dept.ID
into StudentDepartmentGroup
from Department in StudentDepartmentGroup.DefaultIfEmpty()
select new { student, Department };
foreach (var item in result)
{
Console.WriteLine($"Student Name : {item.emp.Name}, Department : {item.Department?.DepartmentName} ");
}
Console.ReadLine();
}
}
}
Output:
The output of the above program is given as following and you can see that Student 4 does not have a matching department ID and therefore there is not name showing for this record.
Student Name : Student Name 1 : Department : Department Name 1
Student Name : Student Name 2 : Department : Department Name 2
Student Name : Student Name 3 : Department : Department Name 3
Student Name : Student Name 4 : Department :
Student Name : Student Name 5 : Department : Department Name 4
Student Name : Student Name 6: Department : Department Name 3
Entity Framework Join on Multiple Columns
To work with multiple columns, let us take an example of that there is an additional column in both entities i.e. IsActive. The query will give the records that have same state in both entities:
var result = from student in Student.GetAllStudents()
join dept in Department.GetDepartment()
on new {DepartmentId = student.DepartmentId, IsActive = student.IsActive } equals new { DepartmentId = dept.DepartmentId, isActive =dept.IsActive}
Entity Framework Join three tables
If you want to print the names of Division that is assigned to each department, you can use the following code to achieve it:
var result = (from student in Student.GetAllStudents()
join dept in Department.GetDepartment() on student.DepartmentId equals dept.ID
join division in Division.GetDivision() on dept.DivisionId equals division.ID
where dept.ID == 1
).ToList();
Join Using Navigational Property
Navigation Property provides a way to create an association between two entity types. It represents the related entities to the principle entities. The common way to represent such properties is through Foreign Key. It allows you to manage the relationship in both direction. If you use navigation property in the code, then it automatically instructs the program to perform a join between the two related tables.
class Department
{
public int DepartmentId {get; set ;}
public string DepartmentName {get; set ;}
}
class Student
{
public int Id {get; set ;}
public Department department {get; set ;}
}
var result = _dbContext.Student.Where(s => s.department.DepartmentName == ‘Department 1’);
//The SQL interpretation of the code is given below:
//Select Projects .Id, Projects .client_Id
//From
//Student
//inner join Department on Student.DepartmentID = Department.DepartmentId
//Where
//Department.DepartmentName = ‘Department 1’
