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
Let's look at a technique for using Entity Framework to retrieve a large number of records.
Using ADO.NET to Query for a Large Number of Records
In some situations, disabling entity tracking still won't give you the necessary performance. When that happens, you can fallback to using an ADO.NET DataReader. For information (including code examples) on how to do this, see this article on Microsoft's official docs:
Sometimes it's necessary for an EF query
to return a large number of records.
0:00
Maybe you need to process a large
number of records as part of a monthly,
0:05
quarterly, or yearly business process.
0:09
Or you need to implement a feature that
allows the user to export a list of
0:12
records to a file like a CSV text file or
an Excel spreadsheet.
0:16
Whatever your situation might be,
using EF to query for
0:21
a large number of records can cause
a performance issue if you're not careful.
0:25
Here's the query to retrieve the list
of comic books for our home page.
0:30
When executing this query,
EF will generate and
0:35
execute the SQL to retrieve
the entity data from the database.
0:37
Instantiate a comic book entity and
related series entity for each data record
0:41
returned from the database and instantiate
an entry object for each entity.
0:47
Instantiating an entry object for
0:53
each entity allows the context
to track each entity.
0:55
Unfortunately, this adds overhead
to the overall query process.
0:59
Under normal circumstances,
1:04
this additional overhead is typically not
noticable, so it's generally acceptable.
1:05
But if you are retrieving a large number
of records, this additional overhead
1:12
might unnecessarily slow
down the query process or
1:16
consume an unexpected memory on the server
that's hosting your web application.
1:20
Luckily EF gives us
a convenient escape hatch.
1:26
We can add a call to the DB
set AsNoTracking method.
1:30
Calling the AsNoTracking method tells EF
to not track the materialized entities,
1:35
which saves having to instantiate
an entry object for each entity.
1:40
Let's run the app to make sure that
the list of comic books on the home page
1:45
still works as expected.
1:48
And here's the list of comic books,
as expected.
1:51
While the AsNoTracking method is easy
to use, using it does have a downside.
1:57
Because the materialized entities
aren't tracked by the context,
2:03
automatic relationship fix up like we used
in the previous video as an alternative to
2:07
eagerly loading related entities
using the include method, won't work.
2:12
To demonstrate this, let's rewrite this
query to rely upon relationship fix up.
2:16
Now let's run our app again.
2:36
Noticed here in the first
column of the table,
2:39
the comic book series
titles are now missing.
2:41
To see first hand in
the code what is happening,
2:46
let's set a break point on the return
statement and reload the page.
2:48
Here we are at our break point.
2:58
We can drill into the collection
of the comic books, and
3:00
see that the first item
series property is null.
3:02
If we comment out the call to
the AsNoTracking method And
3:08
run the app again, we can see that
the first item series property is now set.
3:14
Press F5 to continue execution.
3:26
And here's our home page, now displaying
the expected comic book series titles.
3:31
So if you're going to use the AsNoTracking
method, just remember to eagerly load
3:38
any related entities that you need in
your results using the include method.
3:43
You may or may not run into any
performance issues when using EF to query
3:53
for a large number of records.
3:57
If you do,
try using the AsNoTracking method.
3:59
Hopefully that resolves the issue and
4:03
you can continue to use EF for
all of your data access code.
4:05
If that doesn't resolve the issue,
4:09
you might need to look at using an
alternative approach, like using ADO.net.
4:11
See the Teacher's Notes for
more information.
4:16
You need to sign up for Treehouse in order to download course files.
Sign up