c# - 如何使用 MySQL Connector/NET 在深度超过 2 的对象图上使用 Entity Framework？

Question

这里是 Oracle 的确认错误报告:http://bugs.mysql.com/bug.php?id=67183

情况

在我的存储库中使用 .Include 链时，我注意到我得到了奇怪的结果 - 大多数情况下，返回的查询值来自错误的字段(名称最终会在例如描述 - 但在数据库中所有值都是正确的，它们只会在查询后显示错误)。我更改了名称，使关系更加明显，但结构是一样的。我一直在为关联的 CrewMember 及其相对等级和许可获取错误的值。似乎如果在 CrewMember 中有一个字段名称与 Rank 相同，那么 Rank 中该字段的值将成为 CrewMember 中的值。例如，如果 Rank 有描述，CrewMember 也有描述，那么 CrewMember 的 Rank 描述就是 CrewMember 的描述。

当由于 MySQL 连接器/NET sql 提供程序未能正确形成 join 语句而定义了类似的字段时， Entity Framework 无法进行超过 2 的结构良好的查询。

定义

这是一个模拟数据库表的类定义。我将 C# ASP.NET MVC 3 与 Entity Framework 4.1 和 MySQL Connector/NET 6.5 版一起使用

public class Harbor
{
 public int HarborId { get; set; }
 public virtual ICollection<Ship> Ships { get; set; }
 public string Description { get; set; }
}

public class Ship
{
 public int ShipId { get; set; }
 public int HarborId { get; set; }
 public virtual Harbor Harbor { get; set; }
 public virtual ICollection<CrewMember> CrewMembers { get; set; }
 public string Description { get; set; }
} 

public class CrewMember
{
 public int CrewMemberId { get; set; }
 public int ShipId { get; set; }
 public virtual Ship Ship { get; set; }
 public int RankId { get; set; }
 public virtual Rank Rank { get; set; }
 public int ClearanceId { get; set; }
 public virtual Clearance Clearance { get; set; }
 public string Description { get; set; }
}

public class Rank
{
 public int RankId { get; set; }
 public virtual ICollection<CrewMember> CrewMembers { get; set; }
 public string Description { get; set; }
}

public class Clearance
{
 public int ClearanceId { get; set; }
 public virtual ICollection<CrewMember> CrewMembers { get; set; }
 public string Description { get; set; }
}

查询

这是查询数据库并具有查询和.Include 调用的代码。

DbSet<Harbor> dbSet = context.Set<Harbor>();
IQueryable<Harbor> query = dbSet;
query = query.Include(entity => entity.Ships);
query = query.Include(entity => entity.Ships.Select(s => s.CrewMembers));
query = query.Include(entity => entity.Ships.Select(s => s.CrewMembers.Select(cm => cm.Rank)));
query = query.Include(entity => entity.Ships.Select(s => s.CrewMembers.Select(cm => cm.Clearance)));

这些 .Include 调用格式正确吗？我错过了什么吗？

这相当复杂，所以如果您有任何问题，请在评论中告诉我，我会尽力澄清我可能遗漏的任何内容。

在使用 MySQL Connector/NET 时，如何使用 Entity Framework 在深度超过 2 的对象图上获取结构良好的查询？

编辑

这是生成的查询:

{SELECT
[Project1].[HarborId], 
[Project1].[Description], 
[Project1].[C2] AS [C1], 
[Project1].[ShipId], 
[Project1].[HarborId1], 
[Project1].[Description1], 
[Project1].[C1] AS [C2], 
[Project1].[CrewMemberId], 
[Project1].[ShipId1], 
[Project1].[ClearanceId], 
[Project1].[RankId], 
[Project1].[Description2], 
[Project1].[RankId1], 
[Project1].[Description3], 
[Project1].[ClearanceId1], 
[Project1].[Description4], 
FROM (SELECT
[Extent1].[HarborId], 
[Extent1].[Description], 
[Join3].[ShipId], 
[Join3].[HarborId] AS [HarborId1], 
[Join3].[Description]AS [Description1], 
[Join3].[CrewMemberId], 
[Join3].[ShipId]AS [ShipId1], 
[Join3].[ClearanceId], 
[Join3].[RankId], 
[Join3].[Description] AS [Description2], 
[Join3].[RankId] AS [RankId1], 
[Join3].[Description] AS [Description3], 
[Join3].[ClearanceId] AS [ClearanceId1], 
[Join3].[Description] AS [Description4], 
CASE WHEN ([Join3].[ShipId] IS  NULL) THEN (NULL)  WHEN ([Join3].[CrewMemberId] IS  NULL) THEN (NULL)  ELSE (1) END AS [C1], 
CASE WHEN ([Join3].[ShipId] IS  NULL) THEN (NULL)  ELSE (1) END AS [C2]
FROM [Harbor] AS [Extent1] LEFT OUTER JOIN (SELECT
[Extent2].[ShipId], 
[Extent2].[HarborId], 
[Extent2].[Description], 
[Join2].[CrewMemberId], 
[Join2].[ShipId] AS [ShipID1], 
[Join2].[ClearanceId], 
[Join2].[RankId], 
[Join2].[Description] AS [DESCRIPTION1], 
[Join2].[RankID1], 
[Join2].[DESCRIPTION1] AS [DESCRIPTION11], 
[Join2].[ClearanceID1], 
[Join2].[DESCRIPTION2], 
FROM [Ship] AS [Extent2] LEFT OUTER JOIN (SELECT
[Extent3].[CrewMemberId], 
[Extent3].[ShipId], 
[Extent3].[ClearanceId], 
[Extent3].[RankId], 
[Extent3].[Description], 
[Extent4].[RankId] AS [RankID1], 
[Extent4].[Description] AS [DESCRIPTION1], 
[Extent5].[ClearanceId] AS [ClearanceID1], 
[Extent5].[Description] AS [DESCRIPTION2], 
FROM [CrewMember] AS [Extent3] INNER JOIN [Rank] AS [Extent4] ON [Extent3].[RankId] = [Extent4].[RankId] LEFT OUTER JOIN [Clearance] AS [Extent5] ON [Extent3].[ClearanceId] = [Extent5].[ClearanceId]) AS [Join2] ON [Extent2].[ShipId] = [Join2].[ShipId]) AS [Join3] ON [Extent1].[HarborId] = [Join3].[HarborId]
 WHERE [Extent1].[HarborId] = @p__linq__0) AS [Project1]
 ORDER BY 
[Project1].[HarborId] ASC, 
[Project1].[C2] ASC, 
[Project1].[ShipId] ASC, 
[Project1].[C1] ASC}

澄清

当以这种方式“向下钻取”时，在 1-1 关系上使用 include 似乎没有问题。但是，当钻孔过程中存在一对多关系时，问题似乎就出现了。为了急切加载，钻孔是必要的。

第一个投影，entity => entity.Ships.Select(s => s.CrewMembers，将返回与每艘船相关的 CrewMembers 列表。这会正确返回图表，其中 a harbor 包含一个船舶列表，每个船舶都有一个船员列表。

然而，第二个投影 CrewMembers.Select(cm => cm.Rank，实际上并没有返回正确的图 block 。字段开始混合，任何共享相同的字段无论出于何种原因，名称都将默认为父字段。这会导致结果不一致，更重要的是会导致错误数据。没有抛出错误的事实使情况变得更糟，因为这只能通过运行时检查来确定。

如果有办法从第一个投影中以某种方式获得强类型的单一响应(而不是列表)，也许第二个投影就没有必要了。就像现在一样，我认为问题在于第一个投影返回列表。当第二个投影尝试基于该列表而不是单个对象进行投影时，就会引入逻辑错误。

如果 CrewMembers 不是一个 ICollection，而是只有一个 CrewMember，那么这个嵌套投影实际上会返回正确的数据。然而，这是这个问题的简化版本，不幸的是，几乎所有的测试似乎都是从我试图解决这个问题的各种博客、教程、帖子、文章和文档中进行的。

Answer 1

编辑

下面的测试是使用 SQL Server 和 SqlClient 作为提供程序进行的。如果您使用的 MySql 提供程序存在一个错误，即为您的 LINQ 查询创建了不正确的 SQL，则该问题无法通过 SQL Server 重现这一事实引发了一个问题。它看起来与 this question 中的问题相同MySql 提供程序也出现问题，并且无法使用 SqlClient/SQL Server 重现。

---

I keep getting the wrong values for the associated CrewMember and their relative Rank and Clearance. It seems if there is a field name which is the same in CrewMember as Rank, then the value of that field in Rank becomes what the value was in CrewMember. For example, if Rank had a description, and so did CrewMember, then the description of Rank for the CrewMember would be the CrewMember's description.

我已经测试了粗体示例(使用 EF 4.3.1)并且无法重现问题:

using System;
using System.Collections.Generic;
using System.Data.Entity;
using System.Linq;

namespace EFInclude
{
    public class Harbor
    {
        public int HarborId { get; set; }
        public virtual ICollection<Ship> Ships { get; set; }

        public string Description { get; set; }
    }

    public class Ship
    {
        public int ShipId { get; set; }
        public int HarborId { get; set; }
        public virtual Harbor Harbor { get; set; }
        public virtual ICollection<CrewMember> CrewMembers { get; set; }

        public string Description { get; set; }
    }

    public class CrewMember
    {
        public int CrewMemberId { get; set; }
        public int ShipId { get; set; }
        public virtual Ship Ship { get; set; }
        public int RankId { get; set; }
        public virtual Rank Rank { get; set; }
        public int ClearanceId { get; set; }
        public virtual Clearance Clearance { get; set; }

        public string Description { get; set; }
    }

    public class Rank
    {
        public int RankId { get; set; }
        public virtual ICollection<CrewMember> CrewMembers { get; set; }

        public string Description { get; set; }
    }

    public class Clearance
    {
        public int ClearanceId { get; set; }
        public virtual ICollection<CrewMember> CrewMembers { get; set; }

        public string Description { get; set; }
    }

    public class MyContext : DbContext
    {
        public DbSet<Harbor> Harbors { get; set; }
        public DbSet<Ship> Ships { get; set; }
        public DbSet<CrewMember> CrewMembers { get; set; }
        public DbSet<Rank> Ranks { get; set; }
        public DbSet<Clearance> Clearances { get; set; }
    }

    class Program
    {
        static void Main(string[] args)
        {
            Database.SetInitializer(new DropCreateDatabaseAlways<MyContext>());

            using (var context = new MyContext())
            {
                context.Database.Initialize(true);

                var harbor = new Harbor
                {
                    Ships = new HashSet<Ship>
                    {
                        new Ship
                        {
                            CrewMembers = new HashSet<CrewMember>
                            {
                                new CrewMember
                                {
                                    Rank = new Rank { Description = "Rank A" },
                                    Clearance = new Clearance { Description = "Clearance A" },
                                    Description = "CrewMember A"
                                },
                                new CrewMember
                                {
                                    Rank = new Rank { Description = "Rank B" },
                                    Clearance = new Clearance { Description = "Clearance B" },
                                    Description = "CrewMember B"
                                }
                            },
                            Description = "Ship AB"
                        },
                        new Ship
                        {
                            CrewMembers = new HashSet<CrewMember>
                            {
                                new CrewMember
                                {
                                    Rank = new Rank { Description = "Rank C" },
                                    Clearance = new Clearance { Description = "Clearance C" },
                                    Description = "CrewMember C"
                                },
                                new CrewMember
                                {
                                    Rank = new Rank { Description = "Rank D" },
                                    Clearance = new Clearance { Description = "Clearance D" },
                                    Description = "CrewMember D"
                                }
                            },
                            Description = "Ship CD"
                        }
                    },
                    Description = "Harbor ABCD"
                };

                context.Harbors.Add(harbor);
                context.SaveChanges();
            }

            using (var context = new MyContext())
            {
                DbSet<Harbor> dbSet = context.Set<Harbor>();
                IQueryable<Harbor> query = dbSet;
                query = query.Include(entity => entity.Ships);
                query = query.Include(entity => entity.Ships.Select(s => s.CrewMembers));
                query = query.Include(entity => entity.Ships.Select(s => s.CrewMembers.Select(cm => cm.Rank)));
                query = query.Include(entity => entity.Ships.Select(s => s.CrewMembers.Select(cm => cm.Clearance)));

                var sqlString = query.ToString();
                // see below for the generated SQL query

                var harbor = query.Single();

                Console.WriteLine("Harbor {0} Description = \"{1}\"",
                    harbor.HarborId, harbor.Description);
                foreach (var ship in harbor.Ships)
                {
                    Console.WriteLine("- Ship {0} Description = \"{1}\"",
                        ship.ShipId, ship.Description);
                    foreach (var crewMember in ship.CrewMembers)
                    {
                        Console.WriteLine("-- CrewMember {0} Description = \"{1}\"", 
                            crewMember.CrewMemberId, crewMember.Description);
                        Console.WriteLine("-- CrewMember {0} Rank Description = \"{1}\"",
                            crewMember.CrewMemberId, crewMember.Rank.Description);
                        Console.WriteLine("-- CrewMember {0} Clearance Description = \"{1}\"",
                            crewMember.CrewMemberId, crewMember.Clearance.Description);
                    }
                }

                Console.ReadLine();
            }
        }
    }
}

输出是:

根据你的粗体描述，我应该有:CrewMember 1 Description = "Rank A" 和其他 3 名机组成员一样的烂摊子。但是我没有这个。

与您出错的代码相比，我的测试程序有什么不同吗？

编辑

查询生成的SQL(参见上面源代码中的var sqlString = query.ToString();行，以下是sqlString的内容)为:

SELECT 
[Project1].[HarborId] AS [HarborId], 
[Project1].[Description] AS [Description], 
[Project1].[C2] AS [C1], 
[Project1].[ShipId] AS [ShipId], 
[Project1].[HarborId1] AS [HarborId1], 
[Project1].[Description1] AS [Description1], 
[Project1].[C1] AS [C2], 
[Project1].[CrewMemberId] AS [CrewMemberId], 
[Project1].[ShipId1] AS [ShipId1], 
[Project1].[RankId] AS [RankId], 
[Project1].[ClearanceId] AS [ClearanceId], 
[Project1].[Description2] AS [Description2], 
[Project1].[RankId1] AS [RankId1], 
[Project1].[Description3] AS [Description3], 
[Project1].[ClearanceId1] AS [ClearanceId1], 
[Project1].[Description4] AS [Description4]
FROM ( SELECT 
    [Extent1].[HarborId] AS [HarborId], 
    [Extent1].[Description] AS [Description], 
    [Join3].[ShipId1] AS [ShipId], 
    [Join3].[HarborId] AS [HarborId1], 
    [Join3].[Description1] AS [Description1], 
    [Join3].[CrewMemberId] AS [CrewMemberId], 
    [Join3].[ShipId2] AS [ShipId1], 
    [Join3].[RankId1] AS [RankId], 
    [Join3].[ClearanceId1] AS [ClearanceId], 
    [Join3].[Description2] AS [Description2], 
    [Join3].[RankId2] AS [RankId1], 
    [Join3].[Description3] AS [Description3], 
    [Join3].[ClearanceId2] AS [ClearanceId1], 
    [Join3].[Description4] AS [Description4], 
    CASE WHEN ([Join3].[ShipId1] IS NULL) THEN CAST(NULL AS int) WHEN ([Join3].[CrewMemberId] IS NULL) THEN CAST(NULL AS int) ELSE 1 END AS [C1], 
    CASE WHEN ([Join3].[ShipId1] IS NULL) THEN CAST(NULL AS int) ELSE 1 END AS [C2]
    FROM  [dbo].[Harbors] AS [Extent1]
    LEFT OUTER JOIN  (SELECT [Extent2].[ShipId] AS [ShipId1], [Extent2].[HarborId] AS [HarborId], [Extent2].[Description] AS [Description1], [Join2].[CrewMemberId], [Join2].[ShipId2], [Join2].[RankId1], [Join2].[ClearanceId1], [Join2].[Description2], [Join2].[RankId2], [Join2].[Description3], [Join2].[ClearanceId2], [Join2].[Description4]
        FROM  [dbo].[Ships] AS [Extent2]
        LEFT OUTER JOIN  (SELECT [Extent3].[CrewMemberId] AS [CrewMemberId], [Extent3].[ShipId] AS [ShipId2], [Extent3].[RankId] AS [RankId1], [Extent3].[ClearanceId] AS [ClearanceId1], [Extent3].[Description] AS [Description2], [Extent4].[RankId] AS [RankId2], [Extent4].[Description] AS [Description3], [Extent5].[ClearanceId] AS [ClearanceId2], [Extent5].[Description] AS [Description4]
            FROM   [dbo].[CrewMembers] AS [Extent3]
            INNER JOIN [dbo].[Ranks] AS [Extent4] ON [Extent3].[RankId] = [Extent4].[RankId]
            LEFT OUTER JOIN [dbo].[Clearances] AS [Extent5] ON [Extent3].[ClearanceId] = [Extent5].[ClearanceId] ) AS [Join2] ON [Extent2].[ShipId] = [Join2].[ShipId2] ) AS [Join3] ON [Extent1].[HarborId] = [Join3].[HarborId]
)  AS [Project1]
ORDER BY [Project1].[HarborId] ASC, [Project1].[C2] ASC, [Project1].[ShipId] ASC, [Project1].[C1] ASC