Article index and introduction

Through the study of the last article We put demo All kinds of relationships are finally built up And how to map to database however It's just a relationship The problem is far from solved

This article is about how to find navigation properties And several ways to find navigation properties The generated SQL To see if you're satisfied Learn through this section To understand when and which ~~

One . Three kinds of loading

1. Delay loading

This is a original text Map in You can go and have a look at   I did a test to imitate the above   There is   There are already open Command The associated DataReader, It must be closed first .

My solution is    var departments = db.Departments.ToList();    Now read it out And I'm going to go through it again . Without ToList()  True execution SQL Statements in foreach When

And then I'll talk about it After that SQL Execution of statements

1. Come up and find out all the Department

SELECT [Extent1].[DepartmentID] AS [DepartmentID], [Extent1].[Name] AS [Name], [Extent1].[Budget] AS [Budget], [Extent1].[StartDate] AS [StartDate], [Extent1].[InstructorID] AS [InstructorID] FROM [dbo].[Department] AS [Extent1]

2. And then to the inner layer foreach when   This will be done many times   Every time @EntityKeyValue1 be equal to Iterate to this DepartmentID

exec sp_executesql N'SELECT [Extent1].[CourseID] AS [CourseID], [Extent1].[Title] AS [Title], [Extent1].[Credits] AS [Credits], [Extent1].[DepartmentID] AS [DepartmentID] FROM [dbo].[Course] AS [Extent1] WHERE [Extent1].[DepartmentID] = @EntityKeyValue1',N'@EntityKeyValue1 int',@EntityKeyValue1=1

in other words How many we have Department How many times do you have to perform the above method    Of course What we use here is exec sp_executesql   utilize sp_executesql, Be able to reuse execution plans , This greatly provides execution performance

2. Greedy loading

From execution to the first foreach when   It's done SQL sentence This is a EF That generated it for us

SELECT [Project1].[DepartmentID] AS [DepartmentID], [Project1].[Name] AS [Name], [Project1].[Budget] AS [Budget], [Project1].[StartDate] AS [StartDate], [Project1].[InstructorID] AS [InstructorID], [Project1].[C1] AS [C1], [Project1].[CourseID] AS [CourseID], [Project1].[Title] AS [Title], [Project1].[Credits] AS [Credits], [Project1].[DepartmentID1] AS [DepartmentID1] FROM ( SELECT [Extent1].[DepartmentID] AS [DepartmentID], [Extent1].[Name] AS [Name], [Extent1].[Budget] AS [Budget], [Extent1].[StartDate] AS [StartDate], [Extent1].[InstructorID] AS [InstructorID], [Extent2].[CourseID] AS [CourseID], [Extent2].[Title] AS [Title], [Extent2].[Credits] AS [Credits], [Extent2].[DepartmentID] AS [DepartmentID1], CASE WHEN ([Extent2].[CourseID] IS NULL) THEN CAST(NULL AS int) ELSE 1 END AS [C1] FROM [dbo].[Department] AS [Extent1] LEFT OUTER JOIN [dbo].[Course] AS [Extent2] ON [Extent1].[DepartmentID] = [Extent2].[DepartmentID] ) AS [Project1] ORDER BY [Project1].[DepartmentID] ASC, [Project1].[C1] ASC

3. According to load

Look at the picture first

After I tested this The effect is the same as the first one I don't see any benefit ? I'm looking forward to the guidance of experts

English is good also can see original text

4. Turn off delayed loading

If we want to enable lazy loading There are two ways

1. Remove the attribute virtual

2.context.Configuration.LazyLoadingEnabled = false;

Two . The actual battle begins Create a teacher page

The effect picture after the first implementation

From the picture We can see the relationship to be dealt with

1 Yes 1 Of Teachers and offices

1 To many The course the teacher teaches

Ordinary many to many

Many to many ( There's data in the relational table )  Courses and students   View the students and credits for the selected course

1. establish viewmodel

Sometimes Our page It doesn't show the content of an entity class   At this point, we can create a ViewModel To show the interface

using System; 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; } } }

2. Create controller add Index

public ActionResult Index(Int32? id, Int32? courseID) { var viewModel = new InstructorIndexData(); viewModel.Instructors = db.Instructors .Include(i => i.OfficeAssignment) .Include(i => i.Courses.Select(c => c.Department)) .OrderBy(i => i.LastName);
if (id != null) { ViewBag.InstructorID = id.Value; viewModel.Courses = viewModel.Instructors.Where(i => i.InstructorID == id.Value).Single().Courses; }
if (courseID != null) { ViewBag.CourseID = courseID.Value; viewModel.Enrollments = viewModel.Courses.Where(x => x.CourseID == courseID).Single().Enrollments; }
return View(viewModel); }

Let's take a look at the area we visited first

 viewModel.Instructors = db.Instructors .Include(i => i.OfficeAssignment) .Include(i => i.Courses.Select(c => c.Department)) .OrderBy(i => i.LastName);

From the top picture We can see   To display teacher information Office address And the courses taught

therefore We use greedy to load out office addresses and courses   however The original tutorial in also Select(c => c.Department) The departments are also loaded in   I don't think it's necessary

therefore I changed the code to

 db.Instructors .Include(i => i.OfficeAssignment) .Include(i => i.Courses) .OrderBy(i => i.LastName);

Get rid of the greedy load on the Department

Look at the generated SQL sentence

SELECT [Project1].[InstructorID1] AS [InstructorID], [Project1].[InstructorID] AS [InstructorID1], [Project1].[LastName] AS [LastName], [Project1].[FirstName] AS [FirstName], [Project1].[HireDate] AS [HireDate], [Project1].[InstructorID2] AS [InstructorID2], [Project1].[Location] AS [Location], [Project1].[C1] AS [C1], [Project1].[CourseID] AS [CourseID], [Project1].[Title] AS [Title], [Project1].[Credits] AS [Credits], [Project1].[DepartmentID] AS [DepartmentID] FROM ( SELECT [Extent1].[InstructorID] AS [InstructorID], [Extent1].[LastName] AS [LastName], [Extent1].[FirstName] AS [FirstName], [Extent1].[HireDate] AS [HireDate], [Extent2].[InstructorID] AS [InstructorID1], [Extent3].[InstructorID] AS [InstructorID2], [Extent3].[Location] AS [Location], [Join3].[CourseID1] AS [CourseID], [Join3].[Title] AS [Title], [Join3].[Credits] AS [Credits], [Join3].[DepartmentID] AS [DepartmentID], CASE WHEN ([Join3].[CourseID2] IS NULL) THEN CAST(NULL AS int) ELSE 1 END AS [C1] FROM [dbo].[Instructor] AS [Extent1] LEFT OUTER JOIN [dbo].[OfficeAssignment] AS [Extent2] ON [Extent1].[InstructorID] = [Extent2].[InstructorID] LEFT OUTER JOIN [dbo].[OfficeAssignment] AS [Extent3] ON [Extent2].[InstructorID] = [Extent3].[InstructorID] LEFT OUTER JOIN (SELECT [Extent4].[CourseID] AS [CourseID2], [Extent4].[InstructorID] AS [InstructorID], [Extent5].[CourseID] AS [CourseID1], [Extent5].[Title] AS [Title], [Extent5].[Credits] AS [Credits], [Extent5].[DepartmentID] AS [DepartmentID] FROM [dbo].[CourseInstructor] AS [Extent4] INNER JOIN [dbo].[Course] AS [Extent5] ON [Extent5].[CourseID] = [Extent4].[CourseID] ) AS [Join3] ON [Extent1].[InstructorID] = [Join3].[InstructorID] ) AS [Project1] ORDER BY [Project1].[LastName] ASC, [Project1].[InstructorID1] ASC, [Project1].[InstructorID] ASC, [Project1].[InstructorID2] ASC, [Project1].[C1] ASC

Continue analysis

 if (id != null) { ViewBag.InstructorID = id.Value; viewModel.Courses = viewModel.Instructors.Where(i => i.InstructorID == id.Value).Single().Courses; }

If you click teacher You can view the courses taught by the teacher   This id It's teachers ID I'll show this in the view later This is based on the teacher's view of the course

Then click on the course View selected students and scores

 if (courseId != null) { viewModel.Enrollments = viewModel.Courses.Where(i => i.CourseID == courseId.Value).Single().Enrollments; }

Here's another way

 if (courseID != null) { ViewBag.CourseID = courseID.Value;
var selectedCourse = viewModel.Courses.Where(x => x.CourseID == courseID).Single(); db.Entry(selectedCourse).Collection(x => x.Enrollments).Load(); foreach (Enrollment enrollment in selectedCourse.Enrollments) { db.Entry(enrollment).Reference(x => x.Student).Load(); } viewModel.Enrollments = viewModel.Courses.Where(x => x.CourseID == courseID).Single().Enrollments; }

Finally, the top view

@model ContosoUniversity.ViewModels.InstructorIndexData
@{ ViewBag.Title = "Instructors"; }
<p> @Html.ActionLink("Create New", "Create") </p> <table> <tr> <th></th> <th>Last Name</th> <th>First Name</th> <th>Hire Date</th> <th>Office</th> <th>Courses</th> </tr> @foreach (var item in Model.Instructors) { string selectedRow = ""; if (item.InstructorID == ViewBag.PersonID) { selectedRow = "selectedrow"; } <tr class="@selectedRow" valign="top"> <td> @Html.ActionLink("Select", "Index", new { id = item.InstructorID }) | @Html.ActionLink("Edit", "Edit", new { id = item.InstructorID }) | @Html.ActionLink("Details", "Details", new { id = item.InstructorID }) | @Html.ActionLink("Delete", "Delete", new { id = item.InstructorID }) </td> <td> @item.LastName </td> <td> @item.FirstMidName </td> <td> @String.Format("{0:d}", item.HireDate) </td> <td> @if (item.OfficeAssignment != null) { @item.OfficeAssignment.Location } </td> <td> @{ foreach (var course in item.Courses) { @course.CourseID @:&nbsp; @course.Title <br /> } } </td> </tr> } </table>
@if (Model.Courses != null) { <h3>Courses Taught by Selected Instructor</h3> <table> <tr> <th></th> <th>ID</th> <th>Title</th> <th>Department</th> </tr> @foreach (var item in Model.Courses) { string selectedRow = ""; if (item.CourseID == ViewBag.CourseID) { selectedRow = "selectedrow"; } <tr class="@selectedRow"> <td> @Html.ActionLink("Select", "Index", new { courseID = item.CourseID }) </td> <td> @item.CourseID </td> <td> @item.Title </td> <td> @item.Department.Name </td> </tr> } </table> }
@if (Model.Enrollments != null) { <h3> Students Enrolled in Selected Course</h3> <table> <tr> <th>Name</th> <th>Grade</th> </tr> @foreach (var item in Model.Enrollments) { <tr> <td> @item.Student.FullName </td> <td> @item.Grade </td> </tr> } </table> }

3、 ... and . A question and question in the last section

In the last section of building a relationship There's a question like this   In a one to many relationship Whether it should be a navigation property Build another one ID

For example, we can Courses and departments   A department can have multiple courses   A course can only belong to one department So whether we should be in the course class Join the faculty ID Well

Such as


 /// <summary> /// Courses /// </summary> public class Course { /// <summary> /// Course ID /// </summary> [DatabaseGenerated(DatabaseGeneratedOption.None)] [Display(Name = "Number")] public int CourseID { get; set; } /// <summary> /// Course name /// </summary> [Required(ErrorMessage = "Title is required.")] [MaxLength(50)] public string Title { get; set; } /// <summary> /// credits /// </summary> [Required(ErrorMessage = "Number of credits is required.")] [Range(0, 5, ErrorMessage = "Number of credits must be between 0 and 5.")] public int Credits { get; set; }
[Display(Name = "Department")] public int DepartmentID { get; set; }
/// <summary> /// Relationship table navigation properties A course is allowed to be registered multiple times /// </summary> public virtual ICollection<Enrollment> Enrollments { get; set; } public virtual Department Department { get; set; } public virtual ICollection<Instructor> Instructors { get; set; }

It's got... In it departments ID  I used to think there was no need to add this When we do this navigation property search today Find a problem Do a little experiment

For example, I'd like to get one of the courses ID If there are departments ID attribute It can be written like this

 var courses = db.Courses.ToList(); int i = courses[0].DepartmentID;

If not It can be written like this

 int i = courses[0].Department.DepartmentID;

First This is useless Default delay loading If you use the above You will not execute a course in the database ID Look up the Department's SQL sentence

But you use the following A lookup statement will be sent to the database

this EF It's not good stay NH in The two methods Will not send   Because there's a proxy down there and EF No,

My question is What's wrong with me ? That's why ? Please master answer

Four . summary

The loading of the relationship is over In fact, there are many good articles in the garden of relationship loading I'm writing less here ~~

But the operation of the relationship is not over yet


Navigation attribute update and other operations

MVC3+EF4.1 Learning Series ( 5、 ... and )----- EF Find more articles about several ways to navigate the properties

  1. MVC3+EF4.1 Learning Series ( 7、 ... and )-----EF Concurrent processing

    Before reading this article Recommend the garden   This article has already introduced And it's very good ~~ You can take a look at his Look at my Concurrent 1. Pessimistic concurrency To put it simply When a user accesses a piece of data The data will be read-only   Make the data exclusive ...

  2. MVC3+EF4.1 Learning Series ( Four )----- ORM The handling of the relationship

    Last article Finally, I have finished some basic operations But these are all single table processing and EF As a ORM frame   We must talk about how to deal with the relationship Deal with the relationship To be able to use EF On the handling of relationships It is commonly   one-on-one   One to many   ...

  3. MVC3+EF4.1 Learning Series ( One )------- establish EF4.1 code first The first example of

    be based on EF4.1 code first ordinary CRUD  There are already many in the garden ~~ I don't want to write this anymore But to make a complete little demo From the beginning To some simple refactorings later I decided to write this down Try to write about others ...

  4. MVC3+EF4.1 Learning Series ( Two )------- Basic addition, deletion, modification and query, and life cycle changes of persistent objects

    In the last article We've created it EF4.1 be based on code first Example   With the database And initialize some data   Today, I'm going to write the basic addition, deletion, modification and query, and the life cycle changes of persistent objects Learn the original text and paste the running original picture first ~~ One . establish ...

  5. MVC3+EF4.1 Learning Series ( Nine )-----EF4.1 The use of other techniques

    In the last section, through a series of refactorings A simple project is achieved But there are still some EF It doesn't say This section is through the project Tell me about EF Other functions and techniques One . Direct execution SQL sentence Generally speaking EF Do not write SQL Of the statement   however On some occasions   For example, opposite students ...

  6. MVC3+EF4.1 Learning Series ( 8、 ... and )----- utilize Repository and Unit of Work Refactoring project

    The basics of the project are over , But now our project is not perfect   Not conducive to testing There's a lot of repetitive code    The coupling between layers is high   Not conducive to expansion and other issues . Today's Chapter It's mainly about solving these problems . When we solve these problems , I am also suspicious ...

  7. MVC3+EF4.1 Learning Series ( 6、 ... and )----- Navigation attribute data update processing

    Through the study of the last article We already know how to query relationships This article talks about how to deal with the updating of navigation attribute data as well as EF What will be generated for us SQL~ Old rules Take a look at today's picture first Adding and modifying pages is basically like this This section is relatively simple ~~ Lord ...

  8. C# Data manipulation series - 7. EF Core Navigation property configuration

    In the last article , It's about Entity Framework Core On the logic of Relational Mapping . In the last article, I left EF The foreign key mapping of doesn't say , It's one-on-one , One to many , For one more , Many to many relationships, etc . This article will give you a detailed analysis , How to design ...

  9. MVC3+EF4.1 Learning Series ( Ten )----MVC+EF Deal with tree structure

    Through the previous articles We dealt with one-on-one , One to many , Many to many relationship Good play ORM The use of the frame however One less The treatment of tree structure , And this tree relationship We often meet , common N How to deal with level categories , And there's always data and categories ...

Random recommendation

  1. Python Basics - Three user authentication login purchase program

    demand : One : Three login locks 1. User information is stored in files 2. Three attempts failed , Lock the user Two . Shopping cart function requirements : Ask the user to enter the total assets , for example :2000 Show a list of products , Let the user choose the product according to the serial number , Join the shopping cart to buy , If the goods ...

  2. HTML And css Simple learning

    The first 21 course : Inline elements , Also known as inline elements , Mainly to control the words in a line : Such as : span, It's not exclusive , Width and height cannot be set , It can't be set in the vertical direction margin And pading Value , But it can be set horizontally .( Because it's only in one ...

  3. CSS in !important The priority of the

    This article uses the latest IE10 as well as firefox And chrome test ( end 2013 year 5 month 27 Japan 22:23:22) CSS Principle : We know ,CSS Writing in different places has different priorities , .css The definition in the document < ...

  4. nutch Installation configuration Environmental Science ubuntu 12.04 sql Build table CREATE D ...

  5. Browser input URL All the way back to the page

    First step , Domain name resolution , Find the host IP (1) The browser will cache DNS A span , commonly 2-30 Different minutes . If you have a cache , Go straight back to IP, Otherwise, the next step . (2) Cannot find... In cache IP, The browser makes a system call , Inquire about hosts file . ...

  6. ASP.NET MVC 5 Study the tutorial : The generated code is explained in detail

    original text  ASP.NET MVC 5 Study the tutorial : The generated code is explained in detail Take off net ASP.NET MVC 5 Study the course catalog : Add controller Add view Modify view and layout pages The controller passes data to the view Add model Create connection string ...

  7. Java I/O Evolution and development Linux The Internet I/O Model

    Reference article : Simple books - Talking about Linux Five kinds IO: One .linux Basic concepts 1.1 Memory space linux Virtual memory is used in the system , namely ...

  8. Redis Set related commands

    1. The properties of unordered sets :① deterministic ② The opposite sex ③ Disorder redis Of set yes string Unordered collection of type set The element can contain at most (2^32-1) Elements sadd key value1....valueN The elements will be ...

  9. [Java] stay jar File read resources A file in a directory

    Pay attention to two points : 1. Add the resource directory to build path, Make sure that the files in this directory are copied to jar In file . 2. jar Things inside , Can be viewed as stream To read , But it should not be taken as file To read . Example ...

  10. Use mysqlbinlog To the main library binlog To synchronize

    #!/bin/bash BASEDIR="/usr/local/mysql" BIN="$BASEDIR/bin" MYSQLBINLOG="$BIN ...