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.