EF - WithOptional - Left Outer Join?

Question

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?

Answer 1

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 JOINs 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 JOINs, or sacrifice the Foo.Bar navigation property and configure the relationship as unidirectional one-to-many as you already mentioned.