Part 6, Razor Pages with EF Core in ASP.NET Core - Read Related Data
By Tom Dykstra, Jon P Smith, and Rick Anderson
The Contoso University web app demonstrates how to create Razor Pages web apps using EF Core and Visual Studio. For information about the tutorial series, see the first tutorial.
If you run into problems you can't solve, download the completed app and compare that code to what you created by following the tutorial.
This tutorial shows how to read and display related data. Related data is data that EF Core loads into navigation properties.
The following illustrations show the completed pages for this tutorial:
Eager, explicit, and lazy loading
There are several ways that EF Core can load related data into the navigation properties of an entity:
Eager loading. Eager loading is when a query for one type of entity also loads related entities. When an entity is read, its related data is retrieved. This typically results in a single join query that retrieves all of the data that's needed. EF Core will issue multiple queries for some types of eager loading. Issuing multiple queries can be more efficient than a large single query. Eager loading is specified with the Include and ThenInclude methods.
Eager loading sends multiple queries when a collection navigation is included:
- One query for the main query
- One query for each collection "edge" in the load tree.
Separate queries with
Load
: The data can be retrieved in separate queries, and EF Core "fixes up" the navigation properties. "Fixes up" means that EF Core automatically populates the navigation properties. Separate queries withLoad
is more like explicit loading than eager loading.Note: EF Core automatically fixes up navigation properties to any other entities that were previously loaded into the context instance. Even if the data for a navigation property is not explicitly included, the property may still be populated if some or all of the related entities were previously loaded.
Explicit loading. When the entity is first read, related data isn't retrieved. Code must be written to retrieve the related data when it's needed. Explicit loading with separate queries results in multiple queries sent to the database. With explicit loading, the code specifies the navigation properties to be loaded. Use the
Load
method to do explicit loading. For example:Lazy loading. When the entity is first read, related data isn't retrieved. The first time a navigation property is accessed, the data required for that navigation property is automatically retrieved. A query is sent to the database each time a navigation property is accessed for the first time. Lazy loading can hurt performance, for example when developers use N+1 queries. N+1 queries load a parent and enumerate through children.
Create Course pages
The Course
entity includes a navigation property that contains the related Department
entity.
To display the name of the assigned department for a course:
- Load the related
Department
entity into theCourse.Department
navigation property. - Get the name from the
Department
entity'sName
property.
Scaffold Course pages
Follow the instructions in Scaffold Student pages with the following exceptions:
- Create a Pages/Courses folder.
- Use
Course
for the model class. - Use the existing context class instead of creating a new one.
Open
Pages/Courses/Index.cshtml.cs
and examine theOnGetAsync
method. The scaffolding engine specified eager loading for theDepartment
navigation property. TheInclude
method specifies eager loading.Run the app and select the Courses link. The department column displays the
DepartmentID
, which isn't useful.
Display the department name
Update Pages/Courses/Index.cshtml.cs with the following code:
using ContosoUniversity.Models;
using Microsoft.AspNetCore.Mvc.RazorPages;
using Microsoft.EntityFrameworkCore;
using System.Collections.Generic;
using System.Threading.Tasks;
namespace ContosoUniversity.Pages.Courses
{
public class IndexModel : PageModel
{
private readonly ContosoUniversity.Data.SchoolContext _context;
public IndexModel(ContosoUniversity.Data.SchoolContext context)
{
_context = context;
}
public IList<Course> Courses { get; set; }
public async Task OnGetAsync()
{
Courses = await _context.Courses
.Include(c => c.Department)
.AsNoTracking()
.ToListAsync();
}
}
}
The preceding code changes the Course
property to Courses
and adds AsNoTracking
.
No-tracking queries are useful when the results are used in a read-only scenario. They're generally quicker to execute because there's no need to set up the change tracking information. If the entities retrieved from the database don't need to be updated, then a no-tracking query is likely to perform better than a tracking query.
In some cases a tracking query is more efficient than a no-tracking query. For more information, see Tracking vs. No-Tracking Queries.
In the preceding code, AsNoTracking
is called because the entities aren't updated in the current context.
Update Pages/Courses/Index.cshtml
with the following code.
@page
@model ContosoUniversity.Pages.Courses.IndexModel
@{
ViewData["Title"] = "Courses";
}
<h1>Courses</h1>
<p>
<a asp-page="Create">Create New</a>
</p>
<table class="table">
<thead>
<tr>
<th>
@Html.DisplayNameFor(model => model.Courses[0].CourseID)
</th>
<th>
@Html.DisplayNameFor(model => model.Courses[0].Title)
</th>
<th>
@Html.DisplayNameFor(model => model.Courses[0].Credits)
</th>
<th>
@Html.DisplayNameFor(model => model.Courses[0].Department)
</th>
<th></th>
</tr>
</thead>
<tbody>
@foreach (var item in Model.Courses)
{
<tr>
<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>
<td>
<a asp-page="./Edit" asp-route-id="@item.CourseID">Edit</a> |
<a asp-page="./Details" asp-route-id="@item.CourseID">Details</a> |
<a asp-page="./Delete" asp-route-id="@item.CourseID">Delete</a>
</td>
</tr>
}
</tbody>
</table>
The following changes have been made to the scaffolded code:
Changed the
Course
property name toCourses
.Added a Number column that shows the
CourseID
property value. By default, primary keys aren't scaffolded because normally they're meaningless to end users. However, in this case the primary key is meaningful.Changed the Department column to display the department name. The code displays the
Name
property of theDepartment
entity that's loaded into theDepartment
navigation property:@Html.DisplayFor(modelItem => item.Department.Name)
Run the app and select the Courses tab to see the list with department names.
Loading related data with Select
The OnGetAsync
method loads related data with the Include
method. The Select
method is an alternative that loads only the related data needed. For single items, like the Department.Name
it uses a SQL INNER JOIN
. For collections, it uses another database access, but so does the Include
operator on collections.
The following code loads related data with the Select
method:
public IList<CourseViewModel> CourseVM { get; set; }
public async Task OnGetAsync()
{
CourseVM = await _context.Courses
.Select(p => new CourseViewModel
{
CourseID = p.CourseID,
Title = p.Title,
Credits = p.Credits,
DepartmentName = p.Department.Name
}).ToListAsync();
}
The preceding code doesn't return any entity types, therefore no tracking is done. For more information about the EF tracking, see Tracking vs. No-Tracking Queries.
The CourseViewModel
:
public class CourseViewModel
{
public int CourseID { get; set; }
public string Title { get; set; }
public int Credits { get; set; }
public string DepartmentName { get; set; }
}
See IndexSelectModel for the complete Razor Pages.
Create Instructor pages
This section scaffolds Instructor pages and adds related Courses and Enrollments to the Instructors Index page.
This page reads and displays related data in the following ways:
- The list of instructors displays related data from the
OfficeAssignment
entity (Office in the preceding image). TheInstructor
andOfficeAssignment
entities are in a one-to-zero-or-one relationship. Eager loading is used for theOfficeAssignment
entities. Eager loading is typically more efficient when the related data needs to be displayed. In this case, office assignments for the instructors are displayed. - When the user selects an instructor, related
Course
entities are displayed. TheInstructor
andCourse
entities are in a many-to-many relationship. Eager loading is used for theCourse
entities and their relatedDepartment
entities. In this case, separate queries might be more efficient because only courses for the selected instructor are needed. This example shows how to use eager loading for navigation properties in entities that are in navigation properties. - When the user selects a course, related data from the
Enrollments
entity is displayed. In the preceding image, student name and grade are displayed. TheCourse
andEnrollment
entities are in a one-to-many relationship.
Create a view model
The instructors page shows data from three different tables. A view model is needed that includes three properties representing the three tables.
Create Models/SchoolViewModels/InstructorIndexData.cs
with the following code:
using System;
using System.Collections.Generic;
using System.Linq;
using System.Threading.Tasks;
namespace ContosoUniversity.Models.SchoolViewModels
{
public class InstructorIndexData
{
public IEnumerable<Instructor> Instructors { get; set; }
public IEnumerable<Course> Courses { get; set; }
public IEnumerable<Enrollment> Enrollments { get; set; }
}
}
Scaffold Instructor pages
Follow the instructions in Scaffold the student pages with the following exceptions:
- Create a Pages/Instructors folder.
- Use
Instructor
for the model class. - Use the existing context class instead of creating a new one.
Run the app and navigate to the Instructors page.
Update Pages/Instructors/Index.cshtml.cs
with the following code:
using ContosoUniversity.Models;
using ContosoUniversity.Models.SchoolViewModels; // Add VM
using Microsoft.AspNetCore.Mvc.RazorPages;
using Microsoft.EntityFrameworkCore;
using System.Collections.Generic;
using System.Linq;
using System.Threading.Tasks;
namespace ContosoUniversity.Pages.Instructors
{
public class IndexModel : PageModel
{
private readonly ContosoUniversity.Data.SchoolContext _context;
public IndexModel(ContosoUniversity.Data.SchoolContext context)
{
_context = context;
}
public InstructorIndexData InstructorData { get; set; }
public int InstructorID { get; set; }
public int CourseID { get; set; }
public async Task OnGetAsync(int? id, int? courseID)
{
InstructorData = new InstructorIndexData();
InstructorData.Instructors = await _context.Instructors
.Include(i => i.OfficeAssignment)
.Include(i => i.Courses)
.ThenInclude(c => c.Department)
.OrderBy(i => i.LastName)
.ToListAsync();
if (id != null)
{
InstructorID = id.Value;
Instructor instructor = InstructorData.Instructors
.Where(i => i.ID == id.Value).Single();
InstructorData.Courses = instructor.Courses;
}
if (courseID != null)
{
CourseID = courseID.Value;
IEnumerable<Enrollment> Enrollments = await _context.Enrollments
.Where(x => x.CourseID == CourseID)
.Include(i=>i.Student)
.ToListAsync();
InstructorData.Enrollments = Enrollments;
}
}
}
}
The OnGetAsync
method accepts optional route data for the ID of the selected instructor.
Examine the query in the Pages/Instructors/Index.cshtml.cs
file:
InstructorData = new InstructorIndexData();
InstructorData.Instructors = await _context.Instructors
.Include(i => i.OfficeAssignment)
.Include(i => i.Courses)
.ThenInclude(c => c.Department)
.OrderBy(i => i.LastName)
.ToListAsync();
The code specifies eager loading for the following navigation properties:
Instructor.OfficeAssignment
Instructor.Courses
Course.Department
The following code executes when an instructor is selected, that is, id != null
.
if (id != null)
{
InstructorID = id.Value;
Instructor instructor = InstructorData.Instructors
.Where(i => i.ID == id.Value).Single();
InstructorData.Courses = instructor.Courses;
}
The selected instructor is retrieved from the list of instructors in the view model. The view model's Courses
property is loaded with the Course
entities from the selected instructor's Courses
navigation property.
The Where
method returns a collection. In this case, the filter select a single entity, so the Single
method is called to convert the collection into a single Instructor
entity. The Instructor
entity provides access to the Course
navigation property.
The Single method is used on a collection when the collection has only one item. The Single
method throws an exception if the collection is empty or if there's more than one item. An alternative is SingleOrDefault, which returns a default value if the collection is empty. For this query, null
in the default returned.
The following code populates the view model's Enrollments
property when a course is selected:
if (courseID != null)
{
CourseID = courseID.Value;
IEnumerable<Enrollment> Enrollments = await _context.Enrollments
.Where(x => x.CourseID == CourseID)
.Include(i=>i.Student)
.ToListAsync();
InstructorData.Enrollments = Enrollments;
}
Update the instructors Index page
Update Pages/Instructors/Index.cshtml
with the following code.
@page "{id:int?}"
@model ContosoUniversity.Pages.Instructors.IndexModel
@{
ViewData["Title"] = "Instructors";
}
<h2>Instructors</h2>
<p>
<a asp-page="Create">Create New</a>
</p>
<table class="table">
<thead>
<tr>
<th>Last Name</th>
<th>First Name</th>
<th>Hire Date</th>
<th>Office</th>
<th>Courses</th>
<th></th>
</tr>
</thead>
<tbody>
@foreach (var item in Model.InstructorData.Instructors)
{
string selectedRow = "";
if (item.ID == Model.InstructorID)
{
selectedRow = "table-success";
}
<tr class="@selectedRow">
<td>
@Html.DisplayFor(modelItem => item.LastName)
</td>
<td>
@Html.DisplayFor(modelItem => item.FirstMidName)
</td>
<td>
@Html.DisplayFor(modelItem => item.HireDate)
</td>
<td>
@if (item.OfficeAssignment != null)
{
@item.OfficeAssignment.Location
}
</td>
<td>
@{
foreach (var course in item.Courses)
{
@course.CourseID @: @course.Title <br />
}
}
</td>
<td>
<a asp-page="./Index" asp-route-id="@item.ID">Select</a> |
<a asp-page="./Edit" asp-route-id="@item.ID">Edit</a> |
<a asp-page="./Details" asp-route-id="@item.ID">Details</a> |
<a asp-page="./Delete" asp-route-id="@item.ID">Delete</a>
</td>
</tr>
}
</tbody>
</table>
@if (Model.InstructorData.Courses != null)
{
<h3>Courses Taught by Selected Instructor</h3>
<table class="table">
<tr>
<th></th>
<th>Number</th>
<th>Title</th>
<th>Department</th>
</tr>
@foreach (var item in Model.InstructorData.Courses)
{
string selectedRow = "";
if (item.CourseID == Model.CourseID)
{
selectedRow = "table-success";
}
<tr class="@selectedRow">
<td>
<a asp-page="./Index" asp-route-courseID="@item.CourseID">Select</a>
</td>
<td>
@item.CourseID
</td>
<td>
@item.Title
</td>
<td>
@item.Department.Name
</td>
</tr>
}
</table>
}
@if (Model.InstructorData.Enrollments != null)
{
<h3>
Students Enrolled in Selected Course
</h3>
<table class="table">
<tr>
<th>Name</th>
<th>Grade</th>
</tr>
@foreach (var item in Model.InstructorData.Enrollments)
{
<tr>
<td>
@item.Student.FullName
</td>
<td>
@Html.DisplayFor(modelItem => item.Grade)
</td>
</tr>
}
</table>
}
The preceding code makes the following changes:
Updates the
page
directive to@page "{id:int?}"
."{id:int?}"
is a route template. The route template changes integer query strings in the URL to route data. For example, clicking on the Select link for an instructor with only the@page
directive produces a URL like the following:https://localhost:5001/Instructors?id=2
When the page directive is
@page "{id:int?}"
, the URL is:https://localhost:5001/Instructors/2
Adds an Office column that displays
item.OfficeAssignment.Location
only ifitem.OfficeAssignment
isn't null. Because this is a one-to-zero-or-one relationship, there might not be a related OfficeAssignment entity.@if (item.OfficeAssignment != null) { @item.OfficeAssignment.Location }
Adds a Courses column that displays courses taught by each instructor. See Explicit line transition for more about this razor syntax.
Adds code that dynamically adds
class="table-success"
to thetr
element of the selected instructor and course. This sets a background color for the selected row using a Bootstrap class.string selectedRow = ""; if (item.CourseID == Model.CourseID) { selectedRow = "table-success"; } <tr class="@selectedRow">
Adds a new hyperlink labeled Select. This link sends the selected instructor's ID to the
Index
method and sets a background color.<a asp-action="Index" asp-route-id="@item.ID">Select</a> |
Adds a table of courses for the selected Instructor.
Adds a table of student enrollments for the selected course.
Run the app and select the Instructors tab. The page displays the Location
(office) from the related OfficeAssignment
entity. If OfficeAssignment
is null, an empty table cell is displayed.
Click on the Select link for an instructor. The row style changes and courses assigned to that instructor are displayed.
Select a course to see the list of enrolled students and their grades.
Next steps
The next tutorial shows how to update related data.
This tutorial shows how to read and display related data. Related data is data that EF Core loads into navigation properties.
The following illustrations show the completed pages for this tutorial:
Eager, explicit, and lazy loading
There are several ways that EF Core can load related data into the navigation properties of an entity:
Eager loading. Eager loading is when a query for one type of entity also loads related entities. When an entity is read, its related data is retrieved. This typically results in a single join query that retrieves all of the data that's needed. EF Core will issue multiple queries for some types of eager loading. Issuing multiple queries can be more efficient than a giant single query. Eager loading is specified with the
Include
andThenInclude
methods.Eager loading sends multiple queries when a collection navigation is included:
- One query for the main query
- One query for each collection "edge" in the load tree.
Separate queries with
Load
: The data can be retrieved in separate queries, and EF Core "fixes up" the navigation properties. "Fixes up" means that EF Core automatically populates the navigation properties. Separate queries withLoad
is more like explicit loading than eager loading.Note: EF Core automatically fixes up navigation properties to any other entities that were previously loaded into the context instance. Even if the data for a navigation property is not explicitly included, the property may still be populated if some or all of the related entities were previously loaded.
Explicit loading. When the entity is first read, related data isn't retrieved. Code must be written to retrieve the related data when it's needed. Explicit loading with separate queries results in multiple queries sent to the database. With explicit loading, the code specifies the navigation properties to be loaded. Use the
Load
method to do explicit loading. For example:Lazy loading. When the entity is first read, related data isn't retrieved. The first time a navigation property is accessed, the data required for that navigation property is automatically retrieved. A query is sent to the database each time a navigation property is accessed for the first time. Lazy loading can hurt performance, for example when developers use N+1 patterns, loading a parent and enumerating through children.
Create Course pages
The Course
entity includes a navigation property that contains the related Department
entity.
To display the name of the assigned department for a course:
- Load the related
Department
entity into theCourse.Department
navigation property. - Get the name from the
Department
entity'sName
property.
Scaffold Course pages
Follow the instructions in Scaffold Student pages with the following exceptions:
- Create a Pages/Courses folder.
- Use
Course
for the model class. - Use the existing context class instead of creating a new one.
Open
Pages/Courses/Index.cshtml.cs
and examine theOnGetAsync
method. The scaffolding engine specified eager loading for theDepartment
navigation property. TheInclude
method specifies eager loading.Run the app and select the Courses link. The department column displays the
DepartmentID
, which isn't useful.
Display the department name
Update Pages/Courses/Index.cshtml.cs with the following code:
using ContosoUniversity.Models;
using Microsoft.AspNetCore.Mvc.RazorPages;
using Microsoft.EntityFrameworkCore;
using System.Collections.Generic;
using System.Threading.Tasks;
namespace ContosoUniversity.Pages.Courses
{
public class IndexModel : PageModel
{
private readonly ContosoUniversity.Data.SchoolContext _context;
public IndexModel(ContosoUniversity.Data.SchoolContext context)
{
_context = context;
}
public IList<Course> Courses { get; set; }
public async Task OnGetAsync()
{
Courses = await _context.Courses
.Include(c => c.Department)
.AsNoTracking()
.ToListAsync();
}
}
}
The preceding code changes the Course
property to Courses
and adds AsNoTracking
. AsNoTracking
improves performance because the entities returned are not tracked. The entities don't need to be tracked because they're not updated in the current context.
Update Pages/Courses/Index.cshtml
with the following code.
@page
@model ContosoUniversity.Pages.Courses.IndexModel
@{
ViewData["Title"] = "Courses";
}
<h1>Courses</h1>
<p>
<a asp-page="Create">Create New</a>
</p>
<table class="table">
<thead>
<tr>
<th>
@Html.DisplayNameFor(model => model.Courses[0].CourseID)
</th>
<th>
@Html.DisplayNameFor(model => model.Courses[0].Title)
</th>
<th>
@Html.DisplayNameFor(model => model.Courses[0].Credits)
</th>
<th>
@Html.DisplayNameFor(model => model.Courses[0].Department)
</th>
<th></th>
</tr>
</thead>
<tbody>
@foreach (var item in Model.Courses)
{
<tr>
<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>
<td>
<a asp-page="./Edit" asp-route-id="@item.CourseID">Edit</a> |
<a asp-page="./Details" asp-route-id="@item.CourseID">Details</a> |
<a asp-page="./Delete" asp-route-id="@item.CourseID">Delete</a>
</td>
</tr>
}
</tbody>
</table>
The following changes have been made to the scaffolded code:
Changed the
Course
property name toCourses
.Added a Number column that shows the
CourseID
property value. By default, primary keys aren't scaffolded because normally they're meaningless to end users. However, in this case the primary key is meaningful.Changed the Department column to display the department name. The code displays the
Name
property of theDepartment
entity that's loaded into theDepartment
navigation property:@Html.DisplayFor(modelItem => item.Department.Name)
Run the app and select the Courses tab to see the list with department names.
Loading related data with Select
The OnGetAsync
method loads related data with the Include
method. The Select
method is an alternative that loads only the related data needed. For single items, like the Department.Name
it uses a SQL INNER JOIN. For collections, it uses another database access, but so does the Include
operator on collections.
The following code loads related data with the Select
method:
public IList<CourseViewModel> CourseVM { get; set; }
public async Task OnGetAsync()
{
CourseVM = await _context.Courses
.Select(p => new CourseViewModel
{
CourseID = p.CourseID,
Title = p.Title,
Credits = p.Credits,
DepartmentName = p.Department.Name
}).ToListAsync();
}
The preceding code doesn't return any entity types, therefore no tracking is done. For more information about the EF tracking, see Tracking vs. No-Tracking Queries.
The CourseViewModel
:
public class CourseViewModel
{
public int CourseID { get; set; }
public string Title { get; set; }
public int Credits { get; set; }
public string DepartmentName { get; set; }
}
See IndexSelect.cshtml and IndexSelect.cshtml.cs for a complete example.
Create Instructor pages
This section scaffolds Instructor pages and adds related Courses and Enrollments to the Instructors Index page.
This page reads and displays related data in the following ways:
- The list of instructors displays related data from the
OfficeAssignment
entity (Office in the preceding image). TheInstructor
andOfficeAssignment
entities are in a one-to-zero-or-one relationship. Eager loading is used for theOfficeAssignment
entities. Eager loading is typically more efficient when the related data needs to be displayed. In this case, office assignments for the instructors are displayed. - When the user selects an instructor, related
Course
entities are displayed. TheInstructor
andCourse
entities are in a many-to-many relationship. Eager loading is used for theCourse
entities and their relatedDepartment
entities. In this case, separate queries might be more efficient because only courses for the selected instructor are needed. This example shows how to use eager loading for navigation properties in entities that are in navigation properties. - When the user selects a course, related data from the
Enrollments
entity is displayed. In the preceding image, student name and grade are displayed. TheCourse
andEnrollment
entities are in a one-to-many relationship.
Create a view model
The instructors page shows data from three different tables. A view model is needed that includes three properties representing the three tables.
Create SchoolViewModels/InstructorIndexData.cs
with the following code:
using System;
using System.Collections.Generic;
using System.Linq;
using System.Threading.Tasks;
namespace ContosoUniversity.Models.SchoolViewModels
{
public class InstructorIndexData
{
public IEnumerable<Instructor> Instructors { get; set; }
public IEnumerable<Course> Courses { get; set; }
public IEnumerable<Enrollment> Enrollments { get; set; }
}
}
Scaffold Instructor pages
Follow the instructions in Scaffold the student pages with the following exceptions:
- Create a Pages/Instructors folder.
- Use
Instructor
for the model class. - Use the existing context class instead of creating a new one.
To see what the scaffolded page looks like before you update it, run the app and navigate to the Instructors page.
Update Pages/Instructors/Index.cshtml.cs
with the following code:
using ContosoUniversity.Models;
using ContosoUniversity.Models.SchoolViewModels; // Add VM
using Microsoft.AspNetCore.Mvc.RazorPages;
using Microsoft.EntityFrameworkCore;
using System.Linq;
using System.Threading.Tasks;
namespace ContosoUniversity.Pages.Instructors
{
public class IndexModel : PageModel
{
private readonly ContosoUniversity.Data.SchoolContext _context;
public IndexModel(ContosoUniversity.Data.SchoolContext context)
{
_context = context;
}
public InstructorIndexData InstructorData { get; set; }
public int InstructorID { get; set; }
public int CourseID { get; set; }
public async Task OnGetAsync(int? id, int? courseID)
{
InstructorData = new InstructorIndexData();
InstructorData.Instructors = await _context.Instructors
.Include(i => i.OfficeAssignment)
.Include(i => i.CourseAssignments)
.ThenInclude(i => i.Course)
.ThenInclude(i => i.Department)
.Include(i => i.CourseAssignments)
.ThenInclude(i => i.Course)
.ThenInclude(i => i.Enrollments)
.ThenInclude(i => i.Student)
.AsNoTracking()
.OrderBy(i => i.LastName)
.ToListAsync();
if (id != null)
{
InstructorID = id.Value;
Instructor instructor = InstructorData.Instructors
.Where(i => i.ID == id.Value).Single();
InstructorData.Courses = instructor.CourseAssignments.Select(s => s.Course);
}
if (courseID != null)
{
CourseID = courseID.Value;
var selectedCourse = InstructorData.Courses
.Where(x => x.CourseID == courseID).Single();
InstructorData.Enrollments = selectedCourse.Enrollments;
}
}
}
}
The OnGetAsync
method accepts optional route data for the ID of the selected instructor.
Examine the query in the Pages/Instructors/Index.cshtml.cs
file:
InstructorData.Instructors = await _context.Instructors
.Include(i => i.OfficeAssignment)
.Include(i => i.CourseAssignments)
.ThenInclude(i => i.Course)
.ThenInclude(i => i.Department)
.Include(i => i.CourseAssignments)
.ThenInclude(i => i.Course)
.ThenInclude(i => i.Enrollments)
.ThenInclude(i => i.Student)
.AsNoTracking()
.OrderBy(i => i.LastName)
.ToListAsync();
The code specifies eager loading for the following navigation properties:
Instructor.OfficeAssignment
Instructor.CourseAssignments
CourseAssignments.Course
Course.Department
Course.Enrollments
Enrollment.Student
Notice the repetition of Include
and ThenInclude
methods for CourseAssignments
and Course
. This repetition is necessary to specify eager loading for two navigation properties of the Course
entity.
The following code executes when an instructor is selected (id != null
).
if (id != null)
{
InstructorID = id.Value;
Instructor instructor = InstructorData.Instructors
.Where(i => i.ID == id.Value).Single();
InstructorData.Courses = instructor.CourseAssignments.Select(s => s.Course);
}
The selected instructor is retrieved from the list of instructors in the view model. The view model's Courses
property is loaded with the Course
entities from that instructor's CourseAssignments
navigation property.
The Where
method returns a collection. But in this case, the filter will select a single entity, so the Single
method is called to convert the collection into a single Instructor
entity. The Instructor
entity provides access to the CourseAssignments
property. CourseAssignments
provides access to the related Course
entities.
The Single
method is used on a collection when the collection has only one item. The Single
method throws an exception if the collection 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.
The following code populates the view model's Enrollments
property when a course is selected:
if (courseID != null)
{
CourseID = courseID.Value;
var selectedCourse = InstructorData.Courses
.Where(x => x.CourseID == courseID).Single();
InstructorData.Enrollments = selectedCourse.Enrollments;
}
Update the instructors Index page
Update Pages/Instructors/Index.cshtml
with the following code.
@page "{id:int?}"
@model ContosoUniversity.Pages.Instructors.IndexModel
@{
ViewData["Title"] = "Instructors";
}
<h2>Instructors</h2>
<p>
<a asp-page="Create">Create New</a>
</p>
<table class="table">
<thead>
<tr>
<th>Last Name</th>
<th>First Name</th>
<th>Hire Date</th>
<th>Office</th>
<th>Courses</th>
<th></th>
</tr>
</thead>
<tbody>
@foreach (var item in Model.InstructorData.Instructors)
{
string selectedRow = "";
if (item.ID == Model.InstructorID)
{
selectedRow = "table-success";
}
<tr class="@selectedRow">
<td>
@Html.DisplayFor(modelItem => item.LastName)
</td>
<td>
@Html.DisplayFor(modelItem => item.FirstMidName)
</td>
<td>
@Html.DisplayFor(modelItem => item.HireDate)
</td>
<td>
@if (item.OfficeAssignment != null)
{
@item.OfficeAssignment.Location
}
</td>
<td>
@{
foreach (var course in item.CourseAssignments)
{
@course.Course.CourseID @: @course.Course.Title <br />
}
}
</td>
<td>
<a asp-page="./Index" asp-route-id="@item.ID">Select</a> |
<a asp-page="./Edit" asp-route-id="@item.ID">Edit</a> |
<a asp-page="./Details" asp-route-id="@item.ID">Details</a> |
<a asp-page="./Delete" asp-route-id="@item.ID">Delete</a>
</td>
</tr>
}
</tbody>
</table>
@if (Model.InstructorData.Courses != null)
{
<h3>Courses Taught by Selected Instructor</h3>
<table class="table">
<tr>
<th></th>
<th>Number</th>
<th>Title</th>
<th>Department</th>
</tr>
@foreach (var item in Model.InstructorData.Courses)
{
string selectedRow = "";
if (item.CourseID == Model.CourseID)
{
selectedRow = "table-success";
}
<tr class="@selectedRow">
<td>
<a asp-page="./Index" asp-route-courseID="@item.CourseID">Select</a>
</td>
<td>
@item.CourseID
</td>
<td>
@item.Title
</td>
<td>
@item.Department.Name
</td>
</tr>
}
</table>
}
@if (Model.InstructorData.Enrollments != null)
{
<h3>
Students Enrolled in Selected Course
</h3>
<table class="table">
<tr>
<th>Name</th>
<th>Grade</th>
</tr>
@foreach (var item in Model.InstructorData.Enrollments)
{
<tr>
<td>
@item.Student.FullName
</td>
<td>
@Html.DisplayFor(modelItem => item.Grade)
</td>
</tr>
}
</table>
}
The preceding code makes the following changes:
Updates the
page
directive from@page
to@page "{id:int?}"
."{id:int?}"
is a route template. The route template changes integer query strings in the URL to route data. For example, clicking on the Select link for an instructor with only the@page
directive produces a URL like the following:https://localhost:5001/Instructors?id=2
When the page directive is
@page "{id:int?}"
, the URL is:https://localhost:5001/Instructors/2
Adds an Office column that displays
item.OfficeAssignment.Location
only ifitem.OfficeAssignment
isn't null. Because this is a one-to-zero-or-one relationship, there might not be a related OfficeAssignment entity.@if (item.OfficeAssignment != null) { @item.OfficeAssignment.Location }
Adds a Courses column that displays courses taught by each instructor. See Explicit line transition for more about this razor syntax.
Adds code that dynamically adds
class="table-success"
to thetr
element of the selected instructor and course. This sets a background color for the selected row using a Bootstrap class.string selectedRow = ""; if (item.CourseID == Model.CourseID) { selectedRow = "table-success"; } <tr class="@selectedRow">
Adds a new hyperlink labeled Select. This link sends the selected instructor's ID to the
Index
method and sets a background color.<a asp-action="Index" asp-route-id="@item.ID">Select</a> |
Adds a table of courses for the selected Instructor.
Adds a table of student enrollments for the selected course.
Run the app and select the Instructors tab. The page displays the Location
(office) from the related OfficeAssignment
entity. If OfficeAssignment
is null, an empty table cell is displayed.
Click on the Select link for an instructor. The row style changes and courses assigned to that instructor are displayed.
Select a course to see the list of enrolled students and their grades.
Using Single
The Single
method can pass in the Where
condition instead of calling the Where
method separately:
public async Task OnGetAsync(int? id, int? courseID)
{
InstructorData = new InstructorIndexData();
InstructorData.Instructors = await _context.Instructors
.Include(i => i.OfficeAssignment)
.Include(i => i.CourseAssignments)
.ThenInclude(i => i.Course)
.ThenInclude(i => i.Department)
.Include(i => i.CourseAssignments)
.ThenInclude(i => i.Course)
.ThenInclude(i => i.Enrollments)
.ThenInclude(i => i.Student)
.AsNoTracking()
.OrderBy(i => i.LastName)
.ToListAsync();
if (id != null)
{
InstructorID = id.Value;
Instructor instructor = InstructorData.Instructors.Single(
i => i.ID == id.Value);
InstructorData.Courses = instructor.CourseAssignments.Select(
s => s.Course);
}
if (courseID != null)
{
CourseID = courseID.Value;
InstructorData.Enrollments = InstructorData.Courses.Single(
x => x.CourseID == courseID).Enrollments;
}
}
Use of Single
with a Where condition is a matter of personal preference. It provides no benefits over using the Where
method.
Explicit loading
The current code specifies eager loading for Enrollments
and Students
:
InstructorData.Instructors = await _context.Instructors
.Include(i => i.OfficeAssignment)
.Include(i => i.CourseAssignments)
.ThenInclude(i => i.Course)
.ThenInclude(i => i.Department)
.Include(i => i.CourseAssignments)
.ThenInclude(i => i.Course)
.ThenInclude(i => i.Enrollments)
.ThenInclude(i => i.Student)
.AsNoTracking()
.OrderBy(i => i.LastName)
.ToListAsync();
Suppose users rarely want to see enrollments in a course. In that case, an optimization would be to only load the enrollment data if it's requested. In this section, the OnGetAsync
is updated to use explicit loading of Enrollments
and Students
.
Update Pages/Instructors/Index.cshtml.cs
with the following code.
using ContosoUniversity.Models;
using ContosoUniversity.Models.SchoolViewModels; // Add VM
using Microsoft.AspNetCore.Mvc.RazorPages;
using Microsoft.EntityFrameworkCore;
using System.Linq;
using System.Threading.Tasks;
namespace ContosoUniversity.Pages.Instructors
{
public class IndexModel : PageModel
{
private readonly ContosoUniversity.Data.SchoolContext _context;
public IndexModel(ContosoUniversity.Data.SchoolContext context)
{
_context = context;
}
public InstructorIndexData InstructorData { get; set; }
public int InstructorID { get; set; }
public int CourseID { get; set; }
public async Task OnGetAsync(int? id, int? courseID)
{
InstructorData = new InstructorIndexData();
InstructorData.Instructors = await _context.Instructors
.Include(i => i.OfficeAssignment)
.Include(i => i.CourseAssignments)
.ThenInclude(i => i.Course)
.ThenInclude(i => i.Department)
//.Include(i => i.CourseAssignments)
// .ThenInclude(i => i.Course)
// .ThenInclude(i => i.Enrollments)
// .ThenInclude(i => i.Student)
//.AsNoTracking()
.OrderBy(i => i.LastName)
.ToListAsync();
if (id != null)
{
InstructorID = id.Value;
Instructor instructor = InstructorData.Instructors
.Where(i => i.ID == id.Value).Single();
InstructorData.Courses = instructor.CourseAssignments.Select(s => s.Course);
}
if (courseID != null)
{
CourseID = courseID.Value;
var selectedCourse = InstructorData.Courses
.Where(x => x.CourseID == courseID).Single();
await _context.Entry(selectedCourse).Collection(x => x.Enrollments).LoadAsync();
foreach (Enrollment enrollment in selectedCourse.Enrollments)
{
await _context.Entry(enrollment).Reference(x => x.Student).LoadAsync();
}
InstructorData.Enrollments = selectedCourse.Enrollments;
}
}
}
}
The preceding code drops the ThenInclude method calls for enrollment and student data. If a course is selected, the explicit loading code retrieves:
- The
Enrollment
entities for the selected course. - The
Student
entities for eachEnrollment
.
Notice that the preceding code comments out .AsNoTracking()
. Navigation properties can only be explicitly loaded for tracked entities.
Test the app. From a user's perspective, the app behaves identically to the previous version.
Next steps
The next tutorial shows how to update related data.
In this tutorial, related data is read and displayed. Related data is data that EF Core loads into navigation properties.
If you run into problems you can't solve, download or view the completed app. Download instructions.
The following illustrations show the completed pages for this tutorial:
Eager, explicit, and lazy Loading of related data
There are several ways that EF Core can load related data into the navigation properties of an entity:
Eager loading. Eager loading is when a query for one type of entity also loads related entities. When the entity is read, its related data is retrieved. This typically results in a single join query that retrieves all of the data that's needed. EF Core will issue multiple queries for some types of eager loading. Issuing multiple queries can be more efficient than was the case for some queries in EF6 where there was a single query. Eager loading is specified with the
Include
andThenInclude
methods.Eager loading sends multiple queries when a collection navigation is included:
- One query for the main query
- One query for each collection "edge" in the load tree.
Separate queries with
Load
: The data can be retrieved in separate queries, and EF Core "fixes up" the navigation properties. "fixes up" means that EF Core automatically populates the navigation properties. Separate queries withLoad
is more like explicit loading than eager loading.Note: EF Core automatically fixes up navigation properties to any other entities that were previously loaded into the context instance. Even if the data for a navigation property is not explicitly included, the property may still be populated if some or all of the related entities were previously loaded.
Explicit loading. When the entity is first read, related data isn't retrieved. Code must be written to retrieve the related data when it's needed. Explicit loading with separate queries results in multiple queries sent to the DB. With explicit loading, the code specifies the navigation properties to be loaded. Use the
Load
method to do explicit loading. For example:Lazy loading. Lazy loading was added to EF Core in version 2.1. When the entity is first read, related data isn't retrieved. The first time a navigation property is accessed, the data required for that navigation property is automatically retrieved. A query is sent to the DB each time a navigation property is accessed for the first time.
The
Select
operator loads only the related data needed.
Create a Course page that displays department name
The Course entity includes a navigation property that contains the Department
entity. The Department
entity contains the department that the course is assigned to.
To display the name of the assigned department in a list of courses:
- Get the
Name
property from theDepartment
entity. - The
Department
entity comes from theCourse.Department
navigation property.
Scaffold the Course model
Follow the instructions in Scaffold the student model and use Course
for the model class.
The preceding command scaffolds the Course
model. Open the project in Visual Studio.
Open Pages/Courses/Index.cshtml.cs
and examine the OnGetAsync
method. The scaffolding engine specified eager loading for the Department
navigation property. The Include
method specifies eager loading.
Run the app and select the Courses link. The department column displays the DepartmentID
, which isn't useful.
Update the OnGetAsync
method with the following code:
public async Task OnGetAsync()
{
Course = await _context.Courses
.Include(c => c.Department)
.AsNoTracking()
.ToListAsync();
}
The preceding code adds AsNoTracking
. AsNoTracking
improves performance because the entities returned are not tracked. The entities are not tracked because they're not updated in the current context.
Update Pages/Courses/Index.cshtml
with the following highlighted markup:
@page
@model ContosoUniversity.Pages.Courses.IndexModel
@{
ViewData["Title"] = "Courses";
}
<h2>Courses</h2>
<p>
<a asp-page="Create">Create New</a>
</p>
<table class="table">
<thead>
<tr>
<th>
@Html.DisplayNameFor(model => model.Course[0].CourseID)
</th>
<th>
@Html.DisplayNameFor(model => model.Course[0].Title)
</th>
<th>
@Html.DisplayNameFor(model => model.Course[0].Credits)
</th>
<th>
@Html.DisplayNameFor(model => model.Course[0].Department)
</th>
<th></th>
</tr>
</thead>
<tbody>
@foreach (var item in Model.Course)
{
<tr>
<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>
<td>
<a asp-page="./Edit" asp-route-id="@item.CourseID">Edit</a> |
<a asp-page="./Details" asp-route-id="@item.CourseID">Details</a> |
<a asp-page="./Delete" asp-route-id="@item.CourseID">Delete</a>
</td>
</tr>
}
</tbody>
</table>
The following changes have been made to the scaffolded code:
Changed the heading from Index to Courses.
Added a Number column that shows the
CourseID
property value. By default, primary keys aren't scaffolded because normally they're meaningless to end users. However, in this case the primary key is meaningful.Changed the Department column to display the department name. The code displays the
Name
property of theDepartment
entity that's loaded into theDepartment
navigation property:@Html.DisplayFor(modelItem => item.Department.Name)
Run the app and select the Courses tab to see the list with department names.
Loading related data with Select
The OnGetAsync
method loads related data with the Include
method:
public async Task OnGetAsync()
{
Course = await _context.Courses
.Include(c => c.Department)
.AsNoTracking()
.ToListAsync();
}
The Select
operator loads only the related data needed. For single items, like the Department.Name
it uses a SQL INNER JOIN. For collections, it uses another database access, but so does the Include
operator on collections.
The following code loads related data with the Select
method:
public IList<CourseViewModel> CourseVM { get; set; }
public async Task OnGetAsync()
{
CourseVM = await _context.Courses
.Select(p => new CourseViewModel
{
CourseID = p.CourseID,
Title = p.Title,
Credits = p.Credits,
DepartmentName = p.Department.Name
}).ToListAsync();
}
The CourseViewModel
:
public class CourseViewModel
{
public int CourseID { get; set; }
public string Title { get; set; }
public int Credits { get; set; }
public string DepartmentName { get; set; }
}
See IndexSelect.cshtml and IndexSelect.cshtml.cs for a complete example.
Create an Instructors page that shows Courses and Enrollments
In this section, the Instructors page is created.
This page reads and displays related data in the following ways:
- The list of instructors displays related data from the
OfficeAssignment
entity (Office in the preceding image). TheInstructor
andOfficeAssignment
entities are in a one-to-zero-or-one relationship. Eager loading is used for theOfficeAssignment
entities. Eager loading is typically more efficient when the related data needs to be displayed. In this case, office assignments for the instructors are displayed. - When the user selects an instructor (Harui in the preceding image), related
Course
entities are displayed. TheInstructor
andCourse
entities are in a many-to-many relationship. Eager loading is used for theCourse
entities and their relatedDepartment
entities. In this case, separate queries might be more efficient because only courses for the selected instructor are needed. This example shows how to use eager loading for navigation properties in entities that are in navigation properties. - When the user selects a course (Chemistry in the preceding image), related data from the
Enrollments
entity is displayed. In the preceding image, student name and grade are displayed. TheCourse
andEnrollment
entities are in a one-to-many relationship.
Create a view model for the Instructor Index view
The instructors page shows data from three different tables. A view model is created that includes the three entities representing the three tables.
In the SchoolViewModels folder, create InstructorIndexData.cs
with the following code:
using System;
using System.Collections.Generic;
using System.Linq;
using System.Threading.Tasks;
namespace ContosoUniversity.Models.SchoolViewModels
{
public class InstructorIndexData
{
public IEnumerable<Instructor> Instructors { get; set; }
public IEnumerable<Course> Courses { get; set; }
public IEnumerable<Enrollment> Enrollments { get; set; }
}
}
Scaffold the Instructor model
Follow the instructions in Scaffold the student model and use Instructor
for the model class.
The preceding command scaffolds the Instructor
model.
Run the app and navigate to the instructors page.
Replace Pages/Instructors/Index.cshtml.cs
with the following code:
using ContosoUniversity.Models;
using ContosoUniversity.Models.SchoolViewModels; // Add VM
using Microsoft.AspNetCore.Mvc.RazorPages;
using Microsoft.EntityFrameworkCore;
using System.Linq;
using System.Threading.Tasks;
namespace ContosoUniversity.Pages.Instructors
{
public class IndexModel : PageModel
{
private readonly ContosoUniversity.Data.SchoolContext _context;
public IndexModel(ContosoUniversity.Data.SchoolContext context)
{
_context = context;
}
public InstructorIndexData Instructor { get; set; }
public int InstructorID { get; set; }
public async Task OnGetAsync(int? id)
{
Instructor = new InstructorIndexData();
Instructor.Instructors = await _context.Instructors
.Include(i => i.OfficeAssignment)
.Include(i => i.CourseAssignments)
.ThenInclude(i => i.Course)
.AsNoTracking()
.OrderBy(i => i.LastName)
.ToListAsync();
if (id != null)
{
InstructorID = id.Value;
}
}
}
}
The OnGetAsync
method accepts optional route data for the ID of the selected instructor.
Examine the query in the Pages/Instructors/Index.cshtml.cs
file:
Instructor.Instructors = await _context.Instructors
.Include(i => i.OfficeAssignment)
.Include(i => i.CourseAssignments)
.ThenInclude(i => i.Course)
.AsNoTracking()
.OrderBy(i => i.LastName)
.ToListAsync();
The query has two includes:
OfficeAssignment
: Displayed in the instructors view.CourseAssignments
: Which brings in the courses taught.
Update the instructors Index page
Update Pages/Instructors/Index.cshtml
with the following markup:
@page "{id:int?}"
@model ContosoUniversity.Pages.Instructors.IndexModel
@{
ViewData["Title"] = "Instructors";
}
<h2>Instructors</h2>
<p>
<a asp-page="Create">Create New</a>
</p>
<table class="table">
<thead>
<tr>
<th>Last Name</th>
<th>First Name</th>
<th>Hire Date</th>
<th>Office</th>
<th>Courses</th>
<th></th>
</tr>
</thead>
<tbody>
@foreach (var item in Model.Instructor.Instructors)
{
string selectedRow = "";
if (item.ID == Model.InstructorID)
{
selectedRow = "success";
}
<tr class="@selectedRow">
<td>
@Html.DisplayFor(modelItem => item.LastName)
</td>
<td>
@Html.DisplayFor(modelItem => item.FirstMidName)
</td>
<td>
@Html.DisplayFor(modelItem => item.HireDate)
</td>
<td>
@if (item.OfficeAssignment != null)
{
@item.OfficeAssignment.Location
}
</td>
<td>
@{
foreach (var course in item.CourseAssignments)
{
@course.Course.CourseID @: @course.Course.Title <br />
}
}
</td>
<td>
<a asp-page="./Index" asp-route-id="@item.ID">Select</a> |
<a asp-page="./Edit" asp-route-id="@item.ID">Edit</a> |
<a asp-page="./Details" asp-route-id="@item.ID">Details</a> |
<a asp-page="./Delete" asp-route-id="@item.ID">Delete</a>
</td>
</tr>
}
</tbody>
</table>
The preceding markup makes the following changes:
Updates the
page
directive from@page
to@page "{id:int?}"
."{id:int?}"
is a route template. The route template changes integer query strings in the URL to route data. For example, clicking on the Select link for an instructor with only the@page
directive produces a URL like the following:http://localhost:1234/Instructors?id=2
When the page directive is
@page "{id:int?}"
, the previous URL is:http://localhost:1234/Instructors/2
Page title is Instructors.
Added an Office column that displays
item.OfficeAssignment.Location
only ifitem.OfficeAssignment
isn't null. Because this is a one-to-zero-or-one relationship, there might not be a related OfficeAssignment entity.@if (item.OfficeAssignment != null) { @item.OfficeAssignment.Location }
Added a Courses column that displays courses taught by each instructor. See Explicit line transition for more about this razor syntax.
Added code that dynamically adds
class="success"
to thetr
element of the selected instructor. This sets a background color for the selected row using a Bootstrap class.string selectedRow = ""; if (item.CourseID == Model.CourseID) { selectedRow = "success"; } <tr class="@selectedRow">
Added a new hyperlink labeled Select. This link sends the selected instructor's ID to the
Index
method and sets a background color.<a asp-action="Index" asp-route-id="@item.ID">Select</a> |
Run the app and select the Instructors tab. The page displays the Location
(office) from the related OfficeAssignment
entity. If OfficeAssignment` is null, an empty table cell is displayed.
Click on the Select link. The row style changes.
Add courses taught by selected instructor
Update the OnGetAsync
method in Pages/Instructors/Index.cshtml.cs
with the following code:
public async Task OnGetAsync(int? id, int? courseID)
{
Instructor = new InstructorIndexData();
Instructor.Instructors = await _context.Instructors
.Include(i => i.OfficeAssignment)
.Include(i => i.CourseAssignments)
.ThenInclude(i => i.Course)
.ThenInclude(i => i.Department)
.AsNoTracking()
.OrderBy(i => i.LastName)
.ToListAsync();
if (id != null)
{
InstructorID = id.Value;
Instructor instructor = Instructor.Instructors.Where(
i => i.ID == id.Value).Single();
Instructor.Courses = instructor.CourseAssignments.Select(s => s.Course);
}
if (courseID != null)
{
CourseID = courseID.Value;
Instructor.Enrollments = Instructor.Courses.Where(
x => x.CourseID == courseID).Single().Enrollments;
}
}
Add public int CourseID { get; set; }
public class IndexModel : PageModel
{
private readonly ContosoUniversity.Data.SchoolContext _context;
public IndexModel(ContosoUniversity.Data.SchoolContext context)
{
_context = context;
}
public InstructorIndexData Instructor { get; set; }
public int InstructorID { get; set; }
public int CourseID { get; set; }
public async Task OnGetAsync(int? id, int? courseID)
{
Instructor = new InstructorIndexData();
Instructor.Instructors = await _context.Instructors
.Include(i => i.OfficeAssignment)
.Include(i => i.CourseAssignments)
.ThenInclude(i => i.Course)
.ThenInclude(i => i.Department)
.AsNoTracking()
.OrderBy(i => i.LastName)
.ToListAsync();
if (id != null)
{
InstructorID = id.Value;
Instructor instructor = Instructor.Instructors.Where(
i => i.ID == id.Value).Single();
Instructor.Courses = instructor.CourseAssignments.Select(s => s.Course);
}
if (courseID != null)
{
CourseID = courseID.Value;
Instructor.Enrollments = Instructor.Courses.Where(
x => x.CourseID == courseID).Single().Enrollments;
}
}
Examine the updated query:
Instructor.Instructors = await _context.Instructors
.Include(i => i.OfficeAssignment)
.Include(i => i.CourseAssignments)
.ThenInclude(i => i.Course)
.ThenInclude(i => i.Department)
.AsNoTracking()
.OrderBy(i => i.LastName)
.ToListAsync();
The preceding query adds the Department
entities.
The following code executes when an instructor is selected (id != null
). The selected instructor is retrieved from the list of instructors in the view model. The view model's Courses
property is loaded with the Course
entities from that instructor's CourseAssignments
navigation property.
if (id != null)
{
InstructorID = id.Value;
Instructor instructor = Instructor.Instructors.Where(
i => i.ID == id.Value).Single();
Instructor.Courses = instructor.CourseAssignments.Select(s => s.Course);
}
The Where
method returns a collection. In the preceding Where
method, only a single Instructor
entity is returned. The Single
method converts the collection into a single Instructor
entity. The Instructor
entity provides access to the CourseAssignments
property. CourseAssignments
provides access to the related Course
entities.
The Single
method is used on a collection when the collection has only one item. The Single
method throws an exception if the collection 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. Using SingleOrDefault
on an empty collection:
- Results in an exception (from trying to find a
Courses
property on a null reference). - The exception message would less clearly indicate the cause of the problem.
The following code populates the view model's Enrollments
property when a course is selected:
if (courseID != null)
{
CourseID = courseID.Value;
Instructor.Enrollments = Instructor.Courses.Where(
x => x.CourseID == courseID).Single().Enrollments;
}
Add the following markup to the end of the Pages/Instructors/Index.cshtml
Razor Page:
<a asp-page="./Delete" asp-route-id="@item.ID">Delete</a>
</td>
</tr>
}
</tbody>
</table>
@if (Model.Instructor.Courses != null)
{
<h3>Courses Taught by Selected Instructor</h3>
<table class="table">
<tr>
<th></th>
<th>Number</th>
<th>Title</th>
<th>Department</th>
</tr>
@foreach (var item in Model.Instructor.Courses)
{
string selectedRow = "";
if (item.CourseID == Model.CourseID)
{
selectedRow = "success";
}
<tr class="@selectedRow">
<td>
<a asp-page="./Index" asp-route-courseID="@item.CourseID">Select</a>
</td>
<td>
@item.CourseID
</td>
<td>
@item.Title
</td>
<td>
@item.Department.Name
</td>
</tr>
}
</table>
}
The preceding markup displays a list of courses related to an instructor when an instructor is selected.
Test the app. Click on a Select link on the instructors page.
Show student data
In this section, the app is updated to show the student data for a selected course.
Update the query in the OnGetAsync
method in Pages/Instructors/Index.cshtml.cs
with the following code:
Instructor.Instructors = await _context.Instructors
.Include(i => i.OfficeAssignment)
.Include(i => i.CourseAssignments)
.ThenInclude(i => i.Course)
.ThenInclude(i => i.Department)
.Include(i => i.CourseAssignments)
.ThenInclude(i => i.Course)
.ThenInclude(i => i.Enrollments)
.ThenInclude(i => i.Student)
.AsNoTracking()
.OrderBy(i => i.LastName)
.ToListAsync();
Update Pages/Instructors/Index.cshtml
. Add the following markup to the end of the file:
@if (Model.Instructor.Enrollments != null)
{
<h3>
Students Enrolled in Selected Course
</h3>
<table class="table">
<tr>
<th>Name</th>
<th>Grade</th>
</tr>
@foreach (var item in Model.Instructor.Enrollments)
{
<tr>
<td>
@item.Student.FullName
</td>
<td>
@Html.DisplayFor(modelItem => item.Grade)
</td>
</tr>
}
</table>
}
The preceding markup displays a list of the students who are enrolled in the selected course.
Refresh the page and select an instructor. Select a course to see the list of enrolled students and their grades.
Using Single
The Single
method can pass in the Where
condition instead of calling the Where
method separately:
public async Task OnGetAsync(int? id, int? courseID)
{
Instructor = new InstructorIndexData();
Instructor.Instructors = await _context.Instructors
.Include(i => i.OfficeAssignment)
.Include(i => i.CourseAssignments)
.ThenInclude(i => i.Course)
.ThenInclude(i => i.Department)
.Include(i => i.CourseAssignments)
.ThenInclude(i => i.Course)
.ThenInclude(i => i.Enrollments)
.ThenInclude(i => i.Student)
.AsNoTracking()
.OrderBy(i => i.LastName)
.ToListAsync();
if (id != null)
{
InstructorID = id.Value;
Instructor instructor = Instructor.Instructors.Single(
i => i.ID == id.Value);
Instructor.Courses = instructor.CourseAssignments.Select(
s => s.Course);
}
if (courseID != null)
{
CourseID = courseID.Value;
Instructor.Enrollments = Instructor.Courses.Single(
x => x.CourseID == courseID).Enrollments;
}
}
The preceding Single
approach provides no benefits over using Where
. Some developers prefer the Single
approach style.
Explicit loading
The current code specifies eager loading for Enrollments
and Students
:
Instructor.Instructors = await _context.Instructors
.Include(i => i.OfficeAssignment)
.Include(i => i.CourseAssignments)
.ThenInclude(i => i.Course)
.ThenInclude(i => i.Department)
.Include(i => i.CourseAssignments)
.ThenInclude(i => i.Course)
.ThenInclude(i => i.Enrollments)
.ThenInclude(i => i.Student)
.AsNoTracking()
.OrderBy(i => i.LastName)
.ToListAsync();
Suppose users rarely want to see enrollments in a course. In that case, an optimization would be to only load the enrollment data if it's requested. In this section, the OnGetAsync
is updated to use explicit loading of Enrollments
and Students
.
Update the OnGetAsync
with the following code:
public async Task OnGetAsync(int? id, int? courseID)
{
Instructor = new InstructorIndexData();
Instructor.Instructors = await _context.Instructors
.Include(i => i.OfficeAssignment)
.Include(i => i.CourseAssignments)
.ThenInclude(i => i.Course)
.ThenInclude(i => i.Department)
//.Include(i => i.CourseAssignments)
// .ThenInclude(i => i.Course)
// .ThenInclude(i => i.Enrollments)
// .ThenInclude(i => i.Student)
// .AsNoTracking()
.OrderBy(i => i.LastName)
.ToListAsync();
if (id != null)
{
InstructorID = id.Value;
Instructor instructor = Instructor.Instructors.Where(
i => i.ID == id.Value).Single();
Instructor.Courses = instructor.CourseAssignments.Select(s => s.Course);
}
if (courseID != null)
{
CourseID = courseID.Value;
var selectedCourse = Instructor.Courses.Where(x => x.CourseID == courseID).Single();
await _context.Entry(selectedCourse).Collection(x => x.Enrollments).LoadAsync();
foreach (Enrollment enrollment in selectedCourse.Enrollments)
{
await _context.Entry(enrollment).Reference(x => x.Student).LoadAsync();
}
Instructor.Enrollments = selectedCourse.Enrollments;
}
}
The preceding code drops the ThenInclude method calls for enrollment and student data. If a course is selected, the highlighted code retrieves:
- The
Enrollment
entities for the selected course. - The
Student
entities for eachEnrollment
.
Notice the preceding code comments out .AsNoTracking()
. Navigation properties can only be explicitly loaded for tracked entities.
Test the app. From a users perspective, the app behaves identically to the previous version.
The next tutorial shows how to update related data.
Additional resources
Feedback
https://aka.ms/ContentUserFeedback.
În curând: Pe parcursul anului 2024, vom elimina treptat Probleme legate de GitHub ca mecanism de feedback pentru conținut și îl vom înlocui cu un nou sistem de feedback. Pentru mai multe informații, consultați:Trimiteți și vizualizați feedback pentru