Training course. Loading data from the Entity Framework in an ASP.NET MVC application

This is an extension of a series of articles devoted to developing with the Entity Framework and ASP.NET MVC 3. You can find the first chapters at the following links:

• Creating the Entity Framework data model for an ASP.NET MVC application
• Implementing Basic CRUD Functionality with the Entity Framework in an ASP.NET MVC Application
• Sorting, filtering and paging with the Entity Framework in an ASP.NET MVC application
• Creating a complex data model for an ASP.NET MVC application
• Creating a complex data model for an ASP.NET MVC application, part 2

In the last lesson, we completed the School data model. In this tutorial we will load and display the necessary data loaded by the Entity Framework in the navigation properties.

The illustration shows the results of our work.


')

Lazy, Eager, and Explicit download necessary data

There are several methods that EF uses to load the necessary data into the navigation properties of an entity:

• Lazy loading . When the entity is first accessed, the corresponding associated data is not loaded. However, when you first access the navigation property, the associated data is loaded automatically. In this case, a set of queries is made to the database: one for the entity and one for each time the data is loaded.

• Eager loading . Data is loaded when the entity is accessed. This is usually followed by a single join request, which returns all data. You can specify the use of eager loading using the Include method.

• Explicit loading . This method is similar to lazy loading except for the fact that you yourself indicate data loading - this does not happen automatically when you access the navigation property. Data is loaded manually using an entity object manager and calling the Collection.Load method for collections and the Reference.Load method for properties with a single value. (In the example, if you want to load the Administrator navigation property, replace the Collection (x => x.Courses) with the Reference (x => x.Administrator).)

Due to the fact that the data is not immediately loaded, lazy loading and explicit loading have the common name deferred loading .

In general, if you have a need for data for each entity, the eager loading method offers the best performance because a single query is usually more efficient than a multitude of queries for each entity. For example, imagine that there are ten courses at each department. The example for eager loading will be accompanied by one join request. Examples for lazy loading and explicit loading will be followed by eleven.

On the other hand, if the access to the navigation properties of entities is rarely performed or a small number of entities is used, lazy loading can be more efficient - eager loading will load more data than necessary. Usually explicit loading is used only when lazy loading is disabled. Suppose there is a situation where lazy loading can be turned off during the serialization process, when you are sure that you do not need all the navigation properties. If lazy loading is enabled, all navigation properties will load automatically, since serialization refers to all properties.

The database context class uses lazy loading by default. There are two ways to turn off lazy loading:

• When declaring the navigation properties, skip the virtual modifier.
• For all navigation properties, specify LazyLoadingEnabled as false.

Lazy loading can hide code that causes performance problems. For example, code that does not use eager or explicit loading but uses a large number of entities and several navigation properties in a loop may be very inefficient due to the large number of database calls, but everything will be fine if lazy loading is used. Temporarily disabling lazy loading is one of the ways to find a code that is oriented towards using lazy loading. In this, the navigation properties will be Null and an error will be triggered.

Creating the Courses Index page

Entity Course has a navigation property that contains the entity Department - the entity of the faculty that owns the course. To display the name of the faculty, you need to refer to the Name property of the corresponding entity.

Create a controller of type Course:



In Controllers \ CourseController.cs, notice the Index method:

public ViewResult Index() { var courses = db.Courses.Include(c => c.Department); return View(courses.ToList()); } 

Automatic scaffolding determines the use of the eager loading for the Department navigation property using the Include method.

In Views \ Course \ Index.cshtml, replace the code with:

 @model IEnumerable<ContosoUniversity.Models.Course> @{ ViewBag.Title = "Courses"; } <h2>Courses</h2> <p> @Html.ActionLink("Create New", "Create") </p> <table> <tr> <th></th> <th>Number</th> <th>Title</th> <th>Credits</th> <th>Department</th> </tr> @foreach (var item in Model) { <tr> <td> @Html.ActionLink("Edit", "Edit", new { id=item.CourseID }) | @Html.ActionLink("Details", "Details", new { id=item.CourseID }) | @Html.ActionLink("Delete", "Delete", new { id=item.CourseID }) </td> <td> @Html.DisplayFor(modelItem => item.CourseID) </td> <td> @Html.DisplayFor(modelItem => item.Title) </td> <td> @Html.DisplayFor(modelItem => item.Credits) </td> <td> @Html.DisplayFor(modelItem => item.Department.Name) </td> </tr> } </table> 

The following changes have been made:

• Title changed to Courses .
• Lines are aligned to the left.
• Added a column under the Number heading that displays the value of the CourseID property. (Primary keys are usually not included in the page, since they do not make sense, but in this case the meaning is meaningful and needs to be shown.
• The heading of the last column is changed to Department .

Notice that for the last column, the value of the Name property of the Department from the Department of the navigation property is displayed:

 <td> @Html.DisplayFor(modelItem => item.Department.Name) </td> 

Select Courses to see the list with the names of the faculties.



Creating the Instructors Index Page with a list of courses and students of these courses

You will create a controller and view for the Instructor entity to display the Instructors Index page:



Loading and display of data occurs in the following scenario:

• The teacher list displays the corresponding OfficeAssignment entity data. The Instructor and OfficeAssignment entities are one-to-zero-or-to-one. For the OfficeAssignment entity, eager loading is used.
• When a user selects a teacher, the associated entities of the Course are displayed. The entities Instructor and Course are connected many-to-many. You will use eager loading for Course Entities and the associated Department entities. In this case, lazy loading will be more efficient because it is necessary to upload data about courses only for the selected teacher. However, the example shows the use of eager loading.
• When the user selects the course, the Enrollments entity data is displayed. Entities Course and Enrollment are one-to-many. You add explicit loading for the Enrollment entity and its associated Student entities. (With lazy loading enabled, using Explicit loading is optional, but we'll just show how explicit loading works.)

Creating a view model for the Instructor Index view

The Instructor Index page displays three different tables. To do this, we will create a view model that includes three properties, each of which contains data for each of the tables.

In the ViewModels folder, create InstructorIndexData.cs :

 using System; using System.Collections.Generic; using ContosoUniversity.Models; namespace ContosoUniversity.ViewModels { public class InstructorIndexData { public IEnumerable<Instructor> Instructors { get; set; } public IEnumerable<Course> Courses { get; set; } public IEnumerable<Enrollment> Enrollments { get; set; } } } 

Styles for selected columns

Selected columns require a separate background color. To do this, add the following code to Content \ Site . css :

 /* MISC ----------------------------------------------------------*/ .selectedrow { background-color: #EEEEEE; } 

Creating a controller and views for the Instructor

Create an Instructor type controller:



In Controllers \ InstructorController.cs, add using for ViewModels:

using ContosoUniversity.ViewModels;

The generated code in the Index method defines the use of eager loading only for the OfficeAssignment navigation property:

 public ViewResult Index() { var instructors = db.Instructors.Include(i => i.OfficeAssignment); return View(instructors.ToList()); } 

Replace the Index method with the following code that loads additional data and puts it into the view model:

 public ActionResult Index(Int32? id, Int32? courseID) { var viewModel = new InstructorIndexData(); viewModel.Instructors = db.Instructors .Include(i => i.OfficeAssignment) .Include(i => i.Courses.Select(c => c.Department)) .OrderBy(i => i.LastName); if (id != null) { ViewBag.InstructorID = id.Value; viewModel.Courses = viewModel.Instructors.Where(i => i.InstructorID == id.Value).Single().Courses; } if (courseID != null) { ViewBag.CourseID = courseID.Value; viewModel.Enrollments = viewModel.Courses.Where(x => x.CourseID == courseID).Single().Enrollments; } return View(viewModel); } 

The method accepts optional string parameters: ID values ​​of the selected teacher and course, and then passes the necessary data to the view. IDs come from the Select links on the page.

The code starts by creating an instance of the representation model and transferring the list of teachers to it:

 var viewModel = new InstructorIndexData(); viewModel.Instructors = db.Instructors .Include(i => i.OfficeAssignment); .Include(i => i.Courses.Select(c => c.Department)) .OrderBy(i => i.LastName); 

We define an eager loading for Instructor.OfficeAssignment and Instructor.Courses navigation property. For related entities, the Course eager loading is defined for the Course.Department navigation property using the Select method in the Include method. Results are sorted by last name.

If a teacher is selected, this teacher is retrieved from the list of teachers in the data model, after which the Courses property is initialized by the Course entities from the corresponding teacher in the Courses navigation property.

 if (id != null) { ViewBag.InstructorID = id.Value; viewModel.Courses = viewModel.Instructors.Where(i => i.InstructorID == id.Value).Single().Courses; } 

The Where method returns a collection, but in this case the condition passed to the method indicates to return only one Instructor entity. The Single method converts the collection into one Instructor entity, which allows you to access the Courses property corresponding to this entity.

The Single method is used on the collection if it is known that the collection will consist of one element. This method throws exceptions if the collection is empty or consists of more than one element. However, in this case, an exception will be thrown (due to the Course property with a null reference). By calling Single instead of a separate call to Where, you can pass the condition itself:

.Single (i => i.InstructorID == id.Value)

Instead:

.Where (I => i.InstructorID == id.Value). Single ()

Further, if a course is selected, then this course is extracted from the list of courses in the model. The Enrollments model property is then initialized by the entities Enrollments navigation property.

 if (courseID != null) { ViewBag.CourseID = courseID.Value; viewModel.Enrollments = viewModel.Courses.Where(x => x.CourseID == courseID).Single().Enrollments; } 

And finally, the return to the model:

return View (viewModel);

Editing the Instructor Index view

In Views \ Instructor \ Index.cshtml, replace the code with:

 @model ContosoUniversity.ViewModels.InstructorIndexData @{ ViewBag.Title = "Instructors"; } <h2>Instructors</h2> <p> @Html.ActionLink("Create New", "Create") </p> <table> <tr> <th></th> <th>Last Name</th> <th>First Name</th> <th>Hire Date</th> <th>Office</th> </tr> @foreach (var item in Model.Instructors) { string selectedRow = ""; if (item.InstructorID == ViewBag.InstructorID) { selectedRow = "selectedrow"; } <tr class="@selectedRow" valign="top"> <td> @Html.ActionLink("Select", "Index", new { id = item.InstructorID }) | @Html.ActionLink("Edit", "Edit", new { id = item.InstructorID }) | @Html.ActionLink("Details", "Details", new { id = item.InstructorID }) | @Html.ActionLink("Delete", "Delete", new { id = item.InstructorID }) </td> <td> @item.LastName </td> <td> @item.FirstMidName </td> <td> @String.Format("{0:d}", item.HireDate) </td> <td> @if (item.OfficeAssignment != null) { @item.OfficeAssignment.Location } </td> </tr> } </table> 

Changes:

• The title of the page is changed to Instructors .
• Moved the row link to the left.
• Removed column FullName .
• An Office column has been added that displays item.OfficeAssignment.Location in the event that item.OfficeAssignment is not null. (Due to the one-to-zero-or-one relationship, no OfficeAssignment entity may be associated with an entity.)

 <td> @if (item.OfficeAssignment != null) { @item.OfficeAssignment.Location } </td> 

• Added code dynamically adding class = "selectedrow" to the tr container of the selected instructor. So we set the background color using the CSS class. (the valign attribute will be useful in the future when we add a multi-line column to the table)

 string selectedRow = ""; if (item.InstructorID == ViewBag.InstructorID) { selectedRow = "selectedrow"; } <tr class="@selectedRow" valign="top"> 

• Before the links, a new ActionLink Select was added to each row, which allows you to transfer the ID of the selected teacher to the Index method.

Run the project to see the list of teachers. The page displays the Location property associated with the OfficeAssignment entities and an empty cell if there are no related OfficeAssignment entities.



in Views \ Instructors \ Index . cshtml after the container table add the code that displays the list of courses of the selected teacher.

 @if (Model.Courses != null) { <h3>Courses Taught by Selected Instructor</h3> <table> <tr> <th></th> <th>ID</th> <th>Title</th> <th>Department</th> </tr> @foreach (var item in Model.Courses) { string selectedRow = ""; if (item.CourseID == ViewBag.CourseID) { selectedRow = "selectedrow"; } <tr class="@selectedRow"> <td> @Html.ActionLink("Select", "Index", new { courseID = item.CourseID }) </td> <td> @item.CourseID </td> <td> @item.Title </td> <td> @item.Department.Name </td> </tr> } </table> } 

The code loads the Courses view model property to display a list of courses, and displays a Select link, which is used to pass the selected course ID to the Index method.

Select a teacher, and you will see a table with the courses of the teacher and the departments where the courses are conducted.



Note if the selected line does not change color, refresh the page, sometimes it is needed to load the .css file.

After the code that you added, add a code that displays a list of students studying at the selected course.

 @if (Model.Enrollments != null) { <h3> Students Enrolled in Selected Course</h3> <table> <tr> <th>Name</th> <th>Grade</th> </tr> @foreach (var item in Model.Enrollments) { <tr> <td> @item.Student.FullName </td> <td> @item.Grade </td> </tr> } </table> } 

The code loads the Enrollments view model property to display a list of students on the selected student course.

Select a teacher and click on the course to see students and their grades.



Add Explicit Loading

In InstructorController . cs and take a look at how the Index method loads the list of students for the selected course:

 if (courseID != null) { ViewBag.CourseID = courseID.Value; viewModel.Enrollments = viewModel.Courses.Where(x => x.CourseID == courseID).Single().Enrollments; } 

While loading the teacher list, you defined the eager loading for the Courses navigation property and the properties of each of the Department’s courses, then sent the Courses collection to the view model, and now you load the Enrollments navigation property from one of the entities in this collection. Due to the fact that you have not defined the eager loading for Course.Enrollments navigation property, the data of this property appears on the page as a result of lazy loading.

If you disable lazy loading, the Enrollments property will be null no matter how many students learn in this course. In this case, to initialize the Enrollments property, you must define for it an eager loading or explicit loading. To determine explicit loading, replace the Index method code with:

 public ActionResult Index(Int32? id, Int32? courseID) { var viewModel = new InstructorIndexData(); viewModel.Instructors = db.Instructors .Include(i => i.OfficeAssignment) .Include(i => i.Courses.Select(c => c.Department)) .OrderBy(i => i.LastName); if (id != null) { ViewBag.InstructorID = id.Value; viewModel.Courses = viewModel.Instructors.Where(i => i.InstructorID == id.Value).Single().Courses; } if (courseID != null) { ViewBag.CourseID = courseID.Value; var selectedCourse = viewModel.Courses.Where(x => x.CourseID == courseID).Single(); db.Entry(selectedCourse).Collection(x => x.Enrollments).Load(); foreach (Enrollment enrollment in selectedCourse.Enrollments) { db.Entry(enrollment).Reference(x => x.Student).Load(); } viewModel.Enrollments = viewModel.Courses.Where(x => x.CourseID == courseID).Single().Enrollments; } return View(viewModel); } 

After loading the selected Course entity, the new code explicitly loads the Enrollments properties:

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

Then the Student entities are explicitly loaded:

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

Note that you use the Collection method to initialize the collection property, but for a single-element property you use the Reference method. Now you can open the Instructor Index page - nothing has changed in appearance, but the principle of data loading has changed.

So, you used all three methods of data loading in navigation properties. In the next lesson, you will learn how to update related data.

Acknowledgments

Thanks for the help in the translation of Alexander Belotserkovsky ( ahriman ).