c# - 我应该使用 Entity Framework 导航属性进行查询而不是直接从 DataContext ICollections 进行查询吗？

Question

我正在尝试着手在 Entity Framework 6 中创建投影并将这些投影映射到我的 View 模型。我担心的是使用导航属性时发送的数据库连接和单独查询的数量。例如:

(我的根)

var item = db.CourseContainers.First(p => p.ID == id); 
item
   .CourseItems
   .SelectMany(p => p.CourseItemLessons)
   .Select(p => new LessonsListItem() {
      ID = p.CourseItemID,
      Name = p.Lesson.Name
   }).ToList()

我正在使用 courseitemlessons 提取所有 courseitems，然后投影到 viewmodel 也使用 Lesson 导航来获取名称。我不认为我理解 Entity Framework 是如何解析它的。我希望是这样的:

SELECT [cil].ID,
       [l].Name
FROM   CourseItems ci INNER JOIN
       CourseItemLessons cil ON ci.ID = cil.CourseItemID INNER JOIN
       Lessons l ON cil.LessonID = l.ID
WHERE  ci.CourseID = @courseID

我刚刚按预期写了这个。我知道表结构有点奇怪。而不是上面，有多个连接和选择语句，这是实际发送到数据库的内容。

SELECT TOP (1) 
    [Extent1].[ID] AS [ID], 
    [Extent1].[ModifiedBy] AS [ModifiedBy], 
    [Extent1].[DateModified] AS [DateModified], 
    [Extent1].[AddedBy] AS [AddedBy], 
    [Extent1].[DateAdded] AS [DateAdded], 
    [Extent1].[Name] AS [Name], 
    [Extent1].[Description] AS [Description], 
    [Extent1].[IsLinear] AS [IsLinear], 
    [Extent1].[Privacy] AS [Privacy]
    FROM [dbo].[CourseContainers] AS [Extent1]
    WHERE [Extent1].[ID] = @p__linq__0


-- p__linq__0: '7' (Type = Int32, IsNullable = false)

-- Executing at 26/03/2015 12:01:44 PM +10:00

-- Completed in 0 ms with result: SqlDataReader



Closed connection at 26/03/2015 12:01:44 PM +10:00

Opened connection at 26/03/2015 12:01:44 PM +10:00

SELECT 
    [Extent1].[ID] AS [ID], 
    [Extent1].[ModifiedBy] AS [ModifiedBy], 
    [Extent1].[DateModified] AS [DateModified], 
    [Extent1].[AddedBy] AS [AddedBy], 
    [Extent1].[DateAdded] AS [DateAdded], 
    [Extent1].[SortOrder] AS [SortOrder], 
    [Extent1].[CourseID] AS [CourseID]
    FROM [dbo].[CourseItems] AS [Extent1]
    WHERE [Extent1].[CourseID] = @EntityKeyValue1


-- EntityKeyValue1: '7' (Type = Int32, IsNullable = false)

-- Executing at 26/03/2015 12:01:44 PM +10:00

-- Completed in 0 ms with result: SqlDataReader



Closed connection at 26/03/2015 12:01:44 PM +10:00

Opened connection at 26/03/2015 12:01:44 PM +10:00

SELECT 
    [Extent1].[ID] AS [ID], 
    [Extent1].[ModifiedBy] AS [ModifiedBy], 
    [Extent1].[DateModified] AS [DateModified], 
    [Extent1].[AddedBy] AS [AddedBy], 
    [Extent1].[DateAdded] AS [DateAdded], 
    [Extent1].[CourseItemID] AS [CourseItemID], 
    [Extent1].[ObjectID] AS [ObjectID]
    FROM [dbo].[CourseItemLessons] AS [Extent1]
    WHERE [Extent1].[CourseItemID] = @EntityKeyValue1


-- EntityKeyValue1: '1049' (Type = Int32, IsNullable = false)

-- Executing at 26/03/2015 12:01:44 PM +10:00

-- Completed in 0 ms with result: SqlDataReader



Closed connection at 26/03/2015 12:01:44 PM +10:00

Opened connection at 26/03/2015 12:01:44 PM +10:00

SELECT 
    [Extent1].[ID] AS [ID], 
    [Extent1].[ModifiedBy] AS [ModifiedBy], 
    [Extent1].[DateModified] AS [DateModified], 
    [Extent1].[AddedBy] AS [AddedBy], 
    [Extent1].[DateAdded] AS [DateAdded], 
    [Extent1].[Name] AS [Name], 
    [Extent1].[Description] AS [Description], 
    [Extent1].[Privacy] AS [Privacy]
    FROM [dbo].[Lessons] AS [Extent1]
    WHERE [Extent1].[ID] = @EntityKeyValue1

无论有多少类(class)/类(class)项目，这都会继续。

我认为这是因为 item 已经在内存中了，但我不确定。如果我将代码更改为:

Lessons = (from ci in db.CourseItems
           join cil in db.CourseItemLessons on ci.ID equals cil.CourseItemID
           join l in db.Lessons on cil.ObjectID equals l.ID
           where ci.CourseID == item.ID
           select new { ID = ci.ID, Name = l.Name }).ToList()
           .Select(p => new LessonsListItem() { ID = p.ID, Name = p.Name}).ToList()

我最终得到了预期的结果:

Opened connection at 26/03/2015 12:06:43 PM +10:00

SELECT 
    [Extent1].[ID] AS [ID], 
    [Extent3].[Name] AS [Name]
    FROM   [dbo].[CourseItems] AS [Extent1]
    INNER JOIN [dbo].[CourseItemLessons] AS [Extent2] ON [Extent1].[ID] = [Extent2].[CourseItemID]
    INNER JOIN [dbo].[Lessons] AS [Extent3] ON [Extent2].[ObjectID] = [Extent3].[ID]
    WHERE [Extent1].[CourseID] = @p__linq__0


-- p__linq__0: '7' (Type = Int32, IsNullable = false)

-- Executing at 26/03/2015 12:06:43 PM +10:00

-- Completed in 0 ms with result: SqlDataReader

我知道我可以只使用它，但我的意思是，如果他们要单独查询每一行作为带有 where 子句的选择，那么使用导航属性有什么用？

Answer 1

问题是 First() 是投影。如果我取出 First() 并将查询添加为 where 那么 IQueryable 就能正确解决。

var items = db.CourseContainers
   .Where(p => p.ID == id)
   .SelectMany(p => p.CourseItems)
   .SelectMany(p => p.CourseItemLessons)
   .Select(p => new LessonsListItem() {
      ID = p.CourseItemID,
      Name = p.Lesson.Name
   }).ToList()

下面有关于这个问题的更多信息。 Should I use Entity Framework navigation properties for querying rather than straight from the DataContext ICollections?