Razor Pages with EF Core in ASP.NET Core – Read Related Data – 6 of 8

[ad_1]


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.

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 the completed app for this stage.

The following illustrations show the completed pages for this tutorial:

Courses Index page

Instructors Index page

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 and ThenInclude methods.

    Eager loading example

    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 with Load is more like explict loading than eager loading.

    Separate queries example

    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:

    Explicit loading example

  • Lazy loading. EF Core doesn’t currently support 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 DB each time a navigation property is accessed for the first time.

  • The Select operator loads only the related data needed.

Create a Courses 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 the Department entity.
  • The Department entity comes from the Course.Department navigation property.

ourse.Department

Scaffold the Course model

  • Exit Visual Studio.

  • Open a command window in the project directory (The directory that contains the Program.cs, Startup.cs, and .csproj files).

  • Run the following command:

    dotnet aspnet-codegenerator razorpage -m Course -dc SchoolContext -udl -outDir PagesCourses --referenceScriptLibraries
    

The preceding command scaffolds the Course model. Open the project in Visual Studio.

Build the project. The build generates errors like the following:

۱>Pages/Courses/Index.cshtml.cs(26,37,26,43): error CS1061: 'SchoolContext' does not contain a definition for 'Course' and no extension method 'Course' accepting a first argument of type 'SchoolContext' could be found (are you missing a using directive or an assembly reference?)

Globally change _context.Course to _context.Courses (that is, add an “s” to Course). 7 occurrences are found and updated.

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="TestCreate">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)
        
                    <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 the Department entity that’s loaded into the Department navigation property:

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

Run the app and select the Courses tab to see the list with department names.

Courses Index page

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.


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). The Instructor and OfficeAssignment entities are in a one-to-zero-or-one relationship. Eager loading is used for the OfficeAssignment 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. The Instructor and Course entities are in a many-to-many relationship. Eager loading is used for the Course entities and their related Department 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. The Course and Enrollment 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

  • Exit Visual Studio.

  • Open a command window in the project directory (The directory that contains the Program.cs, Startup.cs, and .csproj files).

  • Run the following command:

    dotnet aspnet-codegenerator razorpage -m Instructor -dc SchoolContext -udl -outDir PagesInstructors --referenceScriptLibraries
    

The preceding command scaffolds the Instructor model. Open the project in Visual Studio.

Build the project. The build generates errors.

Globally change _context.Instructor to _context.Instructors (that is, add an “s” to Instructor). 7 occurrences are found and updated.

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 on the Pages/Instructors/Index.cshtml page:

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:

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)
        
                    <a asp-page="./Details" asp-route-id="@item.ID">Details</a> 
    </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 if item.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 with @: for more about this razor syntax.

  • Added code that dynamically adds class="success" to the tr 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.

Instructors Index page nothing selected

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.

Instructor-to-Courses m:M

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/Courses/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>
                    @Html.ActionLink("Select", "OnGetAsync", 
                                  new  courseID = item.CourseID )
                </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.

Instructors Index page instructor selected

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.

Instructors Index page instructor and course selected

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 each Enrollment.

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.




[ad_2]

source_link
https://www.asp.net