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

Entity Framework Query Performance

Preview

Start a free Courses trial
to watch this video

Sign up for Treehouse

Preventing Entity Tracking

4:19 with James Churchill

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:

Retrieving Data Using a DataReader

Related Discussions

Have questions about this video? Start a discussion with the community and Treehouse staff.

Sign up

    Related Discussions

    Have questions about this video? Start a discussion with the community and Treehouse staff.

    Sign up

    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

      You need to sign up for Treehouse in order to set up Workspace

      Sign up