Welcome to today’s post.
In today’s post I will be showing how to use the eager loading ORM pattern to load entity data.
Before I progress this discussion, I will explain what Eager Loading is. In my previous post on lazy loading, I explained what a navigation property is within an entity. In that post I discussed how the Lazy Loading pattern improved the performance of an application when querying data. Later in this post I will explain how Eager Loading can also improve performance when querying data.
Today’s post applies to versions of Entity Framework Core 3.1. However, Eager Loading has been a part of the Entity Framework since version 2.1.
What is Eager Loading?
Eager loading is defined as data that is selectively loaded within the initial query within a data context. In this case, we only select the related data that we need from a base entity.
An example of an entity is a Book entity shown below:
With eager loading, we can nominate to load the following entities:
- Only the Book entity, OR
- The Book entity and the Loans reference data, OR
- The Book entity and the Review reference data, OR
- The Book entity, Loans and Review reference data.
In the next section, I will show how to use an Entity Framework Core extension method to allow us to eager load entities.
How do we apply Eager Loading?
We apply eager loading of entities by using the Include() extension method of the data context, which takes one argument, the entity we will be including in the query result.
For example, with the above tables, we can have an eagerly loaded query like this:
var bookLoanReviews = _db.Books
Include(b => b.Loans).
Include(r => r.Reviews).
ToList();
To analyze what Eager Loading does when LINQ queries are executed on a data context, we can run the following query:
public async Task<List<BookViewModel>> GetBookReviewsEager(int id)
{
List<BookViewModel> result = _db.Books
.Where(b => b.ID == id)
.Include(b => b.Reviews)
.ToList();
return result;
}
We are including the Reviews entity with the Book entity. This includes the Review detail records that are matching the filtered Book record.
Before we can retrieve SQL output, we will need to enable logging of SQL statements from EF Core in our application, set the log level to Warning. Below is how we do this in the application settings:
"Logging": {
"IncludeScopes": false,
"Debug": {
"LogLevel": {
"Default": "Warning"
}
},
"Console": {
"LogLevel": {
"Default": "Warning"
}
}
}
In the startup ConfigureServices(), setting upconsole logging is done using the AddLogging() extension method from the namespace Microsoft.Extensions.Logging:
services.AddLogging(config =>
{
config.AddConsole(opts =>
{
opts.IncludeScopes = true;
});
config.AddDebug();
});
There are two resulting SQL queries;
A SQL left join between Book and Review entities, as shown below:
info: Microsoft.EntityFrameworkCore.Database.Command[20101]
Executed DbCommand (32ms) [Parameters=[@__id_0='?' (DbType = Int32)], CommandType='Text', CommandTimeout='30']
SELECT [b].[ID], [b].[Author], [b].[DateCreated], [b].[DateUpdated], [b].[Edition], [b].[Genre], [b].[ISBN], [b].[Location], [b].[Title], [b].[YearPublished], [r].[ID], [r].[Approver], [r].[BookID], [r].[Comment], [r].[DateCreated], [r].[DateReviewed], [r].[DateUpdated], [r].[Heading], [r].[IsVisible], [r].[Rating], [r].[Reviewer]
FROM [Books] AS [b]
LEFT JOIN [Reviews] AS [r] ON [b].[ID] = [r].[BookID]
WHERE [b].[ID] = @__id_0
ORDER BY [b].[ID], [r].[ID]
and a select SQL on Loans records:
info: Microsoft.EntityFrameworkCore.Database.Command[20101]
Executed DbCommand (3ms) [Parameters=[@__p_0='?' (DbType = Int32)], CommandType='Text', CommandTimeout='30']
SELECT [l].[ID], [l].[BookID], [l].[DateCreated], [l].[DateDue], [l].[DateLoaned], [l].[DateReturn], [l].[DateUpdated], [l].[LoanedBy], [l].[OnShelf], [l].[ReturnMethod]
FROM [Loans] AS [l]
WHERE [l].[BookID] = @__p_0
The final query on the Loans entity is executed when the collection is returned from the function.
In the final section, I will explain some of the benefits and drawbacks when using the eager loading pattern.
Benefits and Drawbacks when using the Eager Loading Pattern
What we have seen from the Eager Loading query is that the execution of the LINQ query will run an initial SQL to retrieve the data from the specific entity, Reviews. The data from the other navigation property, Loans in the POCO class for the Books entity is retrieved from the returned collection by running an additional SQL on the Loans table.
You have seen how useful Eager Loading is for a self-contained database using EF Core, especially application forms that load data from multiple details tables.
This can save the developer from having to write additional SQL and LINQ code to retrieve data from dependent child tables.
Eager data loading has a benefit in the client side when the entity being loaded is required in a client interface that has a list or drop-down list that has data related to a parent record. The parent record may also have other related navigational entities that do not require loading in the same query.
Where we require multiple related navigational data to be loaded into a client from a query, the eager loading pattern is not always recommended since it does an additional SQL JOIN for each navigational property which can be a performance hit when the data sizes are large.
Where we require multiple related properties to be loaded, the recommendation is to use the eager loading on multiple queries, one for each related navigational property. Splitting the above LINQ query maybe more optimal using the following two queries:
var bookLoans = _db.Books
Include(b => b.Loans).
ToList();
and
var bookReviews = _db.Books
Include(r => r.Reviews).
ToList();
In the next post I will discuss another ORM loading pattern, the Explicit Loading pattern.
That is all for today’s post.
I hope you found this post useful and informative.
Andrew Halil is a blogger, author and software developer with expertise of many areas in the information technology industry including full-stack web and native cloud based development, test driven development and Devops.
