Asp.Net EF Core Joining multiple tables

In the previous post we creates a custom model class EmployeeDetail, in this post we shall see on how to use Entity Framework Core to JOIN the Employee, Department and Country tables and populate data into the custom Model class EmployeeDetail.

We have 3 tables, the Employee table is the main table which has related data in the Department and Country tables, DepartmentId and CountryId are the Foreign Keys in the employee table.

We will create an Action method in Asp.Net Core which will join the Employee table with the Department and Country tables and populate the EmployeeDetail Model class. The Controller and Action method will be as follows.

    [Route("api/[controller]")]

    [ApiController]

    public class EmployeeController : ControllerBase

    {

        // api/Department/GetDepartments

        [HttpGet("GetEmployeeDetails")]

        public ActionResult<List<EmployeeDetail>> Get()

        {

            var _context = new UserRegistrationContext();

            var employeeList = (from e in _context.Employee

                   join d in _context.Department on e.DepartmentId equals d.DepartmentId

                   join c in _context.Country on e.CountryId equals c.CountryId

                   select new EmployeeDetail()

                   {

                        EmployeeId = e.EmployeeId,

                        Department = d.Name,

                        Name = e.Name,

                        Age = e.Age,

                        Country = c.Name

                   }).ToList();

            return employeeList;

        }

    }

Build the project, in postman let us invoke the following endpoint. Notice that we are getting data from multiple tables populated using the custom Model class. 

http://localhost:51571/api/Employee/GetEmployeeDetails