Introduction to the Entity Framework 6 using MVC5 (vii)--reading relevant data for the ASP.NET MVC application

read related data for ASP.net MVC application

This is the Microsoft Official tutorial Getting started with Entity Framework 6 Code The 5 Series translation, this is seventh: read the relevant data for the ASP.net MVC application

Original: Reading Related Data with the Entity Framework in a asp.net MVC application

You have completed the school data model in the previous tutorial. In this tutorial you will learn how to read data related to reality--this refers to data that is loaded through the navigation properties of the Entity Framework.

The screenshot below shows the page you're about to complete.

delay, advance, and reality load related data

The Entity Framework has a variety of methods for loading related data from the navigation properties of an entity: Deferred loading (Lazy
Loading). When an entity is first read, the relevant data is not fetched. However, when you first try to access the navigation properties, the data that the navigation property requires is automatically loaded. The results are sent to the database using multiple queries-one reading the entity itself and then each associated entity. The DbContext class defaults to using deferred loading.

Pre-loading (Eager
Loading). Gets the data associated with the entity as soon as it is read. This usually results in retrieving all the required data in a single connection query. You can specify preload by using the Include method.

Explicit Loading (Explicit
Loading). Somewhat similar to deferred loading, you simply get the relevant data explicitly in your code. When you access a navigation property, it is not automatically loaded. You need to manually load related data by using the entity's Object state manager and invoking the Collection.load method on the collection or by holding the Reference.load method of the properties of a single entity. (In the following example, if you want to load the admin navigation properties, you need to use reference (x
=> x.administrator) to replace collection (x => x.courses))

Both deferred and explicit loading do not immediately retrieve the value of the property, so they are also referred to as deferred loading.

Performance Considerations

If you know that you immediately need the relevant data for each entity, preload usually provides the best performance. Because the efficiency of a single query being sent to a database and fetching data one at a time is usually more efficient than sending a query again on each entity. For example, in the above example, assuming that each system has 10 related courses, preloading causes only one query (join union query) to return to the database. Both deferred loading and explicit loading will result in 11 queries and round-trip. In the case of high latency, additional queries and round trips are usually unfavourable.

On the other hand, the use of deferred loading is more efficient in some cases. Preloading can cause a very complex join query that SQL Server cannot handle efficiently. Alternatively, if you are working on the navigation properties of an entity that you need to access, which is only a subset of the entity set, deferred loading may be better than preload performance because preload will load all the data, even if you do not need to access them. If the performance of your application is extremely important, you'd better test and choose the best one between the two methods.

Deferred loading may mask some code that causes performance problems. For example, code that does not specify a pre-or explicit load but uses navigation properties in each iteration when handling a large number of entities is likely to be inefficient (because there will be a large number of database round trips). An application that is performing well in a development environment may degrade performance when moving to a Windows Azure SQL database due to increased latency. You should analyze and test to make sure that the deferred loading is appropriate. For more information, see demystifying Entity framework strategies:loading Related data and using the Entity framework to Reduce network Latenc Y to SQL Azure.

to disable deferred loading before serialization

If you enable deferred loading during serialization, you may eventually be able to query for more data than expected. Serialization typically accesses each property of a class. Property access triggers deferred loading, and then the deferred entities are also serialized. In the end it is possible to cause more delay loading and serialization of attributes, to prevent this chain reaction, disable deferred loading before entity serialization.

One way to avoid serialization problems is to serialize a data transfer object (DTO) instead of an entity object, as shown in the using Web API with Entity framework tutorial.

If you do not want to use the DTO, you can disable deferred loading and avoid proxy problems by disabling proxy creation.

Here are some ways to disable lazy loading: Omit the virtual keyword declaration for specific navigational properties. For all navigation properties, you can set lazyloadingenabled to false and place the following code in the constructor of your context class:

This. configuration.lazyloadingenabled = false;

Create Course page, display the name of the system

The course entity contains a navigation property that includes the department entity assigned to the course. To display the assigned department name in the course list, you need to get the Name property, the Course.department navigation property, from the Department entity.

Create a new "MVC 5 controller (using entity Framework)" Controller for the course entity type and name it Coursecontroller, using the same settings you created the student controller before, as shown in the following illustration:

Open the Controller and view the index method:

Public ActionResult Index ()
{
    var courses = db. Courses.include (c => c.department);
    Return View (courses. ToList ());
}

You see the scaffolding that is automatically generated uses the Include method to specify the preload of the Department property.

Open views\course\index.cshtml and replace the original with the following code:

@model ienumerable<contosouniversity.models.course> @{viewbag.title = "Courses";}  
You made the following changes to the scaffolding code: Change the title from index to course. Added a number row to display the CourseID property value. By default, scaffolding does not generate primary key templates because they are usually meaningless to end users. But in this case, we're just going to show you how you can show it. Moving the course line to the right and modifying its caption, the scaffolding correctly selects the name attribute of the department entity, but in this course page the column headings should be department, not name.

Note that in the line, the scaffolding code displays the Name property of the entity that is loaded by the navigation property.

<td>
    @Html. Displayfor (ModelItem => item. Department.name)
</td>
 
Run the page (select the Course tab) to see a list of the names of the lines.

Create instructor pages to display course and registration information

In this section you will create a controller and a view that uses the instructor entity to display the instructor page.


This page reads and realistically related data in the following ways: The instructor list shows the relevant data for the OfficeAssignment entity. Instructor and OfficeAssignment entities are one-to-one or zero relationships, and you can preload using officeassignment entity amounts. As mentioned earlier, preload is more efficient when you need all of the associated data for the primary table. Here, you want to show the office assignments for all lecturers. When a user selects an instructor, the associated course entity is displayed. There is a many-to-many relationship between the instructor and the course entities. You can also use preload on the course entities and their associated department entities. However, deferred loading may be more effective here, because you only need course information for the instructor you have selected. In fact, this demonstrates how to use deferred loading to load navigation properties in navigation properties. When a user chooses a course, the relevant data in the registered entity record is displayed. Course and enrollment entities are a one-to-many relationship. You will add an explicit load to the enrollment entities and their associated student entities. (Explicit loading is actually unnecessary, but here's just a demonstration of how to do an explicit load)

Create viewmodel for instructor indexed views

The instructor page shows three different tables, so you will create a view model with three properties, each holding the data required by a table.

In the ViewModels folder, create the InstructorIndexData.cs and replace the original with the following code:

Using System.Collections.Generic;
Using Contosouniversity.models;

Namespace Contosouniversity.viewmodels
{public
    class Instructorindexdata
    {public
        ienumerable< instructor> instructors {get; set;}
        Public ienumerable<course> courses {get; set;}
        Public ienumerable<enrollment> enrollments {get; set;}}}
 
creating Instructor controllers and views

Like the previous Coursecontroller controller, create a Instructorcontroller controller, as shown in the following illustration:


Open Controller\instructorcontroller.cs and add a ViewModels namespace reference:

Using Contosouniversity.viewmodels;
 
Specifies that only officeassignment navigation properties are preloaded in the index method code created by scaffolding.

Public ActionResult Index ()
{
    var instructors = db. Instructors.include (i => i.officeassignment);
    Return View (instructors. ToList ());
}
 
Replace the index method with the following code to load other related data:

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))
        . (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 receives an optional route parameter (ID) and a query string parameter (CourseID) to provide the selected instructor and course ID values and pass all required data to the view. The selection hyperlinks on the page provide these parameters.

The code first creates an instance of the view model and puts the instructor list in the model, which specifies that preload is used on officeassignment and courses navigation properties.

var ViewModel = new Instructorindexdata ();
Viewmodel.instructors = db. Instructors
    . Include (i => i.officeassignment)
    . Include (i => i.courses.select (c => c.department))
     . (i => i.lastname);
 
The second include method loads the course and preload the department navigation properties for each course.

. Include (i => i.courses.select (c => c.department))
 
As described previously, preload is not required unless it is to improve performance. Because views always require officeassgnment entities, it is more efficient to process them in the same query. Course entities are loaded when an instructor is selected, and only the page shows the course more often than not, preload is better than deferred loading.

If a lecturer ID is selected, the selected instructor is retrieved from the list of view models. The courses property of the view model loads the associated course entity through the instructor's courses navigation properties.

if (ID!= null)
{
    Viewbag.instructorid = ID. Value;
    viewmodel.courses = ViewModel.Instructors.Where (i => i.id = = ID. Value). Single (). Courses;
}
 
The Where method returns a collection but only returns a single instructor entity here. The single method converts the collection to a lecturer entity, giving you access to the courses property of the entity.

When you know that the collection will contain only one element, you can use the single method on the collection. An exception should be sent when you call the single method on an empty collection or a collection that has multiple elements. Another option is to use Singleordefault, and if the collection is empty, return a default value. However, using Singleordefault in this case will still cause an exception (will attempt to access the courses property, but the property is a null reference) and the exception message will illustrate this. When you call the single method, you can also pass a where condition, instead of calling the where and single method individually:

. Single (i => i.id = = ID. Value)
 
Instead of

. Where (I => i.id = = ID. Value). Single ()
 
Next, if you select a course, the selected course is retrieved from the list of courses in the view model. The registered entity is then read from the course's registration navigation properties and loaded into the registration properties of the view model.

    if (CourseID!= null)
    {
        Viewbag.courseid = Courseid.value;
        viewmodel.enrollments = ViewModel.Courses.Where (
            x => X.courseid = = CourseID). Single (). enrollments;
    }
 
Modify the instructor indexed view

In views\instructor\index.cshtml, use the following code to replace the original:

@model ContosoUniversity.ViewModels.InstructorIndexData @{viewbag.title = "Instructors";}  
You made the following changes to the code: Change the view's model class for Instructorindexdata changed the title added an office column to be used as item. Display location when officeassignent is not empty (since this is a one-to-one or zero relationship)

<td> 
    @if (item. OfficeAssignment!= null) 
    { 
        @item. Officeassignment.location  
    } 
Add class= "Success" to the selected instructor's TR element dynamically through the code. This sets the background color of the selected rows by using the bootstrap style sheet
String selectedrow = ""; 
if (item. Instructorid = = Viewbag.instructorid) 
{ 
    Selectedrow = "Success"; 
} 
Adds a new actionlink that is used to send the selected instructor ID to the index method.
Run the application, then select the Instructor tab, the page displays information about the instructor, and the Location property of the OfficeAssignment entity is not empty, and nothing is displayed if there is no related office.


In the views\instructor\index.cshtml file, add the following code after the table element to display the selected instructor's list of courses

@if (model.courses!= null) { 
This code is used to read the course properties in the View module and display them. It also provides a select hyperlink that sends the ID of the selected course to the index method.

Run the page and select an instructor, and you will see a table showing the courses assigned to the instructor.


Add the following code after the code you just added to display the list of students enrolled in the selected course.

@if (model.enrollments!= null)
{
    
 
This code reads the enrollments property of the view model to show the students enrolled in the course.

Run the page and select a lecturer and choose a course to view the registered students and their results.


Add an explicit load

Open InstructorController.cs to check how the index method gets the registered list for the selected course:

    if (CourseID!= null)
    {
        Viewbag.courseid = Courseid.value;
        viewmodel.enrollments = ViewModel.Courses.Where (
            x => X.courseid = = CourseID). Single (). enrollments;
    }
 
When you retrieve a list of lecturers, you specify preload on the courses navigation properties and on the system properties of each course. Then you put the course collection into the view model, and now you can access it by registering the navigation properties in the entity of the collection. Because you did not specify the preload of the Course.enrollments navigation property, the data in this property will be loaded with deferred loading and will not be loaded until the page is rendered.

If you disable deferred loading without changing other code, the enrollments property will be empty regardless of the actual number of registrations. In this case, if you want to load the enrollments property, you must specify preload or explicit loading. You've seen how to use preload. To demonstrate explicit loading, replace the original student section with the following code, and we will use explicit loading on the enrollments property.

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)).

    (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//viewmodel.enrollments = ViewModel.Courses.Where (//x => X.courseid = = CourseID). S Ingle ().
        enrollments; Explicit loading var selectedcourse = viewModel.Courses.Where (x => X.courseid = = CourseID).
        Single (); Db. Entry (Selectedcourse). Collection (x => x.enrollments). Load ()