如何使用MySQL Connector/NET在超过2深度的对象图上使用Entity Framework? [英] How can I use Entity Framework on an object graph past a depth of 2 with MySQL Connector / NET?
问题描述
以下是与Oracle确认的错误报告: http://bugs.mysql.com /bug.php?id=67183
Here is a confirmed bug report with Oracle: http://bugs.mysql.com/bug.php?id=67183
情况
在存储库中使用.Include链时，我注意到得到的结果很奇怪-大多数情况是所查询的返回值来自错误的字段(例如，名称最终会出现在说明中-但在数据库中的所有值都是正确的，它们只会在查询后显示错误).我更改了名称，以使关系更明显，但结构相同.我一直在为关联的CrewMember及其相对的Rank和Clearance获取错误的值.似乎如果CrewMember中的字段名称与Rank相同，则Rank中该字段的值将变为CrewMember中的值.例如，如果Rank具有描述，而CrewMember也具有描述，则CrewMember的Rank描述将是CrewMember的描述.
When using an .Include chain inside of my repository, I noticed that I was getting strange results - mostly that the values queried that were being returned were from the wrong fields (name would end up in description for example - but in the database all the values are correct, they only show up wrong after the query). I changed the names so the relationships are more obvious, but the structure is the same. 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.
当由于MySQL Connector/NET sql提供程序未能正确形成join语句而定义了相似的字段时，Entity Framework无法进行深度超过2的格式正确的查询.
Entity Framework fails to make well formed queries past a depth of 2 when there are similar fields defined as a result of the MySQL Connector/NET sql provider failing to properly form join statements.
定义
这是对数据库表进行建模的类定义.我正在使用带有实体框架4.1和MySQL Connector/NET版本6.5的C#ASP.NET MVC 3
This is a class definition which models a database table. I am using C# ASP.NET MVC 3 with the Entity Framework 4.1 and the MySQL Connector/NET version 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调用的代码.
This is the code which queries the database and has the query and .Include calls.
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调用是否格式正确?我错过了什么吗?
Are these .Include calls well formed? Did I miss something?
这很复杂，因此，如果您有任何问题，请在评论中让我知道，我会尽力澄清我可能遗漏的任何内容.
This is rather complex, so if you have any questions please let me know in comments and I will try to clarify anything I may have left out.
在使用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关系上的包含不会造成任何问题.但是，当钻探中存在多个关系时，似乎会出现此问题.为了渴望负载，必须进行钻孔.
Using include on 1-1 relationships poses no problem when "drilling down" in this fashion it seems. However, the issue seems to arise when there are 1-many relations as part of the drilling. The drilling is necessary in order to eager load.
第一个投影entity => entity.Ships.Select(s => s.CrewMembers，将返回与每艘船相关的CrewMembers列表.这样可以正确返回图形，其中港口包含一列船舶，每艘船舶都有一列船员.
The first projection, entity => entity.Ships.Select(s => s.CrewMembers, will return a list of CrewMembers which are related to each ship. This properly returns the graph where a harbor contains a list of ships, each with a list of crew members.
但是，第二个投影CrewMembers.Select(cm => cm.Rank实际上并不返回图形的正确部分.字段开始混合在一起，并且任何具有相同名称的字段都会出于任何原因默认为父字段.这导致结果不一致，更重要的是数据不正确.没有抛出错误的事实使情况变得更糟，因为只能通过运行时检查才能确定.
However, the second projection CrewMembers.Select(cm => cm.Rank, does not in fact return the proper piece of the graph. Fields begin to be mixed, and any fields sharing the same name will default for whatever reason to the parent field. This results in inconsistent results and more importantly bad data. The fact that no errors are thrown makes it worse, as this can only be determined through runtime inspection.
如果有一种方法可以从第一个投影中获得强类型的单个响应(而不是列表)，则可能不需要第二个投影.就目前而言，我认为问题在于返回列表的第一个预测.当第二次投影尝试基于该列表而不是从单个对象进行投影时，会引入逻辑错误.
If there were a way to somehow get a strongly typed single response (as opposed to a list) from the first projection, perhaps the second would not be necessary. As it is now, I believe that the issue lies in the first projection returning a list. When the second projection attempts to project based on that list instead of from a single object, the logical error is introduced.
如果不是CrewMembers是一个ICollection，而是仅一个CrewMember，则此嵌套投影实际上将返回正确的数据.但是，这是此问题的简化版本，不幸的是，几乎所有测试似乎都是在我为了解决此问题而回顾的各种博客，教程，帖子，文章和文档中进行的.
If, instead of CrewMembers being an ICollection, it was only one CrewMember, then this nested projection will in fact return the correct data. However, that is a simplified version of this problem and unfortunately it is what almost all testing seems to have been done on from the various blogs, tutorials, posts, articles, and documents which I reviewed trying to solve this issue.
推荐答案
编辑
下面的测试是使用SQL Server和SqlClient作为提供程序进行的.使用SQL Server无法重现该问题的事实提出了一个问题，即所使用的MySql提供程序是否存在错误，即为LINQ查询创建了错误的SQL.它看起来与此问题中的问题相同，其中SqlClient/SQL Server复制.
The test below was made with SQL Server and SqlClient as provider. The fact that the problem is not reproducable with SQL Server raises the question if the MySql provider you are using has a bug in that is creates incorrect SQL for your LINQ query. It looks like the same problem as in this question where the problem occured with a MySql provider as well and couldn't be reproduced with SqlClient/SQL Server.
我一直在为关联的CrewMember获取错误的值，并且 它们的相对等级和清除率.似乎有一个字段名称 在CrewMember中与Rank相同，然后是该字段的值 Rank中的值变为CrewMember中的值. 例如，如果排名 有一个描述，CrewMember也有描述，然后是Rank的描述 因为CrewMember就是CrewMember的描述.
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粗体测试了该示例，无法重现该问题:
I have tested the example in bold (with EF 4.3.1) and can't reproduce the problem:
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 =等级A" ，其他3名机组人员的情况也一样.但是我还没有.
According to your description in bold I should have: CrewMember 1 Description = "Rank A" and the same mess for the other 3 crew members. But I haven't this.
与您有错误的代码相比，我的测试程序中有什么区别吗?
Is something different in my test program compared to your code where you have the error?
修改
查询生成的SQL(请参见上面的源代码中的第var sqlString = query.ToString();行，以下是sqlString的内容)为:
The generated SQL for the query (see line var sqlString = query.ToString(); in source code above, the following is the content of sqlString) is:
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
这篇关于如何使用MySQL Connector/NET在超过2深度的对象图上使用Entity Framework?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
