Welcome to today’s post.
In today’s post I will be showing how to use the lazy loading ORM pattern to load entity data. Later in this post I will explain how Lazy Loading can also improve performance when querying data.
Today’s post applies to versions of Entity Framework Core 3.1. However, Lazy Loading has been a part of the Entity Framework Core since version 2.1.
Before I progress this discussion, I will explain what lazy loading is and what a navigation property is within an entity.
What is Lazy Loading?
Lazy loading is defined as data that is related to a base entity which is loaded when the navigation properties of the base entity are accessed.
Entity navigation properties are references to related entities of a base entity. A reference entity is accessible through a navigation property. In the backend database, a navigational property requires a foreign key to be defined between the base (parent) entity and the reference (detail) entity.
An example of a parent entity is a Book entity shown below:
For the above ER model, we have corresponding Entity POCO class for the Book entity shown below:
public class BookViewModel
{
public int ID { get; set; }
[Required]
public string Title { get; set; }
[Required]
public string Author { get; set; }
public int YearPublished { get; set; }
public string Genre { get; set; }
public string Edition { get; set; }
public string ISBN { get; set; }
[Required]
public string Location { get; set; }
public DateTime DateCreated { get; set; }
public DateTime DateUpdated { get; set; }
public virtual ICollection<LoanViewModel> Loans { get; set; }
public virtual ICollection<ReviewViewModel> Reviews { get; set; }
}
In the above entity, the related entities are Loans and Reviews, where the navigation properties are defined as shown:
public virtual ICollection<LoanViewModel> Loans { get; set; }
public virtual ICollection<ReviewViewModel> Reviews { get; set; }
Before the lazy loaded proxies can be injected into the virtual properties in instances of each of our POCO classes, we will need to install the following package:
Microsoft.EntityFrameworkCore.Proxies
And in ConfigureServices() in the startup class, we can use the UseLazyLoadingProxies() extension method to inject the lazy load proxies into our POCO classes when configuring our data context connection as shown:
.AddDbContext<BloggingContext>(
b => b.UseLazyLoadingProxies()
.UseSqlServer(myConnectionString));
If we fail to declare all references to other entity classes within our POCO classes as virtual, then when we run our application and it hits the EnsureCreated() command of the data context, then the following compiler error will show:
System.InvalidOperationException
HResult=0x80131509
Message=Navigation property 'Book' on entity type 'LoanViewModel' is not virtual.
UseLazyLoadingProxies requires all entity types to be public, unsealed, have virtual navigation properties,
and have a public or protected constructor.
Source=Microsoft.EntityFrameworkCore.Proxies
The above error can be avoided by linking the entities within our data context and identity key and foreign keys of the tables within the SQL database. I will show how this is done in the next section.
Linking Parent and Child Entities with the Fluent API
In the data context, to be able to link the virtual properties from the Book entity back to the Loan and Review detail entities, we use Fluent API within the overridden model creating method to link the parent and child (detail) entities:
public class ApplicationDbContext : DbContext
{
public ApplicationDbContext(DbContextOptions<ApplicationDbContext> options)
: base(options)
{
}
protected override void OnModelCreating(ModelBuilder builder)
{
base.OnModelCreating(builder);
builder.Entity<BookViewModel>().ToTable("Books");
builder.Entity<LoanViewModel>().ToTable("Loans");
builder.Entity<ReviewViewModel>().ToTable("Reviews");
…
builder.Entity<LoanViewModel>().
HasOne(p => p.Book).
WithMany(q => q.Loans).
HasForeignKey(p => p.BookID).HasConstraintName("FK_Book_Loan");
builder.Entity<ReviewViewModel>().
HasOne(p => p.Book).
WithMany(q => q.Reviews).
HasForeignKey(p => p.BookID).HasConstraintName("FK_Book_Review");
}
…
For the above foreign key references to work in EF Core, you will need to have the foreign key relationship constraint on the detail tables, Review and Loan.
For the Book and Review tables, the foreign key relationship can be constructed in SSMS by adding as a table key constraint as shown:
In the above I have deliberately disabled checks on existing data and enforcement within the designer to avoid having to remove existing data before committing the structural change. In a new table the above constraints can be enabled.
The resulting foreign key is shown under the table’s keys folder:
The same constraint can be created likewise for the Loan table.
How does the Lazy Loading Pattern help us with Application Performance?
Of the loading patterns that are used in Entity Framework Core, the lazy loading pattern allows the backend data from a parent entities (tables) navigation data to be loaded when the property is accessed within code. What this allows is delay loading of navigational data until it is accessed. This is beneficial for performance, especially when the navigation data contains large numbers of records that we do not wish to preload when the parent entity is queried. This can be result in a significant performance improvement especially when we have the following in the application:
- There are multiple cases of entities reference other entities.
- During loading of entities that reference other entities within a page load.
The default loading behavior of an entity that contains navigation properties is to pre-load records from navigation properties, and this slows down the client or API running the query. Without lazy loading the Book record will show the data as nulls:
{
"id": 1,
"title": "The Lord of the Kings",
"author": "J. R. R. Tolken",
"yearPublished": 1964,
"genre": "fantasy",
"edition": "0",
"isbn": "65423511",
"location": "sydney",
"dateCreated": "2019-11-05T00:00:00",
"dateUpdated": "2021-02-17T12:16:39.7501682",
"loans": null,
"reviews": null
}
Code to access the Review details via the navigation property is shown below:
public async Task<List<ReviewViewModel>> GetBookReviews(int id)
{
List<ReviewViewModel> result = _db.Books
.SingleOrDefault(b => b.ID == id)
.Reviews
.Where(r => r.BookID == id).ToList();
return result;
}
Before we can retrieve SQL output when the run the above LINQ query, 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 start-up method ConfigureServices(), setting up console 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();
});
The following SQL queries are run for the above LINQ query:
The log output for the Reviews table is shown:
info: Microsoft.EntityFrameworkCore.Database.Command[20101]
…
Executed DbCommand (0ms) [Parameters=[@__p_0='?' (DbType = Int32)], CommandType='Text', CommandTimeout='30']
SELECT [r].[ID], [r].[Approver], [r].[BookID], [r].[Comment], [r].[DateCreated], [r].[DateReviewed], [r].[DateUpdated], [r].[Heading], [r].[IsVisible], [r].[Rating], [r].[Reviewer]
FROM [Reviews] AS [r]
WHERE [r].[BookID] = @__p_0
The log output for the Loans table is shown:
info: Microsoft.EntityFrameworkCore.Database.Command[20101]
…
Executed DbCommand (0ms) [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.
We first obtain a matching book record, then obtain the corresponding reviews for the book.
Code to access the Loan details via the navigation property is shown below:
public async Task<List<LoanViewModel>> GetBookLoans(int id)
{
List<LoanViewModel> result = _db.Books
.Where(b => b.ID == id)
.SingleOrDefault()
.Loans
.ToList();
return result;
}
The log output for the Books table is shown:
info: Microsoft.EntityFrameworkCore.Database.Command[20101]
Executed DbCommand (1ms) [Parameters=[@__id_0='?' (DbType = Int32)], CommandType='Text', CommandTimeout='30']
SELECT TOP(2) [b].[ID], [b].[Author], [b].[DateCreated], [b].[DateUpdated], [b].[Edition], [b].[Genre], [b].[ISBN], [b].[Location], [b].[Title], [b].[YearPublished]
FROM [Books] AS [b]
WHERE [b].[ID] = @__id_0
The log output for the Loans table is shown:
info: Microsoft.EntityFrameworkCore.Database.Command[20101]
Executed DbCommand (0ms) [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 log output for the Reviews table is shown:
info: Microsoft.EntityFrameworkCore.Database.Command[20101]
Executed DbCommand (0ms) [Parameters=[@__p_0='?' (DbType = Int32)], CommandType='Text', CommandTimeout='30']
SELECT [r].[ID], [r].[Approver], [r].[BookID], [r].[Comment], [r].[DateCreated], [r].[DateReviewed], [r].[DateUpdated], [r].[Heading], [r].[IsVisible], [r].[Rating], [r].[Reviewer]
FROM [Reviews] AS [r]
WHERE [r].[BookID] = @__p_0
The final query on the Reviews entity is executed when the collection is returned from the function.
What we have seen from the Lazy Loading query is that the execution of the LINQ query will run an initial SQL to retrieve the data from the specific entity. The data from the remaining navigation property in the POCO class is retrieved from the returned collection by running an additional SQL on the entity for the remaining navigation entity table.
When we step into the first record of the _db.books collection, we can see the virtual collections for Review and Loan populated as shown:
On expanding the Reviews collection, we see the lazy loaded proxies for the Review navigation property POCO are populated:
To disable lazy loading for a particular navigation property, we remove the virtual keyword from its declaration.
You have seen how useful Lazy 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 writing additional SQL and LINQ code to retrieve data from dependent child tables.
In applications where the tables are distributed into different data bases, lazy loading is not applicable.
In the next post I will discuss another ORM loading pattern, the Eager 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.
