EF - 可选 - 左外加? [英] EF - WithOptional - Left Outer Join?
问题描述
使用以下一对一型号,具有导航属性: -
public class Foo
{
public int Id {get;组; }
public virtual Bar Bar {get;组; }
}
public class Bar
{
public int Id {get;组; }
public virtual Foo Foo {get;组;
}
Foo 有一个可选的栏
酒吧具有必需的 Foo 。
我在栏之间有以下映射: -
HasRequired(x => ; x.Foo)
.WithOptional(x => x.Bar)
.Map(x => x.MapKey(FooId));哪些在名为FooId的栏表创建外键。
除了它为 Foo 生成带有左外连接到栏的sql 对于所有查询,当它不需要时。
SELECT ..
[Extent2]。[Id] AS [Id1]
FROM [dbo]。[Foo] AS [Extent1]
LEFT OUTER JOIN [dbo]。[Bar] AS [Extent2] ON [Extent1]。[Id] = [Extent2]。[FooId]
靠近它只返回酒吧的ID。
搜索堆栈我可以看到大多数建议使用 .WithMany
而不是 .WithOptional
,但我需要导航属性。
任何建议?
解决方案这是一对一FK关联的标准行为,无法避免。什么EF是内部维护一个隐藏(阴影)属性,如 int? BarId
为 Foo
实体。
摆脱 LEFT OUTER JOIN
s并保持双向导航属性,如果您可以承担变更 c (表)使用(默认EF一对一模型)共享主键关联,通过基本上从流畅的配置中删除 Map
调用: p>
HasRequired(x => x.Foo)
.WithOptional(x => x.Bar);
在这个模型中, Bar
包含 FooId
FK列,而是PK列 Id
将不再是身份,也将作为FK引用 Foo
表。
这允许EF不需要在 Foo $ c $中维护 BarId
c>因为它知道如果有相应的 Bar
,那将是 Id
与 Foo.Id
。
如果你不能更改数据库设计,那么你没有运气 - 你必须要 LEFT OUTER JOIN
s,或牺牲 Foo.Bar
导航属性,并将关系配置为单向一对多正如你已经提到的那样。
With the following one-to-one models, both with navigation properties:-
public class Foo
{
public int Id { get; set; }
public virtual Bar Bar { get; set; }
}
public class Bar
{
public int Id { get; set; }
public virtual Foo Foo { get; set; }
}
Foo has an optional Bar.
Bar has a required Foo.
I have the following mapping on Bar:-
HasRequired(x => x.Foo)
.WithOptional(x => x.Bar)
.Map(x => x.MapKey("FooId"));
Which creates the foreign key on the Bar table named 'FooId'.
All this works fine, except it generates the sql for Foo with a 'Left Outer Join' to Bar on all queries when its not needed.
SELECT ..
[Extent2].[Id] AS [Id1]
FROM [dbo].[Foo] AS [Extent1]
LEFT OUTER JOIN [dbo].[Bar] AS [Extent2] ON [Extent1].[Id] = [Extent2].[FooId]
Looking closer it only returns the Bar's Id.
Searching stack I can see most suggestions to use .WithMany
instead of .WithOptional
, but I need the navigation properties.
Any suggestions?
解决方案 This is the standard behavior for one-to-one FK association and cannot be avoided. What EF does is to maintain internally a hidden (shadow) property like int? BarId
for the Foo
entity.
The only way to get rid of LEFT OUTER JOIN
s and keep bidirectional navigation properties is if you can afford changing the Bar
db model (table) to use the (default EF one-to-one model) Shared Primary Key Association by basically removing the Map
call from the fluent configuration:
HasRequired(x => x.Foo)
.WithOptional(x => x.Bar);
In this model the Bar
table will not contain FooId
FK column, but instead the PK column Id
will no more be identity and will also serve as FK referencing Foo
table.
This allows EF to not care maintaining a BarId
in Foo
since it knows that if there is corresponding Bar
, it would be with Id
same as Foo.Id
.
If you can't change the database design, then you are out of luck - you have to either live with LEFT OUTER JOIN
s, or sacrifice the Foo.Bar
navigation property and configure the relationship as unidirectional one-to-many as you already mentioned.
这篇关于EF - 可选 - 左外加?的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!