MVC3+EF4.1学习系列(五)----- EF查找导航属性的几种方式

文章索引和简介

通过上一篇的学习我们把demo的各种关系终于搭建里起来以及处理好了如何映射到数据库等问题但是只是搭建好了关系问题还远没有解决

这篇就来写如何查找导航属性和查找导航属性的几种方式已经跟踪生成的SQL来检测是否满意通过这节学习来明白什么时候用哪个~~

一.三种加载

1.延迟加载

这是原文中的图大家可以去看下我模仿上面的做了个测试出现了 已有打开的与此 Command 相关联的 DataReader，必须首先将它关闭。

我的解决办法是 var departments = db.Departments.ToList(); 现读取出来然后再遍历. 而不加ToList() 真正执行SQL语句在 foreach的时候

然后再说下这样写以后 SQL语句的执行

1.上来先查询出所有的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.再执行到内层foreach时这个会执行多次每次@EntityKeyValue1 等于迭代到这次的 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

也就是说我们有多少条Department 就要执行多少次上面的方法当然这里使用的是exec sp_executesql 利用sp_executesql，能够重用执行计划，这就大大提供了执行性能

2.贪婪加载

在执行到第一个foreach 时就执行了SQL语句这是EF帮我们生成的

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.显示加载

先看图

这个我测试后效果是和第一个一样的并没有看出什么好处？期待高手指点下

英文好的也可以看下原文

4.关闭延迟加载

如果我们想启用延迟加载可以通过这两种方式

1.去掉属性里的virtual

2.context.Configuration.LazyLoadingEnabled = false;

二.实战开始创建教师页

先上实现后的效果图

从图中我们可以看出这个要处理的关系

1对1的教师和办公地点

1对多的教师教的课程

普通的多对多的

多对多的(关系表里有数据的) 课程和学生查看选择课程的学生和学分

1.创建viewmodel

有时我们的页面显示的不是一个实体类的内容这个时候我们可以创建一个ViewModel 来展示界面

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.创建控制器添加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); }

先看进来访问的这一块

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

从最上面的图中我们可以看到要显示有教师信息办公地址和所教课程

于是我们使用贪婪加载出办公地址和课程但是原文教程里还Select(c => c.Department) 把院系也一起加载了进来我认为这是没必要的

于是我把代码修改为

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

去掉了对院系的贪婪加载

看下生成的SQL语句

View Code

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

继续分析

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

如果点击教师则可查看该教师教的课程这个id 就是教师ID 一会儿会在视图展示这个这个就是根据教师查看课程

接着是点击课程查看所选的学生和分数

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

这里还给出里另一种方法

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

最后上视图

@model ContosoUniversity.ViewModels.InstructorIndexData
@{     ViewBag.Title = "Instructors"; }
<h2>Instructors</h2>
<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>  }

三.上节的一个问题与疑问的提出

再上节的建立关系中有一个这样的问题一对多的关系中是否应该为导航属性再专门建立一个ID

比如我们可课程与院系一个院系可以有多个课程一个课程只能属于一个院系那我们是否应该在课程类里加入院系ID呢

如

    /// <summary>     /// 课程类     /// </summary>     public class Course     {         /// <summary>         /// 课程ID         /// </summary>         [DatabaseGenerated(DatabaseGeneratedOption.None)]         [Display(Name = "Number")]         public int CourseID { get; set; }         /// <summary>         /// 课程名称         /// </summary>         [Required(ErrorMessage = "Title is required.")]         [MaxLength(50)]         public string Title { get; set; }         /// <summary>         /// 学分         /// </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>         /// 关系表导航属性  一个课程允许被多次报名等级         /// </summary>         public virtual ICollection<Enrollment> Enrollments { get; set; }         public virtual Department Department { get; set; }         public virtual ICollection<Instructor> Instructors { get; set; }
     }

这里面加了院系ID 我以前一直觉得没有必要加这个今天在做这个导航属性查找时发现一个问题做个小实验

比如我想得到其中一个课程的ID 如果有院系ID 属性可以这么写

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

如果没可以这么写

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

首先这个都没有用贪婪加载默认的延迟加载如果你使用上面的则不会往数据库里去执行一条根据课程ID查找院系的SQL语句

但你使用下面的则会往数据库里发送一条查找语句

这点 EF做的是并不好的在NH里两种方法都不会发送因为在下面那里使用了代理而EF没有

我想问的是是我哪操作的不对么？造成了这个原因？请高手解答下

四.总结

关系的加载就结束了其实写关系加载的园子中有不少好文章了我这里写的少了些~~

不过关系的操作还没有结束

导航属性的更新等操作