"
 
 
 
ASP.NET (snapshot 2017) Microsoft documentation and samples

Reading Related Data with the Entity Framework in an ASP.NET MVC Application

by Tom Dykstra

Download Completed Project or Download PDF

The Contoso University sample web application demonstrates how to create ASP.NET MVC 5 applications using the Entity Framework 6 Code First and Visual Studio 2013. For information about the tutorial series, see the first tutorial in the series.

In the previous tutorial you completed the School data model. In this tutorial you’ll read and display related data — that is, data that the Entity Framework loads into navigation properties.

The following illustrations show the pages that you’ll work with.

Instructors_index_page_with_instructor_and_course_selected
Instructors_index_page_with_instructor_and_course_selected

There are several ways that the Entity Framework can load related data into the navigation properties of an entity:

Because they don’t immediately retrieve the property values, lazy loading and explicit loading are also both known as deferred loading.

Performance considerations

If you know you need related data for every entity retrieved, eager loading often offers the best performance, because a single query sent to the database is typically more efficient than separate queries for each entity retrieved. For example, in the above examples, suppose that each department has ten related courses. The eager loading example would result in just a single (join) query and a single round trip to the database. The lazy loading and explicit loading examples would both result in eleven queries and eleven round trips to the database. The extra round trips to the database are especially detrimental to performance when latency is high.

On the other hand, in some scenarios lazy loading is more efficient. Eager loading might cause a very complex join to be generated, which SQL Server can’t process efficiently. Or if you need to access an entity’s navigation properties only for a subset of a set of the entities you’re processing, lazy loading might perform better because eager loading would retrieve more data than you need. If performance is critical, it’s best to test performance both ways in order to make the best choice.

Lazy loading can mask code that causes performance problems. For example, code that doesn’t specify eager or explicit loading but processes a high volume of entities and uses several navigation properties in each iteration might be very inefficient (because of many round trips to the database). An application that performs well in development using an on premise SQL server might have performance problems when moved to Azure SQL Database due to the increased latency and lazy loading. Profiling the database queries with a realistic test load will help you determine if lazy loading is appropriate. For more information see Demystifying Entity Framework Strategies: Loading Related Data and Using the Entity Framework to Reduce Network Latency to SQL Azure.

Disable lazy loading before serialization

If you leave lazy loading enabled during serialization, you can end up querying significantly more data than you intended. Serialization generally works by accessing each property on an instance of a type. Property access triggers lazy loading, and those lazy loaded entities are serialized. The serialization process then accesses each property of the lazy-loaded entities, potentially causing even more lazy loading and serialization. To prevent this run-away chain reaction, turn lazy loading off before you serialize an entity.

Serialization can also be complicated by the proxy classes that the Entity Framework uses, as explained in the Advanced Scenarios tutorial.

One way to avoid serialization problems is to serialize data transfer objects (DTOs) instead of entity objects, as shown in the Using Web API with Entity Framework tutorial.

If you don’t use DTOs, you can disable lazy loading and avoid proxy issues by disabling proxy creation.

Here are some other ways to disable lazy loading:

Create a Courses Page That Displays Department Name

The Course entity includes a navigation property that contains the Department entity of the department that the course is assigned to. To display the name of the assigned department in a list of courses, you need to get the Name property from the Department entity that is in the Course.Department navigation property.

Create a controller named CourseController (not CoursesController) for the Course entity type, using the same options for the MVC 5 Controller with views, using Entity Framework scaffolder that you did earlier for the Student controller, as shown in the following illustration:

Add_Controller_dialog_box_for_Course_controller
Add_Controller_dialog_box_for_Course_controller

Open Controllers.cs and look at the Index method:

[!code-csharpMain]

   1:  public ActionResult Index()
   2:  {
   3:      var courses = db.Courses.Include(c => c.Department);
   4:      return View(courses.ToList());
   5:  }

The automatic scaffolding has specified eager loading for the Department navigation property by using the Include method.

Open Views.cshtml and replace the template code with the following code. The changes are highlighted:

[!code-cshtmlMain]

   1:  @model IEnumerable<ContosoUniversity.Models.Course>
   2:   
   3:  @{
   4:      ViewBag.Title = "Courses";
   5:  }
   6:   
   7:  <h2>Courses</h2>
   8:   
   9:  <p>
  10:      @Html.ActionLink("Create New", "Create")
  11:  </p>
  12:  <table class="table">
  13:      <tr>
  14:          <th>
  15:              @Html.DisplayNameFor(model => model.CourseID)
  16:          </th>
  17:          <th>
  18:              @Html.DisplayNameFor(model => model.Title)
  19:          </th>
  20:          <th>
  21:              @Html.DisplayNameFor(model => model.Credits)
  22:          </th>
  23:          <th>
  24:              Department
  25:          </th>
  26:          <th></th>
  27:      </tr>
  28:   
  29:  @foreach (var item in Model) {
  30:      <tr>
  31:          <td>
  32:              @Html.DisplayFor(modelItem => item.CourseID)
  33:          </td>
  34:          <td>
  35:              @Html.DisplayFor(modelItem => item.Title)
  36:          </td>
  37:          <td>
  38:              @Html.DisplayFor(modelItem => item.Credits)
  39:          </td>
  40:          <td>
  41:              @Html.DisplayFor(modelItem => item.Department.Name)
  42:          </td>
  43:          <td>
  44:              @Html.ActionLink("Edit", "Edit", new { id=item.CourseID }) |
  45:              @Html.ActionLink("Details", "Details", new { id=item.CourseID }) |
  46:              @Html.ActionLink("Delete", "Delete", new { id=item.CourseID })
  47:          </td>
  48:      </tr>
  49:  }
  50:   
  51:  </table>

You’ve made the following changes to the scaffolded code:

Notice that for the Department column, the scaffolded code displays the Name property of the Department entity that’s loaded into the Department navigation property:

[!code-cshtmlMain]

   1:  <td>
   2:      @Html.DisplayFor(modelItem => item.Department.Name)
   3:  </td>

Run the page (select the Courses tab on the Contoso University home page) to see the list with department names.

Courses_index_page_with_department_names
Courses_index_page_with_department_names

Create an Instructors Page That Shows Courses and Enrollments

In this section you’ll create a controller and view for the Instructor entity in order to display the Instructors page:

Instructors_index_page_with_instructor_and_course_selected
Instructors_index_page_with_instructor_and_course_selected

This page reads and displays related data in the following ways:

Create a View Model for the Instructor Index View

The Instructors page shows three different tables. Therefore, you’ll create a view model that includes three properties, each holding the data for one of the tables.

In the ViewModels folder, create InstructorIndexData.cs and replace the existing code with the following code:

[!code-csharpMain]

   1:  using System.Collections.Generic;
   2:  using ContosoUniversity.Models;
   3:   
   4:  namespace ContosoUniversity.ViewModels
   5:  {
   6:      public class InstructorIndexData
   7:      {
   8:          public IEnumerable<Instructor> Instructors { get; set; }
   9:          public IEnumerable<Course> Courses { get; set; }
  10:          public IEnumerable<Enrollment> Enrollments { get; set; }
  11:      }
  12:  }

Create the Instructor Controller and Views

Create an InstructorController (not InstructorsController) controller with EF read/write actions as shown in the following illustration:

Add_Controller_dialog_box_for_Instructor_controller
Add_Controller_dialog_box_for_Instructor_controller

Open Controllers.cs and add a using statement for the ViewModels namespace:

[!code-csharpMain]

   1:  using ContosoUniversity.ViewModels;

The scaffolded code in the Index method specifies eager loading only for the OfficeAssignment navigation property:

[!code-csharpMain]

   1:  public ActionResult Index()
   2:  {
   3:      var instructors = db.Instructors.Include(i => i.OfficeAssignment);
   4:      return View(instructors.ToList());
   5:  }

Replace the Index method with the following code to load additional related data and put it in the view model:

[!code-csharpMain]

   1:  public ActionResult Index(int? id, int? courseID)
   2:  {
   3:      var viewModel = new InstructorIndexData();
   4:      viewModel.Instructors = db.Instructors
   5:          .Include(i => i.OfficeAssignment)
   6:          .Include(i => i.Courses.Select(c => c.Department))
   7:          .OrderBy(i => i.LastName);
   8:   
   9:      if (id != null)
  10:      {
  11:          ViewBag.InstructorID = id.Value;
  12:          viewModel.Courses = viewModel.Instructors.Where(
  13:              i => i.ID == id.Value).Single().Courses;
  14:      }
  15:   
  16:      if (courseID != null)
  17:      {
  18:          ViewBag.CourseID = courseID.Value;
  19:          viewModel.Enrollments = viewModel.Courses.Where(
  20:              x => x.CourseID == courseID).Single().Enrollments;
  21:      }
  22:   
  23:      return View(viewModel);
  24:  }

The method accepts optional route data (id) and a query string parameter (courseID) that provide the ID values of the selected instructor and selected course, and passes all of the required data to the view. The parameters are provided by the Select hyperlinks on the page.

The code begins by creating an instance of the view model and putting in it the list of instructors. The code specifies eager loading for the Instructor.OfficeAssignment and the Instructor.Courses navigation property.

[!code-csharpMain]

   1:  var viewModel = new InstructorIndexData();
   2:  viewModel.Instructors = db.Instructors
   3:      .Include(i => i.OfficeAssignment)
   4:      .Include(i => i.Courses.Select(c => c.Department))
   5:       .OrderBy(i => i.LastName);

The second Include method loads Courses, and for each Course that is loaded it does eager loading for the Course.Department navigation property.

[!code-csharpMain]

   1:  .Include(i => i.Courses.Select(c => c.Department))

As mentioned previously, eager loading is not required but is done to improve performance. Since the view always requires the OfficeAssignment entity, it’s more efficient to fetch that in the same query. Course entities are required when an instructor is selected in the web page, so eager loading is better than lazy loading only if the page is displayed more often with a course selected than without.

If an instructor ID was selected, the selected instructor is retrieved from the list of instructors in the view model. The view model’s Courses property is then loaded with the Course entities from that instructor’s Courses navigation property.

[!code-csharpMain]

   1:  if (id != null)
   2:  {
   3:      ViewBag.InstructorID = id.Value;
   4:      viewModel.Courses = viewModel.Instructors.Where(i => i.ID == id.Value).Single().Courses;
   5:  }

The Where method returns a collection, but in this case the criteria passed to that method result in only a single Instructor entity being returned. The Single method converts the collection into a single Instructor entity, which gives you access to that entity’s Courses property.

You use the Single method on a collection when you know the collection will have only one item. The Single method throws an exception if the collection passed to it is empty or if there’s more than one item. An alternative is SingleOrDefault, which returns a default value (null in this case) if the collection is empty. However, in this case that would still result in an exception (from trying to find a Courses property on a null reference), and the exception message would less clearly indicate the cause of the problem. When you call the Single method, you can also pass in the Where condition instead of calling the Where method separately:

[!code-csharpMain]

   1:  .Single(i => i.ID == id.Value)

Instead of:

[!code-csharpMain]

   1:  .Where(I => i.ID == id.Value).Single()

Next, if a course was selected, the selected course is retrieved from the list of courses in the view model. Then the view model’s Enrollments property is loaded with the Enrollment entities from that course’s Enrollments navigation property.

[!code-csharpMain]

   1:  if (courseID != null)
   2:  {
   3:      ViewBag.CourseID = courseID.Value;
   4:      viewModel.Enrollments = viewModel.Courses.Where(
   5:          x => x.CourseID == courseID).Single().Enrollments;
   6:  }

Modify the Instructor Index View

In Views.cshtml, replace the template code with the following code. The changes are highlighted:

[!code-cshtmlMain]

   1:  @model ContosoUniversity.ViewModels.InstructorIndexData
   2:   
   3:  @{
   4:      ViewBag.Title = "Instructors";
   5:  }
   6:   
   7:  <h2>Instructors</h2>
   8:   
   9:  <p>
  10:      @Html.ActionLink("Create New", "Create")
  11:  </p>
  12:  <table class="table">
  13:      <tr>
  14:          <th>Last Name</th>
  15:          <th>First Name</th>
  16:          <th>Hire Date</th>
  17:          <th>Office</th>
  18:          <th></th>
  19:      </tr>
  20:   
  21:      @foreach (var item in Model.Instructors)
  22:      {
  23:          string selectedRow = "";
  24:          if (item.ID == ViewBag.InstructorID)
  25:          {
  26:              selectedRow = "success";
  27:          }
  28:          <tr class="@selectedRow">
  29:              <td>
  30:                  @Html.DisplayFor(modelItem => item.LastName)
  31:              </td>
  32:              <td>
  33:                  @Html.DisplayFor(modelItem => item.FirstMidName)
  34:              </td>
  35:              <td>
  36:                  @Html.DisplayFor(modelItem => item.HireDate)
  37:              </td>
  38:              <td>
  39:                  @if (item.OfficeAssignment != null)
  40:                  {
  41:                      @item.OfficeAssignment.Location
  42:                  }
  43:              </td>
  44:              <td>
  45:                  @Html.ActionLink("Select", "Index", new { id = item.ID }) |
  46:                  @Html.ActionLink("Edit", "Edit", new { id = item.ID }) |
  47:                  @Html.ActionLink("Details", "Details", new { id = item.ID }) |
  48:                  @Html.ActionLink("Delete", "Delete", new { id = item.ID })
  49:              </td>
  50:          </tr>
  51:      }
  52:   
  53:      </table>

You’ve made the following changes to the existing code:

Run the application and select the Instructors tab. The page displays the Location property of related OfficeAssignment entities and an empty table cell when there’s no related OfficeAssignment entity.

Instructors_index_page_with_nothing_selected
Instructors_index_page_with_nothing_selected

In the Views.cshtml file, after the closing table element (at the end of the file), add the following code. This code displays a list of courses related to an instructor when an instructor is selected.

[!code-cshtmlMain]

   1:  @if (Model.Courses != null)
   2:  {
   3:      <h3>Courses Taught by Selected Instructor</h3>
   4:      <table class="table">
   5:          <tr>
   6:              <th></th>
   7:              <th>Number</th>
   8:              <th>Title</th>
   9:              <th>Department</th>
  10:          </tr>
  11:   
  12:          @foreach (var item in Model.Courses)
  13:          {
  14:              string selectedRow = "";
  15:              if (item.CourseID == ViewBag.CourseID)
  16:              {
  17:                  selectedRow = "success";
  18:              }
  19:              <tr class="@selectedRow">
  20:                  <td>
  21:                      @Html.ActionLink("Select", "Index", new { courseID = item.CourseID })
  22:                  </td>
  23:                  <td>
  24:                      @item.CourseID
  25:                  </td>
  26:                  <td>
  27:                      @item.Title
  28:                  </td>
  29:                  <td>
  30:                      @item.Department.Name
  31:                  </td>
  32:              </tr>
  33:          }
  34:   
  35:      </table>
  36:  }

This code reads the Courses property of the view model to display a list of courses. It also provides a Select hyperlink that sends the ID of the selected course to the Index action method.

Run the page and select an instructor. Now you see a grid that displays courses assigned to the selected instructor, and for each course you see the name of the assigned department.

Instructors_index_page_with_instructor_selected
Instructors_index_page_with_instructor_selected

After the code block you just added, add the following code. This displays a list of the students who are enrolled in a course when that course is selected.

[!code-cshtmlMain]

   1:  @if (Model.Enrollments != null)
   2:  {
   3:      <h3>
   4:          Students Enrolled in Selected Course
   5:      </h3>
   6:      <table class="table">
   7:          <tr>
   8:              <th>Name</th>
   9:              <th>Grade</th>
  10:          </tr>
  11:          @foreach (var item in Model.Enrollments)
  12:          {
  13:              <tr>
  14:                  <td>
  15:                      @item.Student.FullName
  16:                  </td>
  17:                  <td>
  18:                      @Html.DisplayFor(modelItem => item.Grade)
  19:                  </td>
  20:              </tr>
  21:          }
  22:      </table>
  23:  }

This code reads the Enrollments property of the view model in order to display a list of students enrolled in the course.

Run the page and select an instructor. Then select a course to see the list of enrolled students and their grades.

Instructors_index_page_with_instructor_and_course_selected
Instructors_index_page_with_instructor_and_course_selected

Adding Explicit Loading

Open InstructorController.cs and look at how the Index method gets the list of enrollments for a selected course:

[!code-csharpMain]

   1:  if (courseID != null)
   2:  {
   3:      ViewBag.CourseID = courseID.Value;
   4:      viewModel.Enrollments = viewModel.Courses.Where(
   5:          x => x.CourseID == courseID).Single().Enrollments;
   6:  }

When you retrieved the list of instructors, you specified eager loading for the Courses navigation property and for the Department property of each course. Then you put the Courses collection in the view model, and now you’re accessing the Enrollments navigation property from one entity in that collection. Because you didn’t specify eager loading for the Course.Enrollments navigation property, the data from that property is appearing in the page as a result of lazy loading.

If you disabled lazy loading without changing the code in any other way, the Enrollments property would be null regardless of how many enrollments the course actually had. In that case, to load the Enrollments property, you’d have to specify either eager loading or explicit loading. You’ve already seen how to do eager loading. In order to see an example of explicit loading, replace the Index method with the following code, which explicitly loads the Enrollments property. The code changed are highlighted.

[!code-csharpMain]

   1:  public ActionResult Index(int? id, int? courseID)
   2:  {
   3:      var viewModel = new InstructorIndexData();
   4:   
   5:      viewModel.Instructors = db.Instructors
   6:          .Include(i => i.OfficeAssignment)
   7:          .Include(i => i.Courses.Select(c => c.Department))
   8:          .OrderBy(i => i.LastName);
   9:   
  10:      if (id != null)
  11:      {
  12:          ViewBag.InstructorID = id.Value;
  13:          viewModel.Courses = viewModel.Instructors.Where(
  14:              i => i.ID == id.Value).Single().Courses;
  15:      }
  16:      
  17:      if (courseID != null)
  18:      {
  19:          ViewBag.CourseID = courseID.Value;
  20:          // Lazy loading
  21:          //viewModel.Enrollments = viewModel.Courses.Where(
  22:          //    x => x.CourseID == courseID).Single().Enrollments;
  23:          // Explicit loading
  24:          var selectedCourse = viewModel.Courses.Where(x => x.CourseID == courseID).Single();
  25:          db.Entry(selectedCourse).Collection(x => x.Enrollments).Load();
  26:          foreach (Enrollment enrollment in selectedCourse.Enrollments)
  27:          {
  28:              db.Entry(enrollment).Reference(x => x.Student).Load();
  29:          }
  30:   
  31:          viewModel.Enrollments = selectedCourse.Enrollments;
  32:      }
  33:   
  34:      return View(viewModel);
  35:  }

After getting the selected Course entity, the new code explicitly loads that course’s Enrollments navigation property:

[!code-csharpMain]

   1:  db.Entry(selectedCourse).Collection(x => x.Enrollments).Load();

Then it explicitly loads each Enrollment entity’s related Student entity:

[!code-csharpMain]

   1:  db.Entry(enrollment).Reference(x => x.Student).Load();

Notice that you use the Collection method to load a collection property, but for a property that holds just one entity, you use the Reference method.

Run the Instructor Index page now and you’ll see no difference in what’s displayed on the page, although you’ve changed how the data is retrieved.

Summary

You’ve now used all three ways (lazy, eager, and explicit) to load related data into navigation properties. In the next tutorial you’ll learn how to update related data.

Please leave feedback on how you liked this tutorial and what we could improve. You can also request new topics at Show Me How With Code.

Links to other Entity Framework resources can be found in the ASP.NET Data Access - Recommended Resources.

Previous Next





Comments ( )
<00>  <01>  <02>  <03>  <04>  <05>  <06>  <07>  <08>  <09>  <10>  <11>  <12>  <13>  <14>  <15>  <16>  <17>  <18>  <19>  <20>  <21>  <22>  <23
Link to this page: //www.vb-net.com/AspNet-DocAndSamples-2017/aspnet/mvc/overview/getting-started/getting-started-with-ef-using-mvc/reading-related-data-with-the-entity-framework-in-an-asp-net-mvc-application.htm
<SITEMAP>  <MVC>  <ASP>  <NET>  <DATA>  <KIOSK>  <FLEX>  <SQL>  <NOTES>  <LINUX>  <MONO>  <FREEWARE>  <DOCS>  <ENG>  <CHAT ME>  <ABOUT ME>  < THANKS ME>