Entity framework 加载多层相关实体数据

Entity framework有3种加载数据的方式：懒汉式（Lazy loading），饿汉式（Eager loading），显示加载（Explicit loading）。3种加载方式有各自的优缺点，详细的可以参考微软的技术文章《Reading Related Data with the Entity Framework in an ASP.NET MVC Application》
本文只针对需要一次性加载多个相关联实体的情况进行讨论。
 
1. 数据库模型
 
 
2. Model和DbContext
 
 1 public class Student
 2     {
 3         public int ID { get; set; }
 4 
 5         public string LastName { get; set; }
 6 
 7         public string FirstMidName { get; set; }
 8 
 9         public DateTime? EnrollmentDate { get; set; }
10 
11         public virtual ICollection<Enrollment> Enrollments { get; set; }
12     }
Student.cs
 1 public class Enrollment
 2     {
 3         public int EnrollmentID { get; set; }
 4 
 5         public int CourseID { get; set; }
 6 
 7         public int StudentID { get; set; }
 8 
 9         public int? Grade { get; set; }
10 
11         public virtual Student Student { get; set; }
12 
13         public virtual Course Course { get; set; }
14     }
Enrollment.cs
 1 public class Course
 2     {
 3         public int CourseID { get; set; }
 4 
 5         public string Title { get; set; }
 6 
 7         public string Credits { get; set; }
 8 
 9         public virtual ICollection<Enrollment> Enrollments { get; set; }
10     }
Course
 1 public class Context:DbContext
 2     {
 3         public Context() : base("ReadMultipleEntity")
 4         {
 5 
 6         }
 7 
 8         public DbSet<Student> Students { get; set; }
 9 
10         public DbSet<Course> Courses { get; set; }
11 
12         public DbSet<Enrollment> Enrollments { get; set; }
13 
14         protected override void OnModelCreating(DbModelBuilder modelBuilder)
15         {
16             modelBuilder.Conventions.Remove<PluralizingTableNameConvention>();
17         }
18 
19     }
Context.cs
 
3. 需求
 
想要得到所有学生及其选课名称的列表
因为是想要所有学生及课程的列表信息，所以此时选择饿汉式效率最佳（一次性将所有数据加载到内存）。
context.Students.Include(x => x.Enrollments).ToList();//加载Students和Enrollments实体
 
通过Sql Profiler监控到所执行的SQL
 1 SELECT 
 2     [Project1].[ID] AS [ID], 
 3     [Project1].[LastName] AS [LastName], 
 4     [Project1].[FirstMidName] AS [FirstMidName], 
 5     [Project1].[EnrollmentDate] AS [EnrollmentDate], 
 6     [Project1].[C1] AS [C1], 
 7     [Project1].[EnrollmentID] AS [EnrollmentID], 
 8     [Project1].[CourseID] AS [CourseID], 
 9     [Project1].[StudentID] AS [StudentID], 
10     [Project1].[Grade] AS [Grade]
11     FROM ( SELECT 
12         [Extent1].[ID] AS [ID], 
13         [Extent1].[LastName] AS [LastName], 
14         [Extent1].[FirstMidName] AS [FirstMidName], 
15         [Extent1].[EnrollmentDate] AS [EnrollmentDate], 
16         [Extent2].[EnrollmentID] AS [EnrollmentID], 
17         [Extent2].[CourseID] AS [CourseID], 
18         [Extent2].[StudentID] AS [StudentID], 
19         [Extent2].[Grade] AS [Grade], 
20         CASE WHEN ([Extent2].[EnrollmentID] IS NULL) THEN CAST(NULL AS int) ELSE 1 END AS [C1]
21         FROM  [dbo].[Student] AS [Extent1]
22         LEFT OUTER JOIN [dbo].[Enrollment] AS [Extent2] ON [Extent1].[ID] = [Extent2].[StudentID]
23     )  AS [Project1]
24     ORDER BY [Project1].[ID] ASC, [Project1].[C1] ASC
Only Enrollment

 

如果想将和Enrollment相关联的Course实体同时加载，则需要如下语句：
context.Students.Include(x => x.Enrollments.Select(y => y.Course)).ToList();
 
通过Sql Profiler监控到所执行的SQL
 1 SELECT 
 2     [Project1].[ID] AS [ID], 
 3     [Project1].[LastName] AS [LastName], 
 4     [Project1].[FirstMidName] AS [FirstMidName], 
 5     [Project1].[EnrollmentDate] AS [EnrollmentDate], 
 6     [Project1].[C1] AS [C1], 
 7     [Project1].[EnrollmentID] AS [EnrollmentID], 
 8     [Project1].[CourseID] AS [CourseID], 
 9     [Project1].[StudentID] AS [StudentID], 
10     [Project1].[Grade] AS [Grade], 
11     [Project1].[CourseID1] AS [CourseID1], 
12     [Project1].[Title] AS [Title], 
13     [Project1].[Credits] AS [Credits]
14     FROM ( SELECT 
15         [Extent1].[ID] AS [ID], 
16         [Extent1].[LastName] AS [LastName], 
17         [Extent1].[FirstMidName] AS [FirstMidName], 
18         [Extent1].[EnrollmentDate] AS [EnrollmentDate], 
19         [Join1].[EnrollmentID] AS [EnrollmentID], 
20         [Join1].[CourseID1] AS [CourseID], 
21         [Join1].[StudentID] AS [StudentID], 
22         [Join1].[Grade] AS [Grade], 
23         [Join1].[CourseID2] AS [CourseID1], 
24         [Join1].[Title] AS [Title], 
25         [Join1].[Credits] AS [Credits], 
26         CASE WHEN ([Join1].[EnrollmentID] IS NULL) THEN CAST(NULL AS int) ELSE 1 END AS [C1]
27         FROM  [dbo].[Student] AS [Extent1]
28         LEFT OUTER JOIN  (SELECT [Extent2].[EnrollmentID] AS [EnrollmentID], [Extent2].[CourseID] AS [CourseID1], [Extent2].[StudentID] AS [StudentID], [Extent2].[Grade] AS [Grade], [Extent3].[CourseID] AS [CourseID2], [Extent3].[Title] AS [Title], [Extent3].[Credits] AS [Credits]
29             FROM  [dbo].[Enrollment] AS [Extent2]
30             INNER JOIN [dbo].[Course] AS [Extent3] ON [Extent2].[CourseID] = [Extent3].[CourseID] ) AS [Join1] ON [Extent1].[ID] = [Join1].[StudentID]
31     )  AS [Project1]
32     ORDER BY [Project1].[ID] ASC, [Project1].[C1] ASC
Enrollment and Course

 

比较两次SQL可得，后者多join了一个Course表，从而一次性得到所有的结果。如果想在Course再连接其他实体，可以仿照上面的形式。
 
 
 
posted @ 2017-08-20 16:44  何事惊慌？  阅读(462)  评论(0)  编辑  收藏  举报