EF Core Joining tables with Include

In the previous pose we saw on how to join tables using the join keyword. Entity Framework core also provides an alternate way to join tables using the Include clause.  To use include for joining the tables we need to set the Primary Key, Foreign Key relation in the Database. 

In the sample we have an Employee table which has a Foreign Key DepartmentId referencing to the Department table. We will join these tables using Entity Framework Core.

Make sure that the Foreign Keys are setup in the database before we generate the Model classes using Entity Framework core if now we cannot use Include to join tables. If the relationship is set in the database the Model class will contain a property for each child table it is related to. In the below case the Employee table has Foreign Keys referencing to the Department and Country tables.

namespace DataAccess.Model

{

    public partial class Employee

    {

        public int EmployeeId { get; set; }

        public int? DepartmentId { get; set; }

        public string Name { get; set; }

        public int? Age { get; set; }

        public string City { get; set; }

        public string State { get; set; }

        public int? CountryId { get; set; }

        public string Phone { get; set; }

        public string Email { get; set; }

        public virtual Country Country { get; set; }

        public virtual Department Department { get; set; }

    }

}

If these reference objects are not present in the Model class we will have to set the relations in the database and re-run the Entity Framework core command to re-generate the Model classes. We will have to use the forec (-f) option to overwrite the previously generated Model classes.

\HelloWebAPI\DataAccess>dotnet ef dbcontext Scaffold "Server=<SQLServer>\SQLEXPRESS;Database=UserRegistration;Trusted_Connection=True;" Microsoft.EntityFrameworkCore.SqlServer -o Model -f

Once we have the Database relations and Model classes in place we can use Include to join the tables as follows.

            var _context = new UserRegistrationContext();

            var employeeList = _context.Employee

                                    .Include(x => x.Department).ToList();

This query will produce a list of nested objects, the primary list contains a list of employees and each employee object in the list will contain a Department object with the Department details it is related to.