CSharp - 如何在对象图上使用 Entity Framework,以过去的深度为 2的连接器/net?

  显示原文与译文双语对照的内容
0 0

以下是一个已经确认的Bug 报告: http://bugs.mysql.com/bug.php?id=67183

情景

在我的存储库中使用 .Include 链时,我注意到,我得到了奇怪的结果- 大部分查询结果都是从错误的字段( 名称将在描述中结束,但是在数据库中,所有值都是正确的,它们在查询之后才出现) 中返回的。 我改变了名称,所以关系更加明显,但是结构是相同的。 我一直为相关的CrewMember获取错误的值及其相对等级和清除。 如果在CrewMember中有一个字段 NAME 与相同,那么该字段在秩中的值就变成了CrewMember中的值。 例如如果Rank有一个描述,那么CrewMember也是如此,那么CrewMember的等级描述就是CrewMember的描述。

当 Entity Framework 连接器/net sql提供程序没有正确地形成 join 语句的结果时,无法在 2个深度内形成良好的表单查询。

磅定义

这是一个对数据库表进行建模的类定义。 我使用 C# ASP.NET 3与 Entity Framework 4.1和MySQL连接器/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; }
}

查询

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


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 电话格式好? 我 是不是 错过 了 什么?

这是相当复杂的,所以如果你有任何问题,请让我知道在评论,我将试图澄清任何我可能遗漏了。

我用 Entity Framework 怎样才能形成良好的查询过去一个对象图的深度 2当使用mysql连接器/净?

磅编辑

下面是生成的查询:


{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关系中使用包括在这种方式中,"向下钻取"在这种情况下似乎没有问题。 然而,当有 1-many 关系作为钻井的一部分时,这个问题就出现了。 为了 eager,钻孔是必要的。

第一个投影 entity => entity.Ships.Select(s => s.CrewMembers 将返回与每个ship相关的CrewMembers的List 。 这可以正确地返回一个港口包含船只 List的图,每个都有一个crew成员的List 。

然而第二个投影 CrewMembers.Select(cm => cm.Rank ,实际上 NOT 是否返回了图的正确部分。 字段开始混合,任何共享相同 NAME的字段都将默认为父字段的任何原因。 结果导致结果不一致,更重要的是。 没有抛出错误的事实会使它变得更糟糕,因为这只能通过运行时检查来确定。

如果有某种方法可以从第一个投影获得强类型的单个响应( 与 List 相反),那么第二个可能是 NOT 。 现在,我认为问题在于第一个投影返回 List 。 当第二次投影尝试基于 List 而不是单个对象进行投影时,会引入逻辑错误。

如果CrewMembers不是一个 ICollection,它只是一个 CrewMember,那么这个嵌套的投影实际上将返回正确的数据。 然而,这是这个问题的简化版本,不幸的是,它几乎是所有的测试。教程。文章。文章和文档所做的,我试图尝试解决这个问题。

时间: 原作者:

0 0

编辑

下面的测试是用 SQL Server 和 SqlClient 作为提供程序。 这一事实的问题不是 reproducable SQL Server 引发了一个问题如果你正在使用 MySql 提供者有 Bug 创建正确的sqllinq查询。 看起来同样的问题在 这个问题,这个问题发生与 MySql 提供者和无法复制 SqlClient/SQL 服务器。


我一直为相关的CrewMember获取错误的值及其相对等级和清除。 如果在CrewMember中有一个字段 NAME 与相同,那么该字段在秩中的值就变成了CrewMember中的值。 例如,如果等级描述,CrewMember也是如此,那么描述的排名 CrewMember CrewMember的描述。

我已经用粗体( 用 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();
 }
 }
 }
}

输出结果为:

enter image description here

根据你的粗体说明,我应该有: CrewMember 1描述="分级",其他 3组成员的mess 。 但我没有。

你的代码相比,我的测试程序有什么不同?

编辑

查询生成的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

原作者:
0 0

query.Include(entity => entity.Ships);
query.Include(entity => entity.Ships.Select(s => s.CrewMembers));
query.Include(entity => entity.Ships.Select(s => s.CrewMembers.Select(cm => cm.Rank)));
query.Include(entity => entity.Ships.Select(s => s.CrewMembers.Select(cm => cm.Clearance)));

首先你知道它必须是 query = query.Include(...).Include(...)

只要执行上 2个,就不需要第一个 2. Ships和CrewMembers都将从第二个 2装载。 你有没有试过?


//query.Include(entity => entity.Ships);
//query.Include(entity => entity.Ships.Select(s => s.CrewMembers));
query = query.Include(entity => entity.Ships.Select(s => s.CrewMembers.Select(cm => cm.Rank)))
. Include(entity => entity.Ships.Select(s => s.CrewMembers.Select(cm => cm.Clearance)));

另外,你总是可以启动sql探查器,以准确地查看发送到数据库的查询效率。 如果只运行 3rd 和 4th,那么我不会期望 Bug 能够交换图中不同对象的属性值。

原作者:
0 0

在使用 mysqlconnector/net时,无法在一个旅行中检索到一个旅行中的图。 请参见这里的Bug 报告 Orcale 。 必须做的是


DbSet<Harbor> dbSet = context.Set<Harbor>();
IQueryable<Harbor> query = dbSet;
query = query.Include(entity => entity.Ships.Select(s => s.CrewMembers));
var Harbor = query.ToList();
foreach (var S in Harbor.Ships)
{
 foreach (var CM in S.CrewMembers)
 {
 CM.Rank =//get Rank where RankId == CM.RankId
 CM.Clearance =//get Clearance where ClearanceId == CM.ClearanceId
 }
}

这里代码与示例一致,但显然只是一个示例,需要更好的实现才能实际运行。 这是我使用的方法,直到我能够重载或者改进 .Include 效率功能,才能在一个旅程中获得整个图。

但是,在多次旅行中获取数据是不理想的,它。

原作者:
...