1 00:00:00,420 --> 00:00:05,470 Sometimes it's necessary for an EF query to return a large number of records. 2 00:00:05,470 --> 00:00:09,140 Maybe you need to process a large number of records as part of a monthly, 3 00:00:09,140 --> 00:00:12,340 quarterly, or yearly business process. 4 00:00:12,340 --> 00:00:16,350 Or you need to implement a feature that allows the user to export a list of 5 00:00:16,350 --> 00:00:21,800 records to a file like a CSV text file or an Excel spreadsheet. 6 00:00:21,800 --> 00:00:25,200 Whatever your situation might be, using EF to query for 7 00:00:25,200 --> 00:00:29,100 a large number of records can cause a performance issue if you're not careful. 8 00:00:30,890 --> 00:00:33,960 Here's the query to retrieve the list of comic books for our home page. 9 00:00:35,100 --> 00:00:37,850 When executing this query, EF will generate and 10 00:00:37,850 --> 00:00:41,920 execute the SQL to retrieve the entity data from the database. 11 00:00:41,920 --> 00:00:47,650 Instantiate a comic book entity and related series entity for each data record 12 00:00:47,650 --> 00:00:52,740 returned from the database and instantiate an entry object for each entity. 13 00:00:53,750 --> 00:00:55,460 Instantiating an entry object for 14 00:00:55,460 --> 00:00:59,310 each entity allows the context to track each entity. 15 00:00:59,310 --> 00:01:04,060 Unfortunately, this adds overhead to the overall query process. 16 00:01:04,060 --> 00:01:05,910 Under normal circumstances, 17 00:01:05,910 --> 00:01:10,850 this additional overhead is typically not noticable, so it's generally acceptable. 18 00:01:12,070 --> 00:01:16,800 But if you are retrieving a large number of records, this additional overhead 19 00:01:16,800 --> 00:01:20,450 might unnecessarily slow down the query process or 20 00:01:20,450 --> 00:01:26,410 consume an unexpected memory on the server that's hosting your web application. 21 00:01:26,410 --> 00:01:29,590 Luckily EF gives us a convenient escape hatch. 22 00:01:30,710 --> 00:01:35,230 We can add a call to the DB set AsNoTracking method. 23 00:01:35,230 --> 00:01:40,470 Calling the AsNoTracking method tells EF to not track the materialized entities, 24 00:01:40,470 --> 00:01:44,070 which saves having to instantiate an entry object for each entity. 25 00:01:45,170 --> 00:01:48,640 Let's run the app to make sure that the list of comic books on the home page 26 00:01:48,640 --> 00:01:49,790 still works as expected. 27 00:01:51,860 --> 00:01:54,641 And here's the list of comic books, as expected. 28 00:01:57,576 --> 00:02:03,380 While the AsNoTracking method is easy to use, using it does have a downside. 29 00:02:03,380 --> 00:02:07,070 Because the materialized entities aren't tracked by the context, 30 00:02:07,070 --> 00:02:12,120 automatic relationship fix up like we used in the previous video as an alternative to 31 00:02:12,120 --> 00:02:16,990 eagerly loading related entities using the include method, won't work. 32 00:02:16,990 --> 00:02:21,332 To demonstrate this, let's rewrite this query to rely upon relationship fix up. 33 00:02:36,795 --> 00:02:38,320 Now let's run our app again. 34 00:02:39,610 --> 00:02:41,860 Noticed here in the first column of the table, 35 00:02:41,860 --> 00:02:44,340 the comic book series titles are now missing. 36 00:02:46,250 --> 00:02:48,490 To see first hand in the code what is happening, 37 00:02:48,490 --> 00:02:51,743 let's set a break point on the return statement and reload the page. 38 00:02:58,168 --> 00:03:00,090 Here we are at our break point. 39 00:03:00,090 --> 00:03:02,960 We can drill into the collection of the comic books, and 40 00:03:02,960 --> 00:03:05,970 see that the first item series property is null. 41 00:03:08,560 --> 00:03:14,515 If we comment out the call to the AsNoTracking method And 42 00:03:14,515 --> 00:03:19,940 run the app again, we can see that the first item series property is now set. 43 00:03:26,093 --> 00:03:28,420 Press F5 to continue execution. 44 00:03:31,440 --> 00:03:35,968 And here's our home page, now displaying the expected comic book series titles. 45 00:03:38,556 --> 00:03:43,015 So if you're going to use the AsNoTracking method, just remember to eagerly load 46 00:03:43,015 --> 00:03:47,232 any related entities that you need in your results using the include method. 47 00:03:53,951 --> 00:03:57,707 You may or may not run into any performance issues when using EF to query 48 00:03:57,707 --> 00:03:59,810 for a large number of records. 49 00:03:59,810 --> 00:04:03,060 If you do, try using the AsNoTracking method. 50 00:04:03,060 --> 00:04:05,220 Hopefully that resolves the issue and 51 00:04:05,220 --> 00:04:09,260 you can continue to use EF for all of your data access code. 52 00:04:09,260 --> 00:04:11,040 If that doesn't resolve the issue, 53 00:04:11,040 --> 00:04:16,300 you might need to look at using an alternative approach, like using ADO.net. 54 00:04:16,300 --> 00:04:18,240 See the Teacher's Notes for more information.