LINQ-to-Entities Include 的奇怪行为

Question

我意识到我并不完全理解 LINQ-to-Entities 中的 Include 方法。

例如，采用下面的两个代码片段。我希望它们产生相同的输出（尽管第一个版本可能更有效，因为它避免了 JOIN ）。

// Snippet 1
using (var db = new Db()) {
  var author = db.Authors.First();
  db.LoadProperty<Author>(author, o => o.Books);
  foreach (var book in author.Books) {
    db.LoadProperty<Book>(book, o => o.Editions);
    foreach (var edition in book.Editions)
      Response.Write(edition.Id + " - " + edition.Title + "<br />");
  }
}

Response.Write("<br />");

// Snippet 2
using (var db = new Db()) {
  var author = db.Authors.Include("Books.Editions").First();
  foreach (var book in author.Books) {
    foreach (var edition in book.Editions)
      Response.Write(edition.Id + " - " + edition.Title + "<br />");
  }
}

但每个代码段的输出都不同：

1 - Some Book First Edition
2 - Another Book First Edition
3 - Another Book Second Edition
4 - Another Book Third Edition

8 - Some Book First Edition
9 - Another Book First Edition

第一个代码段正确输出 {Edition Id} - {Edition Title}，而第二个代码段意外打印 {Book Id} - {Edition Title}< /code> 并且只给出每本书的第一版。

这是怎么回事？有没有办法使用 Include 来实现所需的输出？

编辑1：MySql数据看起来像（更正）：

Authors         = { { Id = 1, Name = "Some Author" } }

Books           = { { Id = 8, AuthorId = 1 },
                    { Id = 9, AuthorId = 1 } }

Editions        = { { Id = 1, Title = "Some Book First Edition" },
                    { Id = 2, Title = "Another Book First Edition" },
                    { Id = 3, Title = "Another Book Second Edition" },
                    { Id = 4, Title = "Another Book Third Edition" } }

EditionsInBooks = { { BookId = 8, EditionId = 1 },
                    { BookId = 9, EditionId = 2 },
                    { BookId = 9, EditionId = 3 },
                    { BookId = 9, EditionId = 4 } }

请注意，没有Edition与Id = 8或Id = 9。

上面的代码是我的完整代码，在 Page_Load 中用于空测试页。

编辑2：我已经测试了以下内容，它们没有什么区别：

1. varauthor = db.Authors.Include("Books.Editions").AsEnumerable().First( );
2. varauthor = db.Authors.Include("Books.Editions").Single(o => o.Id == 1);
3. varauthor = db.Authors.Include("Books").Include("Books.Editions").First();

编辑 3：如果我启用延迟加载，则以下工作（在代码片段中） 2)：（

var author = db.Authors.First();

我认为这本质上与代码片段 1 的作用相同。）

但是，无论延迟加载如何，这仍然会返回奇怪的输出：

var author = db.Authors.Include("Books.Editions").First();

编辑 4：我真的很抱歉，但是我歪曲了上面的表结构。 （我正在经历这样的日子。）现在已更正，以显示多对多关系。请参阅编辑 1。

的输出

((ObjectQuery)db.Authors.Include("Books.Editions").AsEnumerable())
  .ToTraceString()

此外，考虑到我的 MySql 字段都不可为空，CASE语句

SELECT
  `Project1`.`Id`,
  `Project1`.`Name`,
  `Project1`.`C2` AS `C1`,
  `Project1`.`id1`,
  `Project1`.`AuthorId`,
  `Project1`.`C1` AS `C2`,
  `Project1`.`id2`,
  `Project1`.`Title`
FROM (SELECT
  `Extent1`.`Id`,
  `Extent1`.`Name`,
  `Join2`.`Id` AS `id1`,
  `Join2`.`AuthorId`,
  `Join2`.`Id` AS `id2`,
  `Join2`.`Title`,
   CASE WHEN (`Join2`.`Id` IS NULL) THEN (NULL)
        WHEN (`Join2`.`BookId` IS NULL) THEN (NULL)
        ELSE (1) END AS `C1`,
   CASE WHEN (`Join2`.`Id` IS NULL) THEN (NULL)
        ELSE (1) END AS `C2`
   FROM `authors` AS `Extent1`
     LEFT OUTER JOIN (SELECT
       `Extent2`.`Id`,
       `Extent2`.`AuthorId`,
       `Join1`.`BookId`,
       `Join1`.`EditionId`,
       `Join1`.`Id` AS `Id1`,
       `Join1`.`Title`
       FROM `books` AS `Extent2`
       LEFT OUTER JOIN (SELECT
         `Extent3`.`BookId`,
         `Extent3`.`EditionId`,
         `Extent4`.`Id`,
         `Extent4`.`Title`
         FROM `editionsinbooks` AS `Extent3`
         INNER JOIN `editions` AS `Extent4`
           ON `Extent4`.`Id` = `Extent3`.`EditionId`) AS `Join1`
       ON `Extent2`.`Id` = `Join1`.`BookId`) AS `Join2`
     ON `Extent1`.`Id` = `Join2`.`AuthorId`) AS `Project1`
   ORDER BY
     `Project1`.`Id` ASC,
     `Project1`.`C2` ASC,
     `Project1`.`id1` ASC,
     `Project1`.`C1` ASC

也很有趣。

Answer 1

实体框架提供程序在编译 LINQ 语句中的 First() 表达式时可能存在错误。当涉及 Include 时，偶尔会出现奇怪的情况： http://wildermuth.com/ 2008/12/28/Caution_when_Eager_Loading_in_the_Entity_Framework

尝试将第二个片段重写为：

using (var db = new Db()) {
    var author = db.Authors.Include("Books.Editions").AsEnumerable().First();
    foreach (var book in author.Books)
    {
        foreach (var edition in book.Editions)
        {
            Response.Write(edition.Id + " - " + edition.Title + "<br />");
        }
    }
}

如果这修复了您的输出，那么它肯定是 First() 方法。

编辑：关于你的第三次编辑与代码片段 1 做同样的事情，你是正确的。我无法理解该 include 语句如何使事情变得如此严重。我唯一可以鼓励的是查看它生成的 SQL 查询：

var sql = ((System.Data.Objects.ObjectQuery)db.Authors.Include("Books.Editions").AsEnumerable().First()).ToTraceString();

编辑 2： 考虑到生成的疯狂 sql 输出，问题很可能出在 EF 的 MySQL 提供程序中。