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.