本文详细介绍了在数据库应用中如何高效地加载关系数据，包括延迟加载、贪婪加载、显示加载等策略，并通过实例展示了如何优化导航属性的加载，以提高性能和用户体验。以课程与院系之间的关系为例，探讨了是否在课程类中加入院系ID以优化查询效率的问题。
通过上一篇的学习 我们把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语句
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 @: @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没有
我想问的是 是我哪操作的不对么？ 造成了这个原因？ 请高手解答下
四.总结
关系的加载就结束了 其实写关系加载的园子中有不少好文章了 我这里写的少了些~~
不过关系的操作还没有结束
扫一扫
890
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
