This workshop will be retired on May 1, 2025.
Heads up! To view this whole video, sign in with your Courses account or enroll in your free 7-day trial. Sign In Enroll
Preview
Start a free Courses trial
to watch this video
Using EF's `Include` method to eagerly load related entities can result in overly-complex queries. Let's take a look at a couple of potential solutions.
Related Discussions
Have questions about this video? Start a discussion with the community and Treehouse staff.
Sign upRelated Discussions
Have questions about this video? Start a discussion with the community and Treehouse staff.
Sign up
As we've seen in other
Treehouse content about EF,
0:00
when writing queries to retrieve
entities from the database.
0:02
You can use EFs include method to
eagerly load related entities.
0:05
The upside to this approach
is that it's easy to do.
0:10
The downside is that it can
result in overly complex queries.
0:14
You can see here at the bottom
of the browser window,
0:19
that I've enabled the tool Glimpse.
0:21
Which will allow us to audit
each request made to the server.
0:23
For information about how to install and
0:27
configure Glimpse,
see the previous video in this workshop.
0:29
Let's click on the first Comic Books
Detail button to view the detail for
0:34
that comic book.
0:37
Once the page is finished loading, we can
select the SQL tab in the Glimpse panel.
0:39
To see the queries that were generated and
executed by EF.
0:43
This particular SQL query
may not be easy to read, but
0:47
it's not what I describe
as an overly complex query.
0:50
And if we look at
the performance of the query,
0:54
it's certainly not taking
a long time to execute either.
0:56
Let's take a look at the EF query.
1:01
When to include related
entities parameter.
1:04
Set the true, which is the case when this
method is called from the comic books
1:06
controller, detail action method.
1:10
Then our query uses three
calls to the include method.
1:12
To eagerly load the comic
book's related entities.
1:16
Three calls to the include
method isn't too bad.
1:19
But with more complex real world data
models you might need to make ten or
1:22
more calls to the include method.
1:26
In order to eagerly load all of
the related entities that are required to
1:28
display the information that
your users need or expect.
1:33
The more calls to the include method that
you make, the more likely that you'll end
1:37
up with an EF query that generates
unexpectedly complex SQL.
1:41
This is especially true when
eagerly loading related entities
1:46
on collections of entities.
1:49
How complex can the SQL get?
1:52
On one project,
I saw a 10,000+ line SQL query
1:54
that took on average
over a minute to execute.
1:58
Let's take a look at a potential
solution for this problem.
2:02
Even though our EF query is working fine,
let's imagine that's not the case.
2:06
And rewrite it to not
use the include method.
2:11
We'll look at two alternative approaches
for loading related entities.
2:14
Using explicit loading, and
leveraging relationship fix up.
2:18
First up, explicit loading.
2:23
To start, let's comment out
the method's existing implementation.
2:25
At the top of the method let's
write a query to retrieve
2:31
a single comicBook entity.
2:34
Go ahead and return the comicBook entity.
2:53
Then just about the return statement, add
an if statement that checks if the include
2:59
related in these parameter is set to true.
3:04
To explicitly load related entities,
3:08
we need to reference to the comicBook
entities context entry.
3:10
As we saw in an earlier Treehouse
course that covered the basics of EF,
3:16
the entry method returns a DB Entity Entry
object for the referenced entity.
3:20
Every entity that has
been materialized and
3:27
tracked by the context has a DB Entity
Entry object associated with it.
3:29
This object exposes two methods that
we can use to get the entry objects for
3:35
navigation properties.
3:39
Reference for
non collection navigation properties.
3:43
And Collection for
navigation collection properties.
3:47
The entry objects that are returned by
these methods provide a load method.
3:52
That when called will load
the related entities.
3:56
To explicitly load the series property,
which is a non-collection navigation
4:00
property, we'll use the Reference
method to get the entry.
4:04
Then we'll call Load on
the returned entry object.
4:10
To load the artist property,
which is a collection navigation property,
4:14
we'll use the collection
method to get the entry.
4:18
Then, instead of coding the load
method on the returned entry object,
4:23
we'll call the query method.
4:27
The query method returns the EF query that
would be used to load the collection.
4:29
Getting the reference to
the query allows us to modify it.
4:34
Specifically in this case, to eagerly load
the comic book artist in this Artist and
4:38
Role navigation properties.
4:42
Then add a call to the ToList method
to force execution of the query.
4:48
Let's test our changes.
4:54
Here's our homepage, click on
the first comic book's Detail button.
4:57
In the Glimpse SQL tab, we can now see
that three queries were generated and
5:05
executed.
5:09
One to retrieve the comic book entity,
another to retrieve the series entity and
5:12
another to retrieve the comic
book artists entities.
5:19
So, by switching from
eager to explicit loading,
5:25
we're now running three
queries instead of one.
5:27
But each of the three queries is
simpler than a single, combined query.
5:30
Remember, in this case, our original query
was performing in an acceptable manner so
5:35
this refactoring wasn't necessary.
5:40
We're simply doing it in order
to have an example to work with.
5:43
Now let's look at an approach that
leverages EFs relationship fix up feature.
5:47
To start, we need a query to
retrieve the comic book series.
5:56
And we need a query to retrieve
the comic book's artist.
6:13
Let's set a breakpoint just inside of
the if statement and run our web app.
6:42
Here's our comic books list page.
6:49
Click the first Detail button.
6:51
Okay, here we are at our break point.
6:54
In the watch window, let's add a watch for
the comicBook variable.
6:56
Now, we can see that the comic book
series navigation property is null.
7:03
And the artist navigation collection
property has a count of zero.
7:08
Press F10 to execute the query to
retrieve the comic book series.
7:13
In the watch window,
7:18
notice that the comic book series property
now has a reference to an entity.
7:19
Press F10 again to execute the query
to retrieve the comic book artist.
7:25
Now the artist collection
has two items in it.
7:29
To recap, we added queries to
retrieve the related entities.
7:35
But we didn't write any code to capture
references to those entities and
7:38
set the appropriate navigation
properties on the comic book entity.
7:43
This approach highlights an EF feature
called automatic relationship fix-up.
7:48
Any time that you run a query against
one of the context's DbSet properties,
7:53
EF will automatically review
the context's tracked entities.
7:57
During this review process,
8:01
EF detected that the comic book
series navigation property was null.
8:02
And then checked to see if the appropriate
series entity was loaded in the context.
8:07
After finding the expected series entity,
EF automatically set the ComicBooks
8:12
series navigation property with
the reference to that series entity.
8:17
It also added the comic book
entity to the series entity's
8:21
comic books navigation
collection property.
8:24
With automatic relationship fix up,
8:27
two related entities will
magically find each other.
8:30
Even when they are loaded into
the context using separate queries.
8:33
Let's continue execution by pressing F5.
8:38
In order to make sure that the comic
book detail page successfully loads.
8:41
And here's our comic book's detail.
8:46
In the Glimpse SQL tab,
we can see that we're still generating and
8:51
executing three queries.
8:54
One query each to retrieve the comic book,
series and comic book artist entities.
8:56
After seeing the complications
that can arise from eagerly
9:03
loading related entities
using the include method.
9:06
You might be tempted to completely
avoid using that approach.
9:09
But that's not necessary to do.
9:13
In fact,
most of the time not only it'll work fine
9:15
it's often the preferred method for
loading related entities.
9:19
Just remember to keep an eye
on the complexity and
9:23
overall performance of your EF queries.
9:25
And if you need to, use one of
the approaches shown in this video
9:28
to reduce your reliance
on the include method.
9:32
You need to sign up for Treehouse in order to download course files.
Sign upYou need to sign up for Treehouse in order to set up Workspace
Sign up