实体框架（4.2）HasRequired导致意外的LEFT OUTER JOIN [英] Entity Framework (4.2) HasRequired results in unexpected LEFT OUTER JOIN

问题描述

似乎实体框架（来自NuGet的最新版本）在为导航属性构建连接时可能忽略HasRequired配置，而不是第一个定义的导航属性。

It appears that the Entity Framework (latest version from NuGet) may be ignoring the HasRequired configuration when constructing joins for Navigation Properties other than the first one defined.

For例如，给定具有以下配置的POCO对象（Person）：

For example, given a POCO object (Person) with the following configuration:

var person = modelBuilder.Entity<Person>();
person.ToTable("The_Peoples");
person.HasKey(i => i.Id);
person.Property(i => i.Id).HasColumnName("the_people_id");
person.HasRequired(i => i.Address)
    .WithMany()
    .Map(map => map.MapKey("address_id"));
person.HasRequired(i => i.WorkPlace)
    .WithMany()
    .Map(map => map.MapKey("work_place_id"));

我正在尝试使用以下查询加载人员列表：

I'm attempting to load a list of people with the following query:

myContext.Set<People>()
    .Include(o => o.Address)
    .Include(o => o.WorkPlace);

实体框架生成以下查询：

Entity Framework generates the following query:

FROM  [dbo].[The_Peoples] AS [Extent1]
INNER JOIN [dbo].[The_Addresses] AS [Extent2] ON [Extent1].[address_id] = [Extent2].[address_id]
LEFT OUTER JOIN [dbo].[The_Work_Places] AS [Extent3] ON [Extent1].[work_place_id] = [Extent3].[work_place_id]

请注意，* The_Addresses *表的连接是内连接（如预期的那样），但是，* The_Work_Places *的后续连接是外连接加入。鉴于Address和WorkPlace属性都标记为必需，我希望两个连接都是内连接。我还尝试使用Required属性标记Address和WorkPlace属性，但这没有效果。

Notice that the join to the *The_Addresses* table is an inner join (as expected), however, the subsequent join to the *The_Work_Places* is an outer join. Given that both the Address and WorkPlace properties are marked as required, I would expect both joins to be inner joins. I've also attempted marking the Address and WorkPlace properties with the Required attribute, but this had no effect.

这是一个错误还是我可能错误配置了什么？建议？

Is this a bug or am I perhaps misconfiguring something? Suggestions?

推荐答案

您的模型配置是正确的，我认为这不是一个错误，但它是设计行为，但我不能准确地说出什么设计。我也在这样的查询中看到过SQL。只有几点评论：

Your model configuration is correct and I think it is not a bug but it is behaviour by design, but I cannot tell exactly what design. I've also seen that SQL in such queries. Only a few remarks:

• 您看到的查询并非特定于EF 4.2。 EF 4.1和EF 4.0也会出现这种情况。但对于EF 1（.NET 3.5），不。在EF 1中，每个包括，也是第一个，已经映射到 LEFT OUTER JOIN ，也用于所需的关系。

• The query you are seeing is not specific to EF 4.2. It would also occur for EF 4.1 and EF 4.0. But not for EF 1 (.NET 3.5). In EF 1 every Include, also the first, has been mapped to a LEFT OUTER JOIN, also for required relationships.

我认为不能说使用 INNER JOIN 对于所需的导航属性是正确的 LEFT OUTER JOIN 错误。鉴于数据库中的约束正确表示模型中的关系，从映射视点来看，使用什么并不重要。对于必需的导航属性，数据库中的FK列不能为空，并且数据库中必须有一个约束，强制FK引用目标表中的现有行。如果是这种情况，每个 JOIN 必须返回一行，无论你使用 INNER JOIN 还是 LEFT OUTER JOIN 。

I think one cannot say that using an INNER JOIN is "correct" for required navigation properties and LEFT OUTER JOIN is wrong. From a mapping view point it doesn't matter what you use, given that the constraints in the database represent the relationships in the model correctly. For a required navigation property the FK column in the database must not be nullable and there must a constraint in the database which enforces that the FK refers to an existing row in the target table. If that is the case, every JOIN must return a row, no matter if you use INNER JOIN or LEFT OUTER JOIN.

如果模型和数据库关系不同步，会发生什么？在两种情况下基本上都是无意义的：如果你在数据库中使用 LEFT OUTER JOIN 并且FK是 NULL 对于不存在的行，您将获得导航属性为 null 的实体，违反了该属性所需的模型定义。使用 INNER JOIN 并不是更好：你根本就没有实体，查询结果至少与的结果一样错误LEFT OUTER JOIN ，如果不是更糟。

What happens if model and database is "out of sync" regarding the relationships? Basically nonsense happens in both cases: If you use a LEFT OUTER JOIN and the FK is NULL in the DB or refers to a not existing row, you'd get an entity where the navigation property is null, violating the model definition that the property is required. Using an INNER JOIN is not better: You'd get no entity at all, a query result which is at least as wrong as the result with the LEFT OUTER JOIN, if not worse.

所以，我认为.NET 4的变化要使用 INNER JOIN s for some Include s不是因为EF 1中的SQL错误，而是创建更好且性能更高的SQL。这一变化实际上引入了一个突破性的变化，一些查询现在返回其他结果，而不是它们在EF 1中所做的： http://thedatafarm.com/blog/data-access/ef4-breaking-change-ef4-inner-joins-affect-eager-loading-many -to-many /

So, I think the change in .NET 4 to use INNER JOINs for some Includes has been made not because the SQL in EF 1 was wrong but to create better and more performant SQL. This change actually introduced a breaking change in that some queries returned other results now than they did in EF 1: http://thedatafarm.com/blog/data-access/ef4-breaking-change-ef4-inner-joins-affect-eager-loading-many-to-many/

我的理解是这已经修复，原因是 INNER JOIN 在很多情况下已经引入了EF 4中的热切加载。（也许在这个阶段（EF 4的测试版/候选版）你的查询将有两个 INNER JOIN s。）EF团队对该问题的回复： http://connect.microsoft.com/VisualStudio/feedback/details/534675/ef4-include-method-returns-different-results-than-ef1-include （大胆突出我） ：

My understanding is that this has been fixed and that the reason was that INNER JOINs in too many situations have been introduced for eager loading in EF 4. (Perhaps in this phase (beta/release candidate for EF 4) your query would have had two INNER JOINs.) The reply to that problem from the EF team: http://connect.microsoft.com/VisualStudio/feedback/details/534675/ef4-include-method-returns-different-results-than-ef1-include (bold highlight from me):

我们正在修复.net 4 RTM的问题。这是一次无意的
突破性变化。 我们没有做出预期的更改，其中包含Include生成的每个左
外连接成为.Net 4中的内连接。但是
而不是优化查看了EF元数据
并尝试转换那些左外连接，可以安全地根据约束将
转换为内连接。我们在
代码中遇到了一个错误，我们根据约束条件推断出
的积极转换比约束所暗示的要多。 我们
缩减了优化，以便我们将左外连接
转换为内连接仅在我们绝对确定我们可以根据约束进行
的地方。 / strong>我们认为我们可以在未来稍微改善这个
优化。 与RC和Beta
2相比，RTM中的某些查询会开始看到更多
左外连接，但在大多数情况下，需要返回正确的结果。

因此，EF 4的最终版本显然重新引入了一些 LEFT OUTER JOIN s（与测试版/候选版本相比）以避免这种突破性变化。

So, the final release for EF 4 apparently reintroduced some more LEFT OUTER JOINs (compared to beta/release candidate) to avoid a breaking change like that.

对不起，这更像是一个历史故事而不是一个真实的解释为什么你得到 INNER JOIN 然后一个 LEFT OUTER JOIN 。如上所述，以这种方式编写查询并没有错 - 因为使用两个 INNER JOIN s或两个 LEFT OUTER是不错的JOIN 秒。我想只有EF团队才能解释为什么你的查询产生了特定的SQL。

Sorry, this is more a historical story than a real explanation why you get an INNER JOIN and then a LEFT OUTER JOIN. As said, it is not wrong to write the query this way - as it wouldn't be wrong to use two INNER JOINs or two LEFT OUTER JOINs. I guess that only the EF team can explain exactly why your query produces that specific SQL.

我建议 - 如果你没有遇到严重的性能问题 - 不要担心这个SQL（因为你得到的结果毕竟是正确的）并继续。不喜欢EF创建的SQL，最终会编写大量的功能和更改请求，或者编写大量原始SQL查询或者完全放弃EF。

I'd recommend - if you don't experience serious performance problems - not to worry about that SQL (since the result you get is correct after all) and proceed. Not liking the SQL which EF creates ends up in writing either a lot of feature and change requests or in writing a lot of raw SQL queries or in abandoning EF at all.

这篇关于实体框架（4.2）HasRequired导致意外的LEFT OUTER JOIN的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！