Read Records in Entity Framework Core

Read Records in Entity Framework Core

Reading data from Database in Entity Framework Core is quite easy. The below code will fetch the employee with name as Matt.

using (var context = new CompanyContext())
{
    var emp = await context.Employee.Where(e => e.Name == "Matt").FirstOrDefaultAsync();
}

If you want to read all the records of a particular table then use the ToListAsync() method. The below code reads all the Employee table records.

using (var context = new CompanyContext())
{
    var emp = await context.Employee.ToListAsync();
}

Reading Related Records

There are 3 common ORM Patterns used by EF Core to Read Related Records. These are:

  • Eager Loading: In Eager Loading the related data is also retrieved at the same time when an Entity is read.
  • Explicit Loading: In Explicit Loading the related data is not loaded at the same time when an Entity is read. It is explicitly loaded from the database at a later time.
  • Lazy Loading: When the entity is first read, related data isn’t retrieved. However, the first time you attempt to access a navigation property, the data required for that navigation property is automatically retrieved.
Here I will be taking the same Company Database which contains 2 tables – Employee & Department. There is Many-to-One Relationship between these 2 tables i.e. a department a can have one or more employees. So make sure you covered the previous tutorial on EF Core – Insert Records beforehand.

Eager Loading in EF Core

On running a normal reading of a record in EF Core the Related Records are not Loaded. Consider the below code where I am reading a particular Employee with name as “Matt”.

Employee emp = await context.Employee.Where(e => e.Name == "Matt")
                                .FirstOrDefaultAsync();

Apply the breakpoint on the above code and check the value of Navigation Property called Department. You will find it’s value as “null”. See the below image where I have shown this thing.

related record null ef core

In Eager Loading, when an Entity is read, it’s related data is also loaded from the database at the same time. Here the Include() method is used to read the related entity.

In Entity Framework Core the Related data is provided by the navigation property. The Employee entity contains a Navigation Property called Department for this purpose:

public Department Department { get; set; }

Similarly, the Department entity contains a Collection Navigation Property called Employee for this purpose.

public ICollection Employee { get; set; }

The Employee entity has a related Department entity so the Include() method can be used to perform the Eager Loading of Department record like shown below:

using (var context = new CompanyContext())
{
    Employee emp = await context.Employee.Where(e => e.Name == "Matt")
                                   .Include(s => s.Department)
                                   .FirstOrDefaultAsync();
}

Check the above code by putting a breakpoint over it in Visual Studio and check the value of emp variable. You will find the value of Department property filled with value at the same time. I have shown this on the below image:

eager loading of related entity

The Include() method will execute a single SQL Join Query on the database to fetch the data. This SQL query is given below:

SELECT [e].[Id], [e].[Designation], [e].[Name], [e.Department].[Id], [e.Department].[Name]
FROM [Employee] AS [e]
LEFT JOIN [Department] AS [e.Department] ON [e].[DepartmentId] = [e.Department].[Id]
WHERE [e].[Name] = N'Matt'

Multiple “Include()” methods

You can also use multiple Include() methods to load multiple related entities.

For example, if the Employee entity also has another related entity Project. Then the following code loads the Department & Project entities of the Employee.

using (var context = new CompanyContext())
{
    var emp = await context.Employee.Where(e => e.Name == "Matt")
                              .Include(s=>s.Department)
                              .Include(s=>s.Project)
                              .FirstOrDefaultAsync();
}
I have also written a similar article on ADO.NET see Read Records using ADO.NET in ASP.NET Core Application

“ThenInclude()” Method

Entity Framework core has another method named ThenInclude() that is used to load multiple levels of related data.

For example suppose there is also a Navigation Property named Report of the Department entity.

Then see the below code:

using (var context = new CompanyContext())
{
    var emp = await context.Employee.Where(e => e.Name == "Matt")
                              .Include(s => s.Department)
                              .ThenInclude(r => r.Report)
                              .FirstOrDefaultAsync();
}

Here .Include(s => s.Department) will load the related entity called Department of the Employee entity.

Next .ThenInclude(r => r.Report) will load the related entity called Report of the Department entity.

Explicit Loading in EF Core

In Explicit Loading the related data is explicitly loaded from the database at a later time. You write code that retrieves the related data if it’s needed, as a result multiple queries are sent to the database. Therefore Explicit Loading can be a heavy task in certain situations.

Here the Load() or it’s asynchronous method called LoadAsync() is used to load related entity explicitly.

Consider the below code:

using (var context = new CompanyContext())
{
    var emp = await context.Employee.Where(e => e.Name == "Matt")
                              .FirstOrDefaultAsync();
    await context.Entry(emp).Reference(s => s.Department).LoadAsync();
}

The code – await context.Entry(emp).Reference(s => s.Department).LoadAsync() loads the related entity called Department of the Employee entity. The Reference property gets the reference of related data and the LoadAsync() method loads it explicitly. I have shown this in the below image.

explicit loading of related entity

If you want to filter the related data before loading then use the Query() method.

The below code will only load the Department having name as ‘Admin’.

await context.Entry(emp).Reference(s => s.Department).Query().Where(s => s.Name == "Admin").LoadAsync();

Lazy Loading in EF Core

In Lazy Loading technique of Entity Framework Core the related data isn’t retrieved when the entity is first read. However, when the first time you access a navigation property, the data required for that navigation property is automatically retrieved.

In order to use Lazy Loading you must:

  • 1. Install the Microsoft.EntityFrameworkCore.Proxies package and enabling it with a call to UseLazyLoadingProxies method in the OnConfiguring method of Database Context file.
  • protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
    {
        if (!optionsBuilder.IsConfigured)
        {
            optionsBuilder.UseLazyLoadingProxies().UseSqlServer(@"Server=vaio;Database=Company;Trusted_Connection=True;");
        }
    }
    
    
  • 2. Make all the Navigation Properties as virtual.
  • public class Employee
    {
        public int Id { get; set; }
        public int DepartmentId { get; set; }
        public string Name { get; set; }
        public string Designation { get; set; }
    
        public virtual Department Department { get; set; }
    }
    
    public class Department
    {
        public int Id { get; set; }
        public string Name { get; set; }
    
        public virtual ICollection<Employee> Employee { get; set; }
    }
    
Lazy Loading example
using (var context = new CompanyContext())
{
    Employee emp = await context.Employee.Where(e => e.Name == "Matt")
                                   .FirstOrDefaultAsync();
    string deptName = emp.Department.Name;
}

In the above code the Department data which is a related data of Employee entity is lazy loaded. On putting a breakpoint over deptName variable you can see it’s value. Check the below given image.

lazy loading entity framework core

Download the source codes:

DOWNLOAD

Next to read: Update Records in Entity Framework Core

Share this article -

yogihosting

ABOUT THE AUTHOR

This article has been written by the Technical Staff of YogiHosting. Check out other articles on "ASP.NET Core, jQuery, EF Core, SEO, jQuery, HTML" and more.