我应该使用实体框架导航属性来查询而不是直接从DataContext ICollections查询吗？

Question

我正试图在实体框架6中创建投影，并将这些投影映射到我的视图模型中。我关心的是在使用导航属性时发送的数据库连接和单独查询的数量。例如：

(我的根)

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()

我是拉所有课程项目与课程，然后投影到一个视图模型，也使用课程导航，以获得名称。我想我不明白实体框架是如何解析这个的。我希望这样的事情：

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语句，这是实际发送给db的内容。

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

无论有多少课程或课程项目，这种情况都将继续下去。

我想这是因为物品已经在记忆中了，但我不确定。如果我将代码更改为：

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子句作为select，那么使用导航属性有什么用处呢？

Answer 1

问题是，第一个()是预测。如果我取出第一个()并将查询添加为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?