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沒有

我想問的是是我哪操作的不對么？造成了這個原因？請高手解答下

四.總結

關系的加載就結束了其實寫關系加載的園子中有不少好文章了我這里寫的少了些~~

不過關系的操作還沒有結束

導航屬性的更新等操作

posted on 2011-07-29 08:44 wlf 閱讀(23013) 評論(41) 收藏舉報