Eager Loading in Entity Framework Core

Eager Loading in Entity Framework Core (EF Core)

In this article, I will discuss Eager Loading in Entity Framework Core (EF Core) with Examples. Please read our previous article discussing LINQ to Entities Queries in EF Core. At the end of this article, you will learn how to load the entities eagerly. You will also learn how to Eager Loading from multiple Levels and Tables with Examples. We will work with the same example we have worked on so far.

What is Eager Loading in EF Core?

Eager loading in Entity Framework Core is a technique used to load related data of an entity as part of the initial query. It’s an efficient way to retrieve all necessary data in a single database query, avoiding the need for multiple round-trip calls to the database. This is useful when you know you need related data for each entity and want to optimize performance by minimizing the number of queries. Eager loading is achieved using the Include and ThenInclude methods within a LINQ query:

Examples to Understand Eager Loading in Entity Framework Core

Now, to understand Eager Loading in Entity Framework Core, we need multiple database tables. So, we are going to add some new database tables as well as going to update the existing database table.

Entities:

We are going to use the following Entities in our example. We will update the Student and Standard Entities while adding the rest.

Student.cs
namespace EFCoreCodeFirstDemo.Entities
{
    public class Student
    {
        public int StudentId { get; set; }
        public string? FirstName { get; set; }
        public string? LastName { get; set; }
        public int? StandardId { get; set; }
        public virtual Standard? Standard { get; set; }
        public virtual StudentAddress? StudentAddress { get; set; }
        public virtual ICollection<Course> Courses { get; set; } = new List<Course>();
    }
}
Standard.cs
namespace EFCoreCodeFirstDemo.Entities
{
    public class Standard
    {
        public int StandardId { get; set; }
        public string? StandardName { get; set; }
        public string? Description { get; set; }
        public virtual ICollection<Student> Students { get; set; } = new List<Student>();
        public virtual ICollection<Teacher> Teachers { get; set; } = new List<Teacher>();
    }
}
StudentAddress.cs
using System.ComponentModel.DataAnnotations;
namespace EFCoreCodeFirstDemo.Entities
{
    public class StudentAddress
    {
        [Key]
        public int StudentId { get; set; }
        public string? Address1 { get; set; }
        public string? Address2 { get; set; }
        public string? Mobile { get; set; }
        public string? Email { get; set; }
        public virtual Student Student { get; set; } = null!;
    }
}
Course.cs
namespace EFCoreCodeFirstDemo.Entities
{
    public class Course
    {
        public int CourseId { get; set; }
        public string? CourseName { get; set; }
        public int? TeacherId { get; set; }
        public virtual Teacher? Teacher { get; set; }
        public virtual ICollection<Student> Students { get; set; } = new List<Student>();
    }
}
Teacher.cs
namespace EFCoreCodeFirstDemo.Entities
{
    public class Teacher
    {
        public int TeacherId { get; set; }
        public string? FirstName { get; set; }
        public string? LastName { get; set; }
        public int? StandardId { get; set; }
        public virtual ICollection<Course> Courses { get; set; } = new List<Course>();
        public virtual Standard? Standard { get; set; }
    }
}
Modifying the Context Class:

Modify the EFCoreDbContext class as follows:

using Microsoft.EntityFrameworkCore;
using Microsoft.Extensions.Logging;
namespace EFCoreCodeFirstDemo.Entities
{
    public class EFCoreDbContext : DbContext
    {
        //Constructor calling the Base DbContext Class Constructor
        public EFCoreDbContext() : base()
        {
        }

        //OnConfiguring() method is used to select and configure the data source
        protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
        {
            //To Display the Generated the Database Script
            optionsBuilder.LogTo(Console.WriteLine, LogLevel.Information);

            //Configuring the Connection String
            optionsBuilder.UseSqlServer(@"Server=LAPTOP-6P5NK25R\SQLSERVER2022DEV;Database=EFCoreDB1;Trusted_Connection=True;TrustServerCertificate=True;");
        }

        //OnModelCreating() method is used to configure the model using ModelBuilder Fluent API
        protected override void OnModelCreating(ModelBuilder modelBuilder)
        {
            //Use this to configure the model using Fluent API
        }

        //Adding Domain Classes as DbSet Properties
        public virtual DbSet<Course> Courses { get; set; }
        public virtual DbSet<Standard> Standards { get; set; }
        public virtual DbSet<Student> Students { get; set; }
        public virtual DbSet<StudentAddress> StudentAddresses { get; set; }
        public virtual DbSet<Teacher> Teachers { get; set; }
    }
}

Note: As we already discussed, whenever we add or update domain classes or configurations, we need to sync the database with the model using add-migration and update-database commands using Package Manager Console or .NET Core CLI. In Visual Studio, open the NuGet Package Manager Console. To launch Package Manager Console, select Tools => NuGet Package Manager => Package Manager Console from the menu below.

Eager Loading in Entity Framework Core (EF Core) with Examples

This will open the Package Manager Console. Now, type the add-migration CreateEFCoreDB2 command, select the project where your Context class is, and press the enter button, as shown in the image below.

Eager Loading in Entity Framework Core (EF Core) with Examples

You will get the following message once the above command is executed successfully.

Eager Loading in Entity Framework Core (EF Core) with Examples

We are getting one warning that an operation was scaffolded, possibly resulting in data loss. Please review the migration for accuracy. So, he is saying that before updating the database, please review the migration because there might be some data loss. You can ignore this at this moment.

Once this command is executed successfully, you will see that another migration class file will be created with the current date and time, along with the name you provided in the migration command. You can see this class file within the Migrations folder, as shown in the below image.

Eager Loading in Entity Framework Core (EF Core) with Examples

Don’t think the database is created after creating the migration file using the add-migration command. We still need to update the database using the Update-Database command. We can use the –verbose option to view the SQL statements executed in the target database. So, open the Package Manager Console and execute the Update-Database –verbose command, as shown in the image below.

Eager Loading in Entity Framework Core (EF Core) with Examples

Once the above script is executed successfully, you can verify the database table using SSMS, as shown in the image below.

Eager Loading in Entity Framework Core (EF Core) with Examples

Eager Loading in EF Core:

So, to understand Eager Loading in EF Core, we need to have some data in the database table. So, please execute the following SQL Script to insert some dummy data into the respective database tables using SSMS.

-- Use EFCoreDB1
USE EFCoreDB1;
GO

-- Standards table data
INSERT INTO Standards VALUES('STD1', 'Outstanding');
INSERT INTO Standards VALUES('STD2', 'Good');
INSERT INTO Standards VALUES('STD3', 'Average');
INSERT INTO Standards VALUES('STD4', 'Below Average');
GO

-- Teachers table data
INSERT INTO Teachers VALUES('Anurag', 'Mohanty', 1);
INSERT INTO Teachers VALUES('Preety', 'Tiwary', 2);
INSERT INTO Teachers VALUES('Priyanka', 'Dewangan', 3);
INSERT INTO Teachers VALUES('Sambit', 'Satapathy', 3);
INSERT INTO Teachers VALUES('Hina', 'Sharma', 2);
INSERT INTO Teachers VALUES('Sushanta', 'Jena', 1);
GO

-- Courses table data
INSERT INTO Courses VALUES('.NET', 1);
INSERT INTO Courses VALUES('Java', 2);
INSERT INTO Courses VALUES('PHP', 3);
INSERT INTO Courses VALUES('Oracle', 4);
INSERT INTO Courses VALUES('Android', 5);
INSERT INTO Courses VALUES('Python', 6);
GO

-- Students table data
INSERT INTO Students VALUES('Pranaya', 'Rout', 1);
INSERT INTO Students VALUES('Prateek', 'Sahu', 2);
INSERT INTO Students VALUES('Anurag', 'Mohanty', 3);
INSERT INTO Students VALUES('Hina', 'Sharma', 4);
GO


-- StudentAddresses table data
INSERT INTO StudentAddresses VALUES(1, 'Lane1', 'Lane2', '1111111111', '1@dotnettutorials.net');
INSERT INTO StudentAddresses VALUES(2, 'Lane3', 'Lane4', '2222222222', '2@dotnettutorials.net');
INSERT INTO StudentAddresses VALUES(3, 'Lane5', 'Lane6', '3333333333', '3@dotnettutorials.net');
INSERT INTO StudentAddresses VALUES(4, 'Lane7', 'Lane8', '4444444444', '4@dotnettutorials.net');
GO

-- StudentCourse table data
INSERT INTO CourseStudent VALUES(1,1);
INSERT INTO CourseStudent VALUES(2,1);
INSERT INTO CourseStudent VALUES(3,2);
INSERT INTO CourseStudent VALUES(4,2);
INSERT INTO CourseStudent VALUES(1,3);
INSERT INTO CourseStudent VALUES(6,3);
INSERT INTO CourseStudent VALUES(5,4);
INSERT INTO CourseStudent VALUES(6,4);
GO
How many ways can we load the Related Entities in Entity Framework Core?

In Entity Framework Core, we can load the related entities in three ways. They are Eager Loading, Lazy Loading, and Explicit Loading. All these three techniques, i.e., Eager Loading, Lazy Loading, and Explicit Loading, refer to loading the related entities of the main entity. The only difference is they define when to load the related or child entities of the main entity.

First, we must understand what is related or child entities in the Entity Framework Core. For a better understanding, please look at the following Student Entity. Inside this Student Entity, we have three navigation properties, i.e., Standard and StudentAddress as Reference Navigation Property and Courses as Collection Navigation Property. These Navigation properties are nothing but pointing to some other Entities. These other entities are nothing but the related entities of the Student Entity. Related entities are entities created using Foreign Keys.

How many ways can we load the Related Entities in Entity Framework Core?

When loading the Student entity, i.e., retrieving the data from the Student database table, how can we retrieve the related entities, i.e., Standard, StudentAddress, and Courses? We can load these related entities in three ways while retrieving the Student entity. They are Eager LoadingLazy Loading, and Explicit Loading. In this article, we will discuss Eager Loading in detail, and in our next two articles, we will discuss Lazy Loading and Explicit Loading with Examples.

What is Eager Loading in Entity Framework Core?

Eager Loading in Entity Framework Core loads related entities whenever we load the main entity in a single query. This helps reduce the number of database queries or round-trips and improves performance by retrieving all necessary data in one go. This approach is efficient as it saves bandwidth and server CPU time. Eager loading is especially useful when we want to access properties of related entities without triggering additional database requests. Entity Framework Core achieves this by using JOINs to load the related entities instead of separate SQL queries.

How to Implement Eager Loading in EF Core?

Entity Framework Core supports Eager Loading of related entities, same as EF 6, using the Include() extension method. In addition to this, it also provides the ThenInclude() extension method to load multiple levels of related entities. (EF 6 does not support the ThenInclude() method)

Example to Understand Eager Loading in EF Core:

What our requirement is, when loading the Student entities, we also need to eager-load the corresponding Standard entities. The students and their Standards will be retrieved in a single query using the SQL join.

In the following example, we are fetching all the students from the Student database table and its standards using the DbSet Include() method. As shown in the example, we can specify a lambda expression as a parameter in the Include() method to specify a navigation property. To use the Include method, we need to import Microsoft.EntityFrameworkCore namespace. In the example below, I show how to specify the related entity name using string name and lambda expression using Method and Query syntax.

using EFCoreCodeFirstDemo.Entities;
using Microsoft.EntityFrameworkCore;
namespace EFCoreCodeFirstDemo
{
    internal class Program
    {
        static void Main(string[] args)
        {
            try
            {
                var context = new EFCoreDbContext();

                //Loading Related Entities using Eager Loading
                //While Loading the Student table data, it also load the Standard table data
                
                //Eager Loading using Method Syntax
                //Specifying the Related Entity Name using Lambda Expression
                var students = context.Students
                             .Include(std => std.Standard)
                             .ToList();

                //Specifying the Related Entity Name using String Name
                //var students = context.Students
                //             .Include("Standard")
                //             .ToList();

                //Eager Loading using Query Syntax
                //Specifying the Related Entity Name using Lambda Expression
                //var students = (from s in context.Students.Include(std => std.Standard)
                //                 select s).ToList();

                //Specifying the Related Entity Name using String Name
                //var students = (from s in context.Students.Include("Standard")
                //                 select s).ToList();

                //Printing all the Student and Standard details
                foreach (var student in students)
                {
                    Console.WriteLine($"Firstname: {student.FirstName}, Lastname: {student.LastName}, StandardName: {student.Standard?.StandardName}, Description: {student.Standard?.Description}");
                }
            }
            catch (Exception ex)
            {
                Console.WriteLine($"Error: {ex.Message}"); ;
            }
        }
    }
}
Output:

Example to Understand Eager Loading in EF Core

As you can see in the above output, EF Core uses SQL Join to fetch the data.

Note: The Include() method with a lambda expression is recommended. This is because, with the Include method with a string name, we will get a runtime exception if the property name is misspelled or does not exist. So, always use the Include() method with the lambda expression to detect the error during compile time.

FromSql() Method in EF Core:

Using the FromSql() Method in EF Core, we can specify the RAW T-SQL Statements executed in the database. The Include() extension method in EF Core can also be used after the FromSql() method. For a better understanding, please have a look at the following example.

using EFCoreCodeFirstDemo.Entities;
using Microsoft.EntityFrameworkCore;
namespace EFCoreCodeFirstDemo
{
    internal class Program
    {
        static void Main(string[] args)
        {
            try
            {
                var context = new EFCoreDbContext();

                //Loading Related Entities using Eager Loading
                //While Loading the Student Information, also load the related Standard data

                //Create the SQL Statement using FormattableString object
                FormattableString sql = $"Select * from Students WHERE FirstName = 'Pranaya'";

                //Pass the FormattableString Object to the FromSql Method
                var StudentWithStandard = context.Students
                        .FromSql(sql)
                        .Include(s => s.Standard) //Eager Load the Related Standard data
                        .FirstOrDefault();

                //Printing the Student and Standard details
                Console.WriteLine($"Firstname: {StudentWithStandard?.FirstName}, Lastname: {StudentWithStandard?.LastName}, " +
                    $"StandardName: {StudentWithStandard?.Standard?.StandardName}, Description: {StudentWithStandard?.Standard?.Description}");

            }
            catch (Exception ex)
            {
                Console.WriteLine($"Error: {ex.Message}"); ;
            }
        }
    }
}
Output:

FromSql() Method in EF Core

How to Load Multiple Related Entities Using Include Method in EF Core?

If you look at the Student Entity, you will see that the Student Entity Has three related entities as Standard, StudentAddress as Reference Navigational Entities, and Courses as Collection Navigation Entity, as shown in the image below.

How to Load Multiple Related Entities Using Include Method in EF Core?

Our requirement is to load the Standard, StudentAddreess, and Courses Entities while loading the Student Entity. Is it possible in Entity Framework Core? Yes, it is possible. We can eagerly load multiple related entities using the Include method multiple times in Entity Framework Core.

So, we need to use the Include() method multiple times to load multiple navigation properties of the same entity. In the below example, we eagerly load the Student, Standard, StudentAddress, and Courses entities while loading the Student Entity. 

using EFCoreCodeFirstDemo.Entities;
using Microsoft.EntityFrameworkCore;
namespace EFCoreCodeFirstDemo
{
    internal class Program
    {
        static void Main(string[] args)
        {
            try
            {
                var context = new EFCoreDbContext();

                //Loading Related Entities using Eager Loading
                //While Loading the Student table data, it is also going to load the Standard, StudentAddress and Courses tables data
                
                //Eager Loading Multiple Entities using Method Synatx
                var students = context.Students
                    .Include(x => x.Standard)
                    .Include(x => x.StudentAddress)
                    .Include(x => x.Courses)
                    .ToList();

                //Eager Loading Multiple Entities using Query Synatx
                //var students = (from s in context.Students
                //                 .Include(x => x.Standard)
                //                 .Include(x => x.StudentAddress)
                //                 .Include(x => x.Courses)
                //                 select s).ToList();

                //Printing all the Student and Standard details
                foreach (var student in students)
                {
                    Console.WriteLine($"Student Name: {student.FirstName} {student.LastName}, StandardName: {student.Standard?.StandardName}, Address: {student.StudentAddress?.Address1}");
                    foreach (var course in student.Courses)
                    {
                        Console.WriteLine($"\tCourse ID: {course.CourseId} Course Name: {course.CourseName}");
                    }
                }
            }
            catch (Exception ex)
            {
                Console.WriteLine($"Error: {ex.Message}"); ;
            }
        }
    }
}

Now, run the application, and you should get the Student and all the related entities data as expected, as shown in the below image.

How to Load Multiple Related Entities Using Include Method in EF Core?

ThenInclude Method in EF Core:

It is also possible to eagerly load multiple levels of related entities in Entity Framework Core. Entity Framework Core introduced the new ThenInclude() extension method to load multiple levels of related entities.

So, first, we need to understand what it means by multiple levels of related entities. If you go to the Student entity or Student class, you will see that it includes one related entity called Standard, i.e., it includes it as a Navigation property. Now, go to the Standard entity or Standard class. It includes one related entity called Teachers, i.e., the Standard Entity includes the Teachers entity as a collection navigation entity.

Now, what we want is, we want to load Student, Standard, and Teacher entities. This is nothing but multiple levels of related entities. For a better understanding, please have a look at the following example.

using EFCoreCodeFirstDemo.Entities;
using Microsoft.EntityFrameworkCore;
namespace EFCoreCodeFirstDemo
{
    internal class Program
    {
        static void Main(string[] args)
        {
            try
            {
                var context = new EFCoreDbContext();

                //Loading Related Entities using Eager Loading
                //While Loading the Student table data, it is also going to load the Standard, StudentAddress and Courses tables data

                //Method Synatx

                var student = context.Students.Where(s => s.FirstName == "Pranaya")
                        .Include(s => s.Standard)
                        .ThenInclude(std => std.Teachers)
                        .FirstOrDefault();

                Console.WriteLine($"Firstname: {student?.FirstName}, Lastname: {student?.LastName}, StandardName: {student?.Standard?.StandardName}, Description: {student?.Standard?.Description}");

                if(student?.Standard != null)
                {
                    //You can also access the Teacher collection here
                    foreach (var teacher in student.Standard.Teachers)
                    {
                        Console.WriteLine($"\tTeacher ID: {teacher.TeacherId}, Name: {teacher.FirstName} {teacher.LastName}");
                    }
                }
               
            }
            catch (Exception ex)
            {
                Console.WriteLine($"Error: {ex.Message}"); ;
            }
        }
    }
}
Output:

ThenInclude Method in EF Core

Projection Query in EF Core:

We can also load multiple related entities using the projection query instead of the Include() or ThenInclude() methods. The following example demonstrates the projection query to load the Student, Standard, and Teacher entities. In the below example, the Select extension method is used to include the Student, Standard, and Teacher entities in the result. This will execute the same SQL query as ThenInclude() method.

using EFCoreCodeFirstDemo.Entities;
using Microsoft.EntityFrameworkCore;
namespace EFCoreCodeFirstDemo
{
    internal class Program
    {
        static void Main(string[] args)
        {
            try
            {
                var context = new EFCoreDbContext();

                //Loading Related Entities using Eager Loading
                //While Loading the Student table data, it is also going to load the Standard, StudentAddress and Courses tables data

                var teachers = new List<Teacher>();
                var std = context.Students.Where(s => s.FirstName == "Pranaya")
                         .Select(s => new
                         {
                             Student = s,
                             Standard = s.Standard,
                             Teachers = (s.Standard != null)? s.Standard.Teachers : teachers
                         })
                         .FirstOrDefault();

                Console.WriteLine($"Firstname: {std?.Student?.FirstName}, Lastname: {std?.Student?.LastName}, StandardName: {std?.Student?.Standard?.StandardName}, Description: {std?.Student?.Standard?.Description}");

                if(std?.Student?.Standard != null)
                {
                    //You can also access the Teacher collection here
                    foreach (var teacher in std.Student.Standard.Teachers)
                    {
                        Console.WriteLine($"\tTeacher ID: {teacher.TeacherId}, Name: {teacher.FirstName} {teacher.LastName}");
                    }
                }
            }
            catch (Exception ex)
            {
                Console.WriteLine($"Error: {ex.Message}"); ;
            }
        }
    }
}
Output:

Projection Query in EF Core

Advantages of using Eager Loading in EF Core

Eager loading in Entity Framework Core provides several advantages that can improve performance and more efficient use of database resources. Here are some of the key advantages of using eager loading:

  1. Reduced Database Round-Trips: Eager loading retrieves related data in a single query, reducing round-trips to the database. It improves performance, especially for complex object graphs or large datasets.
  2. Improved Performance: Retrieving all necessary data in a single query through eager loading can result in faster execution times and lower latency than lazy loading, which fetches related data on demand.
  3. Avoiding the N+1 Query Problem: Eager loading helps solve the “N+1 Query Problem,” where lazy loading can result in the “N+1 Query Problem” when iterating over a collection of entities. Eager loading reduces this overhead by fetching all related data in a single query.
  4. Simplified Code: Eager loading simplifies code by directly accessing related properties and collections, avoiding additional queries and leading to cleaner, more maintainable code.
  5. Optimal Use of Database Resources: Eager loading enables the database to optimize query execution plans, potentially resulting in better utilization of indexes and caching mechanisms.
  6. Better Performance for Complex Queries: Performing queries involving multiple levels of related data can be made easier and more efficient using eager loading.
  7. Enhanced User Experience: Faster data retrieval times can improve user experience in applications that display data.
Disadvantages of using Eager Loading in EF Core

While Eager Loading in Entity Framework Core offers several advantages, it also comes with certain disadvantages you should be aware of. Here are some of the key disadvantages of using Eager loading:

  1. Over Fetching of Data: It is important to note that eager loading can result in larger query results and increased memory usage, ultimately leading to performance issues. 
  2. Performance Impact for Large Data Sets: Eager loading can be inefficient when working with large datasets or deeply nested object graphs. Retrieving all related data in one query can cause performance issues, especially if the data is not used immediately.
  3. Complex Queries: Eagerly loading multiple levels of related entities can lead to complex and potentially inefficient SQL queries, negatively impacting database performance and query execution time.
  4. Increased Initial Query Time: Eager loading can impact the application responsiveness due to increased initial query execution time, particularly when joining multiple tables or retrieving significant data.
  5. Inflexible Loading Strategy: Eager loading loads all related data upfront, which may not be suitable for all scenarios. It can lead to unnecessary data retrieval when only a subset of related data is needed.
  6. Increased Complexity: Although eager loading can simplify code in some scenarios, it may also bring in unnecessary data, adding complexity. Developers must optimize eager loading configurations carefully.

In the next article, I will discuss Lazy Loading in Entity Framework Core. In this article, I try to explain Eager Loading in Entity Framework Core with Examples, and I hope you enjoyed this Eager Loading in EF Core with Examples article. Please give your valuable feedback and suggestions about this article.

Leave a Reply

Your email address will not be published. Required fields are marked *