1 7_Reading Related Data with the Entity Framework in an ASP.NET MVC Application Mon Jul 06, 2015 8:10 am
Admin
Admin
The following illustrations show the pages that you'll work with.
Lazy, Eager, and Explicit Loading of Related Data
There are several ways that the Entity Framework can load related data into the navigation properties of an entity:
- Lazy loading. When the entity is first read, related data isn't retrieved. However, the first time you attempt to access a navigation property, the data required for that navigation property is automatically retrieved. This results in multiple queries sent to the database — one for the entity itself and one each time that related data for the entity must be retrieved. The
- Code:
DbContext
- Eager loading. When the entity is read, related data is retrieved along with it. This typically results in a single join query that retrieves all of the data that's needed. You specify eager loading by using the
- Code:
Include
- Explicit loading. This is similar to lazy loading, except that you explicitly retrieve the related data in code; it doesn't happen automatically when you access a navigation property. You load related data manually by getting the object state manager entry for an entity and calling the Collection.Load method for collections or the Reference.Load method for properties that hold a single entity. (In the following example, if you wanted to load the Administrator navigation property, you'd replace
- Code:
Collection(x => x.Courses)
- Code:
Reference(x => x.Administrator)
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:
- For specific navigation properties, omit the
- Code:
virtual
- For all navigation properties, set
- Code:
LazyLoadingEnabled
- Code:
false
this.Configuration.LazyLoadingEnabled = false;
Create a Courses Page That Displays Department Name
The
- Code:
Course
- Code:
Department
- Code:
Name
- Code:
Department
- Code:
Course.Department
Create a controller named
- Code:
CourseController
- Code:
Course
- Code:
Student
Open Controllers\CourseController.cs and look at the
- Code:
Index
public ActionResult Index()
{
var courses = db.Courses.Include(c => c.Department);
return View(courses.ToList());
}
The automatic scaffolding has specified eager loading for the
- Code:
Department
- Code:
Include
Open Views\Course\Index.cshtml and replace the template code with the following code. The changes are highlighted:
@model IEnumerable
@{
ViewBag.Title = "Courses";
}
Courses
@Html.ActionLink("Create New", "Create")
@Html.DisplayNameFor(model => model.CourseID) | @Html.DisplayNameFor(model => model.Title) | @Html.DisplayNameFor(model => model.Credits) | Department | |
---|---|---|---|---|
@Html.DisplayFor(modelItem => item.CourseID) | @Html.DisplayFor(modelItem => item.Title) | @Html.DisplayFor(modelItem => item.Credits) | @Html.DisplayFor(modelItem => item.Department.Name) | @Html.ActionLink("Edit", "Edit", new { id=item.CourseID }) | @Html.ActionLink("Details", "Details", new { id=item.CourseID }) | @Html.ActionLink("Delete", "Delete", new { id=item.CourseID }) |
You've made the following changes to the scaffolded code:
- Changed the heading from Index to Courses.
- Added a Number column that shows the
- Code:
CourseID
- Moved the Department column to the right side and changed its heading. The scaffolder correctly chose to display the
- Code:
Name
- Code:
Department
Notice that for the Department column, the scaffolded code displays the
- Code:
Name
- Code:
Department
- Code:
Department
@Html.DisplayFor(modelItem => item.Department.Name)
Run the page (select the Courses tab on the Contoso University home page) to see the list with department names.
Create an Instructors Page That Shows Courses and Enrollments
In this section you'll create a controller and view for the
- Code:
Instructor
This page reads and displays related data in the following ways:
- The list of instructors displays related data from the
- Code:
OfficeAssignment
- Code:
Instructor
- Code:
OfficeAssignment
- Code:
OfficeAssignment
- When the user selects an instructor, related
- Code:
Course
- Code:
Instructor
- Code:
Course
- Code:
Course
- Code:
Department
- When the user selects a course, related data from the
- Code:
Enrollments
- Code:
Course
- Code:
Enrollment
- Code:
Enrollment
- Code:
Student
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:
using System.Collections.Generic;
using ContosoUniversity.Models;
namespace ContosoUniversity.ViewModels
{
public class InstructorIndexData
{
public IEnumerable
public IEnumerable
public IEnumerable
}
}
Create the Instructor Controller and Views
Create an
- Code:
InstructorController
Open Controllers\InstructorController.cs and add a
- Code:
using
- Code:
ViewModels
using ContosoUniversity.ViewModels;
The scaffolded code in the
- Code:
Index
- Code:
OfficeAssignment
public ActionResult Index()
{
var instructors = db.Instructors.Include(i => i.OfficeAssignment);
return View(instructors.ToList());
}
Replace the
- Code:
Index
public ActionResult Index(int? id, int? 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.ID == 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 route data (
- Code:
id
- Code:
courseID
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
- Code:
Instructor.OfficeAssignment
- Code:
Instructor.Courses
var viewModel = new InstructorIndexData();
viewModel.Instructors = db.Instructors
.Include(i => i.OfficeAssignment)
.Include(i => i.Courses.Select(c => c.Department))
.OrderBy(i => i.LastName);
The second
- Code:
Include
- Code:
Course.Department
.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
- Code:
OfficeAssignment
- Code:
Course
If an instructor ID was selected, the selected instructor is retrieved from the list of instructors in the view model. The view model's
- Code:
Courses
- Code:
Course
- Code:
Courses
if (id != null)
{
ViewBag.InstructorID = id.Value;
viewModel.Courses = viewModel.Instructors.Where(i => i.ID == id.Value).Single().Courses;
}
The
- Code:
Where
- Code:
Instructor
- Code:
Single
- Code:
Instructor
- Code:
Courses
You use the Single method on a collection when you know the collection will have only one item. The
- Code:
Single
- Code:
null
- Code:
Courses
- Code:
null
- Code:
Single
- Code:
Where
- Code:
Where
.Single(i => i.ID == id.Value)
Instead of:
.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
- Code:
Enrollments
- Code:
Enrollment
- Code:
Enrollments
if (courseID != null)
{
ViewBag.CourseID = courseID.Value;
viewModel.Enrollments = viewModel.Courses.Where(
x => x.CourseID == courseID).Single().Enrollments;
}
Modify the Instructor Index View
In Views\Instructor\Index.cshtml, replace the template code with the following code. The changes are highlighted:
@model ContosoUniversity.ViewModels.InstructorIndexData
@{
ViewBag.Title = "Instructors";
}
Instructors
@Html.ActionLink("Create New", "Create")
Last Name | First Name | Hire Date | Office | |
---|---|---|---|---|
@Html.DisplayFor(modelItem => item.LastName) | @Html.DisplayFor(modelItem => item.FirstMidName) | @Html.DisplayFor(modelItem => item.HireDate) | @if (item.OfficeAssignment != null) { @item.OfficeAssignment.Location } | @Html.ActionLink("Select", "Index", new { id = item.ID }) | @Html.ActionLink("Edit", "Edit", new { id = item.ID }) | @Html.ActionLink("Details", "Details", new { id = item.ID }) | @Html.ActionLink("Delete", "Delete", new { id = item.ID }) |
You've made the following changes to the existing code:
- Changed the model class to
- Code:
InstructorIndexData
- Changed the page title from Index to Instructors.
- Added an Office column that displays
- Code:
item.OfficeAssignment.Location
- Code:
item.OfficeAssignment
- Code:
OfficeAssignment
@if (item.OfficeAssignment != null)
{
@item.OfficeAssignment.Location
} - Added code that will dynamically add
- Code:
class="success"
- Code:
tr
string selectedRow = "";
if (item.InstructorID == ViewBag.InstructorID)
{
selectedRow = "success";
}- Added a new
- Code:
ActionLink
- Code:
Index
Run the application and select the Instructors tab. The page displays the
- Code:
Location
- Code:
OfficeAssignment
- Code:
OfficeAssignment
In the Views\Instructor\Index.cshtml file, after the closing
- Code:
table
@if (Model.Courses != null)
{
Courses Taught by Selected Instructor
}
Number
Title
Department
@foreach (var item in Model.Courses)
{
string selectedRow = "";
if (item.CourseID == ViewBag.CourseID)
{
selectedRow = "success";
}
@Html.ActionLink("Select", "Index", new { courseID = item.CourseID })
@item.CourseID
@item.Title
@item.Department.Name
}
This code reads the
- Code:
Courses
- Code:
Select
- Code:
Index
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.
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.
@if (Model.Enrollments != null)
{
Students Enrolled in Selected Course
}
Name
Grade
@foreach (var item in Model.Enrollments)
{
@item.Student.FullName
@Html.DisplayFor(modelItem => item.Grade)
}
This code reads the
- Code:
Enrollments
Run the page and select an instructor. Then select a course to see the list of enrolled students and their grades.
Adding Explicit Loading
Open InstructorController.cs and look at how the
- Code:
Index
if (courseID != null)
{
ViewBag.CourseID = courseID.Value;
viewModel.Enrollments = viewModel.Courses.Where(
x => x.CourseID == courseID).Single().Enrollments;
}
When you retrieved the list of instructors, you specified eager loading for the
- Code:
Courses
- Code:
Department
- Code:
Courses
- Code:
Enrollments
- Code:
Course.Enrollments
If you disabled lazy loading without changing the code in any other way, the
- Code:
Enrollments
- Code:
Enrollments
- Code:
Index
- Code:
Enrollments
public ActionResult Index(int? id, int? 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.ID == id.Value).Single().Courses;
}
if (courseID != null)
{
ViewBag.CourseID = courseID.Value;
// Lazy loading
http://viewModel.Enrollments = viewModel.Courses.Where(
// x => x.CourseID == courseID).Single().Enrollments;
// Explicit loading
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 = selectedCourse.Enrollments;
}
return View(viewModel);
}
After getting the selected
- Code:
Course
- Code:
Enrollments
db.Entry(selectedCourse).Collection(x => x.Enrollments).Load();
Then it explicitly loads each
- Code:
Enrollment
- Code:
Student
db.Entry(enrollment).Reference(x => x.Student).Load();
Notice that you use the
- Code:
Collection
- Code:
Reference
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.Khoanglang89 » Học tập » Lập trình » Asp/ Asp.Net » Entity FrameWork » 7_Reading Related Data with the Entity Framework in an ASP.NET MVC Application
Bài viết mới cùng chuyên mục
Bài viết liên quan với7_Reading Related Data with the Entity Framework in an ASP.NET MVC Application
Permissions in this forum:
Bạn không có quyền trả lời bài viết