Reducing Query Complexity9:35 with James Churchill
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.
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 up