Entity Framework generates a second left join in a one-to-one (optional/required) relationship

Question

I have the following models in EF Code First:

public class A
{
    public int Id { get; set; }

    public virtual B { get; set; }
}

public class B
{
    public int Id { get; set; }

    public virtual A { get; set; }
}

I have defined the relationship as follows:

modelBuilder.Entity<A>().HasKey(entity => entity.Id);
modelBuilder.Entity<B>().HasKey(entity => entity.Id);
modelBuilder.Entity<A>()
    .HasOptional(entity => entity.B)
    .WithRequired(entity => entity.A);

When I write the following query:

var a = db.AItems.Include("B");

The query that is produced is as follows:

SELECT
[Extent1].[Id] AS [Id],
[Extent3].[Id] AS [Id1]
FROM [dbo].[As] AS [Extent1]
LEFT OUTER JOIN [dbo].[Bs] AS [Extent2] ON [Extent1].[Id] = [Extent2].[Id]
LEFT OUTER JOIN [dbo].[Bs] AS [Extent3] ON [Extent2].[Id] = [Extent3].[Id]

Why does Entity Framework have an additonal (useless) left join for this type of relationship?

Answer 1

When you have one-to-one relationships, even if you do not explicitly .Include the related entity, entity framework creates a join statement.

In your case, first join results from the .Include statement and the other left outer join is added by default. You can check this by removing include statement and observing the SQL output which will generate the "Required" left outer join statement (it is required for Entity Framework to verify the object model is correct).

Further, as of EF 6.4, there is no way to avoid duplicate joins if your query is:

Set<Parent>
  .Include(x => x.RequiredChild.OptionalGrandChild)
  .ToList();

...because, the XML documentation for .Include notes:

/// To include a reference and then a reference one level down: query.Include(e => e.Level1Reference.Level2Reference)

When this scenario occurs, it will generate the "required" join and then generate a join for the Level1Reference. It then needs to generate a join for:

1. The "required" joins Level2Reference
2. The Include Level2Reference

One workaround for this problem is to use the LINQ syntax and expose database keys as properties on your object model. When you do this, you effectively override the Object Navigation Syntax and manually tell EF how to build the join. Since it is not looking up how to build the join using your Fluent Object Model, it has no choice but to follow your join instructions. (This was the approximate explanation given to me by then Program Manager Diego Vega, when we filed several bugs regarding duplicate/unnecessary joins).