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

This is an extension to the Entity Framework and ASP.NET MVC 3 development article series. You can find the first chapters at the following links:

• Creating an Entity Framework Data Model for an ASP.NET MVC Application
• Implement basic CRUD functionality with Entity Framework in an ASP.NET MVC application
• Sort, filter, and paginate with 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 work on the School data model. In this lesson, we will load and display the necessary data loaded by the Entity Framework in navigation properties.

The illustration shows the results of our work.

Lazy, Eager, and Explicit loading required data

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

• Lazy loading . At the first call to the entity, the corresponding related data is not loaded. However, the first time you access the navigation property, the associated data is loaded automatically. At the same time, many queries are made to the database: one for the entity and one each time the data is downloaded.

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

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

Due to the fact that data is not immediately loaded, lazy loading and explicit loading are collectively called deferred loading .

In general, if you need data for each entity, the eager loading method offers the best performance because one request is usually more efficient than multiple requests for each entity. For example, imagine that at each faculty there are ten courses. The example for eager loading will be followed by one join request. Examples for lazy loading and explicit loading will be followed by eleven.

On the other hand, if navigation properties are rarely accessed or a small number of entities are used, lazy loading can be more efficient - eager loading will load more data than necessary. Normally explicit loading is only used when lazy loading is turned off. Suppose there is a situation where lazy loading can be turned off during serialization, when you are sure that you do not need all 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 navigation properties, skip specifying the virtual modifier.
• For all navigation properties, set LazyLoadingEnabled to false.

Lazy loading may 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 can 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 way to find code that focuses on using lazy loading. In this navigation properties will be Null and an error will be triggered.

Creating Courses Index Page

The essence of the Course has a navigation property that contains the essence of the Department - the essence of the faculty to which this course belongs. 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, pay attention to the Index method:

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

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

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

@model IEnumerable 
@{ 
    ViewBag.Title = "Courses"; 
} 
Courses
 
    @Html.ActionLink("Create New", "Create") 
 
 
@foreach (var item in Model) { 
     
} 
Number
Title
Credits
Department
 
            @Html.ActionLink("Edit", "Edit", new { id=item.CourseID }) | 
            @Html.ActionLink("Details", "Details", new { id=item.CourseID }) | 
            @Html.ActionLink("Delete", "Delete", new { id=item.CourseID }) 
        
 
            @Html.DisplayFor(modelItem => item.CourseID) 
        
 
            @Html.DisplayFor(modelItem => item.Title) 
        
 
            @Html.DisplayFor(modelItem => item.Credits) 
        
 
            @Html.DisplayFor(modelItem => item.Department.Name) 
        

The following changes have been made:

• Title changed to Courses .
• Lines are left aligned.
• Added a column under the Number heading that displays the value of the CourseID property. (Primary keys are usually not included in the page, because 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 .

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

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

Select Courses to see a list of faculty names.



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

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



Data is loaded and displayed in the following scenario:

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

Creating a presentation model for the Instructor Index view The Instructor Index

page displays three different tables. To do this, we will create a presentation 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 Instructors { get; set; } 
        public IEnumerable Courses { get; set; } 
        public IEnumerable 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 Instructor

Create a controller of type Instructor:



In Controllers \ InstructorController.cs add using for ViewModels:

using ContosoUniversity.ViewModels;

The generated code in the Index method determines the use of eager loading only for 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, which 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 Select links on the page.

The code begins by creating an instance of the presentation model and passing the list of teachers into 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 eager loading for Instructor.OfficeAssignment and Instructor.Courses navigation property. For related entities, Course eager loading is defined for 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 Course entities from the Courses navigation property corresponding to the teacher.

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 that only one Instructor entity is returned. 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). When calling Single, instead of a separate call 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. Then the Enrollments model property is initialized by the Enrollments navigation property entities.

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

And finally, returning to the model:

return View (viewModel);

Editing an Instructor Index view

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

@model ContosoUniversity.ViewModels.InstructorIndexData 
@{ 
    ViewBag.Title = "Instructors"; 
} 
Instructors
 
    @Html.ActionLink("Create New", "Create") 
 
  
    @foreach (var item in Model.Instructors)  
    {  
        string selectedRow = "";  
        if (item.InstructorID == ViewBag.InstructorID)  
        {  
            selectedRow = "selectedrow";  
        }  
          
    }  
Last Name
First Name
Hire Date
Office
  
                @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 })  
            
  
                @item.LastName  
            
  
                @item.FirstMidName  
            
  
                @String.Format("{0:d}", item.HireDate)  
            
  
                @if (item.OfficeAssignment != null)  
                {  
                    @item.OfficeAssignment.Location   
                }  
            

Changes:

• Page title changed to Instructors .
• Moved the row link columns to the left.
• Removed the column the FullName .
• An Office column has been added that displays item.OfficeAssignment.Location if item.OfficeAssignment is not null. (Due to the fact that there is a one-to-zero-or-one relationship, no OfficeAssignment entity can be associated with an entity.)

  
    @if (item.OfficeAssignment != null)  
    {  
        @item.OfficeAssignment.Location   
    }  
 

• Added code that dynamically adds class = "selectedrow" to the tr container of the selected teacher. This way we set the background color using the CSS class. (the valign attribute will be useful in the future when we add a multi-row column to the table)

string selectedRow = "";  
if (item.InstructorID == ViewBag.InstructorID)  
{  
    selectedRow = "selectedrow";  
}  
 

• A new ActionLink Select is added in front of the links in each line , which allows you to pass 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 OfficeAssignment entities and an empty cell if there are no OfficeAssignment related entities.



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

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

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

Choose a teacher, and you will see a table with the courses of the teacher and the faculties in which the courses are taught.



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

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

@if (Model.Enrollments != null)  
{  
    
  
        Students Enrolled in Selected Course
  
      
        @foreach (var item in Model.Enrollments)  
        {  
              
        }  
    
Name
Grade
  
                    @item.Student.FullName  
                
  
                    @item.Grade  
                
  
}

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

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



Adding Explicit Loading

To InstructorController . cs and notice how the Index method loads the list of students in the selected course:

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

When loading the list of teachers, you determined the eager loading for Courses navigation property and the properties of each Department course, then sent the Courses collection to the presentation model, and now load the Enrollments navigation property from one of the entities in this collection. Due to the fact that you did not define 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 regardless of how many students are in this course. In this case, to initialize the Enrollments property, you must define eager loading or explicit loading for it. 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 Student entities are explicitly loaded:

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

Notice that you use the Collection method to initialize the collection property, but for the one-element property, you use the Reference method. Now you can open the Instructor Index page - nothing has changed externally, but the principle of loading data has changed.

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

Acknowledgments

Thank you for your help in translating Alexander Belotserkovsky ( ahriman ).