How to Improve Application Performance with Explicit Loading ORM Patterns

Welcome to today’s post.

In today’s post I will be discussing how to use the ORM pattern to explicitly load entity data using Entity Framework Core.

In previous posts I showed how to load entity data using Entity Framework Core using the lazy load pattern and the eager load pattern. In both those posts I discussed how they would both improve the performance of an application when querying data. Likewise, in this post I will explain how using Explicit Loading can improve performance when querying data.

What is Explicit Loading?

Explicit loading is a technique that allows the developer to load the related data later when it is needed. The most common situation for using this loading pattern is in a user interface that has some data that is not visible to the user and only loads the data when the user selects or enables a particular part of a screen, a report, or the application settings.

In Entity Framework Core, to load related navigational data from an entity we use the Entry() API method of the data context.

In the previous posts on lazy loading and eager loading we based our queries on a Book entity relation shown below:

Given the following query to obtain a book entity record:

var book = _db.Books.Where(b => b.ID == id).SingleOrDefault();

The SQL query the above generates is:

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

In the next section, I will show how to load detail records using a useful Entity Framework Core API extension method.

Selectively Loading Detail Records using Navigational Properties

We can then selectively load navigational properties that are detail records (collections) using the Collection() API.

For the Loans navigational property, this is shown below:

_db.Entry(book)
    .Collection(l => l.Loans)
    .Load();

For the Reviews navigational property, this is shown below:

_db.Entry(book)
    .Collection(r => r.Reviews)
    .Load();

The Collection loads generated SQL statements as shown with a query on the Loans entity:

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 

and a query on the Reviews entity:

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

In the next section, I will show how to use another extension method to aggregate detail records from the navigational property.

Aggregating Detail Records using Navigational Properties

Using the Query() API method, we can aggregate records from a navigational property as shown with the Loans and Reviews collections:

var numberLoans = _db.Entry(book)
    .Collection(l => l.Loans)
    .Query()
    .Count();

var numberReviews = _db.Entry(book)
    .Collection(r => r.Reviews)
    .Query()
    .Count();

The above aggregation queries generate SQL statements as shown:

SELECT COUNT(*)
FROM [Loans] AS [l]
WHERE [l].[BookID] = @__p_0

SELECT COUNT(*)
FROM [Reviews] AS [r]
WHERE [r].[BookID] = @__p_0

We can also combine the Query() API method with the Take(), Where() and OrderBy() extension methods to create more complex queries. The following is a query that obtains the five most recent books reviews that have at least a rating of 1: 

var recent5Reviews = await _db.Entry(book)
    .Collection(r => r.Reviews)
    .Query()
    .Where(r => r.Rating > 0)
    .OrderByDescending(s => s.DateReviewed)
    .Take(5).ToListAsync();

The following is a query that obtains the five most recent books loans: 

var recent5Loans = await _db.Entry(book)
     .Collection(l => l.Loans)
     .Query()
     .OrderByDescending(m => m.DateLoaned)
     .Take(5).ToListAsync();

The corresponding SQL queries generated from the above LINQ queries are:

SELECT TOP(@__p_1) 	[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 AND [r].[Rating] > 0
ORDER BY [r].[DateReviewed] DESC

SELECT TOP(@__p_1) 	[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
ORDER BY [l].[DateLoaned] DESC

In all the above queries we have selectively loaded data from navigational properties, which we could combine to produce a report, or split them up into separate API methods that return a subset of data from an entity. The resulting record or collections can be used to optimally load data into a client API or report.

public async Task<BookReportViewModel> GetBookDetailsReport(int id)
{
    var book = _db.Books.Where(b => b.ID == id).SingleOrDefault();

    _db.Entry(book)
        .Collection(l => l.Loans)
       	.Load();

    _db.Entry(book)
       	.Collection(r => r.Reviews)
        .Load();

    var numberLoans = _db.Entry(book)
       	.Collection(l => l.Loans)
        .Query()
        .Count();

    var numberReviews = _db.Entry(book)
       	.Collection(r => r.Reviews)
        .Query()
        .Count();

    var recent5Reviews = await _db.Entry(book)
       	.Collection(r => r.Reviews)
        .Query()
        .OrderByDescending(s => s.DateReviewed)
        .Take(5).ToListAsync();

    var recent5Loans = await _db.Entry(book)
       	.Collection(l => l.Loans)
        .Query()
        .OrderByDescending(m => m.DateLoaned)
        .Take(5).ToListAsync();

    var bookReport = new BookReportViewModel()
    {
       	Title = book.Title,
        Author = book.Author,
        ISBN = book.ISBN,
        Edition = book.Edition,
        Genre = book.Genre,
        Location = book.Location,
        YearPublished = book.YearPublished,
        Loans = recent5Loans,
        Reviews = recent5Reviews,
        NumberOfLoans = numberLoans,
        NumberOfReviews = numberReviews
    };
    return bookReport;
}

When the above queries are run, the output for the resulting record will be:

As I suggested, we could also return the data as a structure that contains placeholder properties that tell the client UI or report if there is existing data in a navigational collection:

var bookReportHeader = new BookReportHeaderViewModel()
{
   	Title = book.Title,
    Author = book.Author,
    ISBN = book.ISBN,
    Edition = book.Edition,
    Genre = book.Genre,
    Location = book.Location,
    YearPublished = book.YearPublished,
    NumberOfLoans = numberLoans,
    NumberOfReviews = numberReviews
};

Explicit data loading has a benefit in the client side when the entity being loaded is an infrequently accessed data set. Loading entity data only when the user requests modification saves having to pre-load the data at application start up. Usually, data that is commonly used for lookups is beneficially pre-loaded using the lazy loading pattern. With data that is uncommonly accessed by the user, explicit loading is beneficial.  

In this post I have discussed the usage of the ORM explicit loading pattern. In the next post I will provide an overview of best practices when using the three ORM loading patterns in client applications and reports.

That is all for today’s post.

I hope you found this post useful and informative.

Andrew Halil

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.