Read Records in Entity Framework Core

Last Updated: October 10, 2022

Entity Framework Core Reads Record from the database through the DbContext object. For example we can get all records from the database by using the below code.

var emp = context.Employee;

Here “context” is the object of DbContext class and “employee” is the entity whose reacords Entity Framework Core is reading from the database.

We can also fetch a particular employee from database. For example in the below code we are fetching the employee with name as Matt.

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

This tutorial is a part of Entity Framework Core series.

Page Contents

Entity Framework Core Read Related Records

There are 3 common ORM Patterns used by Entity Framework 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 we attempt to access a navigation property, then the data required for that navigation property is automatically retrieved.

Here we 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 performing a normal reading of records in Entity Framework Core, the Related Records are not Loaded. But with Eager Loading we can use the Navigation property of an entity to load related records. Consider the below Employee entity which has a navigation property called “Deparment” which is pointing to another entity called “Department”.

public class Employee
{
    public int Id { get; set; }
    public int DepartmentId { get; set; }
    public string Name { get; set; }
    public string Designation { get; set; }

    public Department Department { get; set; }
}

The below code is used to read an 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. We will find it’s value as “null”. See the below image where we have marked this thing.

In Eager Loading we use the Include() method to load related enteries. The Related entity is loaded through 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 class Department
{
    public int Id { get; set; }
    public string Name { get; set; }
    public ICollection<Employee> 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:

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 note the value of emp variable. We will find the value of Department property is filled with value this time. We have shown this on the below image:

It should be noted that Entity Framework Core executes SQL Queries behind the scene to read, create, delete and update data. 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

We can use multiple Include() methods to load multiple levels of related entities with Entity Framework Core. For example, suppose the Employee entity also has another related entity called Project. Then the following Include code loads the Department & Project entities of the Employee.

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 method named ThenInclude() is used to load multiple levels of related data. For example suppose there is a Navigation Property named Report in the Department entity.

public class Department
{
    public int Id { get; set; }
    public string Name { get; set; }
    public ICollection<Employee> Employee { get; set; }
    public Report Report { get; set; }
}

Then see the below code:

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 Entity Framework Core Explicit Loading the related data is explicitly loaded from the database at a later time. We write codes that retrieve the related data if it is needed. So as a result multiple queries are sent to the database thereforem making explicit Loading 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:

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 to the related data and the LoadAsync() method loads it explicitly. We have shown this in the below image.

We can also filter the related data before loading them. For this use the Query() method as shown below.

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

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

Lazy Loading in EF Core

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

In order to use Lazy Loading we must do 2 things:

1. Install the Microsoft.EntityFrameworkCore.Proxies package and enable 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();
    }
}

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

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

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

Optimizing Entity Framework Core Codes

It is necessary that we Optimize our Entity Framework Core code so that the application codes remain light and at the same time execute faster. We can perform the optimization of Entity Framework codes in 3 manners which are:

No Tracking of Entities
Minimum call to Database
Limit the size of resultset

No Tracking of Entities

Entity Framework Core keeps track of all the entities that are returned from a LINQ query. This will cause unnecessary burden when we don’t require tracking particularly in read-only scenarios. The AsNoTracking method tells EF Core not to track the entity. We can use it in our code like shown below.

var emp = context.Employee.AsNoTracking();

Minimum call to Database

Whenever we access an entity through database context then Entity Framework Core calls the database to fetch the result set. We can make use of List type to store the result set and then extact the data from it, instead of making calls to the database again and again. See the below code where EF Core will be making database call 2 times.

var empall = context.Employee;
var empmatt = context.Employee.Where(e => e.Name == "Matt").FirstOrDefault();

We can reduce the calling to the database to just a single time by storing the result in a list type object and then subsiquently fetching a record from there. See the below code where we have done this thing.

var empall = context.Employee.ToList();
var empmatt = empall.Where(e => e.Name == "Matt").FirstOrDefault();

Limit the size of resultset

When we call an entity we are provided with all it’s fields. We should only pull back those fields that we need so that the unnecessary heaviness of the result set is reduced. For example in the below query we are only pulling the Name field for the entity.

var empmall = context.Employee.Select(b => b.Name);

Similary we are only needing the name and deignation of an employee which is done through the below linq code.

var empmatt = context.Employee.Where(e => e.Name == "Matt").Select(b => new {b.Name, b.Designation}).FirstOrDefault();

During reading of records, rather than fetching all at once, we should fetch them based on paga by page manner. This should be done when we implement pagination feature. LINQ Skip and Take operators are used to implement this thing. Skip tells to bipass a given number of records from the start and Take tells to fetch a given number of records from there.

See the below code which are providing us the records for the page numbers 1, 2 and 3. Page size being set to 10 records per page.

var emp_page_One = context.Employee.Skip(0).Take(10); // gets page 1 records
var emp_page_Two = context.Employee.Skip(20).Take(10); // gets page 2 records
var emp_page_Three = context.Employee.Skip(30).Take(10); // gets page 3 records

You can certainly read more about this concept of pagination in our article Create Number Paging with Custom Tag Helper in ASP.NET Core.

Entity Framework Core CRUD Operations – READ RECORDS

We will now perform Entity Framework Core CRUD OPERATIONS for Reading Records from the Database. We do this for both the Employee and Department entities.

Just a recall, we started this CRUD OPERATIONS in our article called Insert Records in Entity Framework Core, make sure to read it first.

Open the DepartmentController.cs and add an “Index” action method that reads all the department records and returns them to the view with the code – context.Department.AsNoTracking().

public class DepartmentController : Controller
{
    private CompanyContext context;
    public DepartmentController(CompanyContext cc)
    {
        context = cc;
    }

    public IActionResult Index()
    {
        return View(context.Department.AsNoTracking());
    }

    //...
}

Next, add the Index.cshtml razor view file inside the “Views/Department” folder which will shown the department records in a HTML Table.

@{
	ViewData["Title"] = "All Departments";
}
@model IEnumerable<Department>

<h1 class="bg-info text-white">All Departments</h1>
<a asp-action="Create" class="btn btn-secondary">Create</a>

<table class="table table-sm table-bordered">
	<tr>
		<th>ID</th>
		<th>Name</th>
	</tr>
	@foreach (Department dept in Model)
	{
		<tr>
			<td>@dept.Id</td>
			<td>@dept.Name</td>
		</tr>
	}
</table>

Run the app and open the url – https://localhost:7018/Department where we can see all the department records displayed nicely inside a table.

Next we will read Employee records and display them on the browser. So add a new Index action method to the “EmployeeController.cs” file as shown below.

public class EmployeeController : Controller
{
    private CompanyContext context;
    public EmployeeController(CompanyContext cc)
    {
        context = cc;
    }

    public IActionResult Index()
    {
        return View(context.Employee.Include(s => s.Department));
    }

    //...
}

We used the Eager Loading concept to read Employee and it’s related Department recods by using the code.

context.Employee.Include(s => s.Department)

The records are returned to the browser where they are displayed to the user. Next add Index.cshtml razor view file inside “Views/Employee” folder with the following code.

@{
	ViewData["Title"] = "All Employees";
}
@model IEnumerable<Employee>

<h1 class="bg-info text-white">All Employees</h1>
<a asp-action="Create" class="btn btn-secondary">Create</a>

<table class="table table-sm table-bordered">
	<tr>
		<th>ID</th>
		<th>Name</th>
		<th>Designation</th>
		<th>Department</th>
	</tr>
	@foreach (Employee emp in Model)
	{
		<tr>
			<td>@emp.Id</td>
			<td>@emp.Name</td>
			<td>@emp.Designation</td>
			<td>@emp.Department.Name</td>
		</tr>
	}
</table>

The view has a model of type IEnumerable<Employee> and we are reading the related department record’s name as – @emp.Department.Name. Now run the app and go to the url – https://localhost:7018/Employee where we will see all the Employee records as shown below.

Download the source codes:

DOWNLOAD

Conclusion

We covered Entity Framework Core Read Records concept and also learned how we can include child collection when reading related records. After that we also discussed optimization techniques that wil help us to speed our apps. In the end we create the Reading part of the CRUD OPERATIONS. We are now read to move to the next part which is Update Records in Entity Framework Core.