通过上一篇的学习 我们把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],