EF Core Navigation Property Include uses Left Join instead of Inner Join

Question

In my API project, I have

public class Team
{
    [Key]
    public long Id { set; get; }
    [Required]
    public TeamSettings TeamSettings { get; set; }
}

public class TeamSettings
{
    [Key]
    [Required]
    [Column("TeamSettingsId")]
    public long Id { set; get; }
    [Required]
    [ForeignKey("TeamId")]
    public long TeamId { get; set; }
    [Required]
    public Team Team { set; get; }
}

When I use

var team = await TeamRepo.GetAsync(t => t.Id == teamId, includes);

I can see on my SQL Server Profiler a left join rather than an inner join.

I tried removing the annotations and going for fluent like this:

modelBuilder.Entity<Team>()
                    .HasOne(t => t.TeamSettings)
                    .WithOne(ts => ts.Team)
                    .HasForeignKey<TeamSettings>(ts => ts.TeamId);

But still, all I get is a left join.

Since TeamSettings is created always for any team and is not nullable, shouldn't it be using inner join?

Answer 1

Relational databases cannot enforce one-to-one (i.e. both ends required) relationship, because no standard FK constraint can prevent deleting the dependent record (TeamSettings in your case).

Hence EF Core does not support it ([Required] attribute on [Team.TeamSettings] is ignored). This is sort of explained in the Required and optional relationships section of the documentation:

You can use the Fluent API to configure whether the relationship is required or optional. Ultimately this controls whether the foreign key property is required or optional.

Since the FK is always at dependent side, technically it means that you can only control whether the dependent can exist w/o a principal. But principal can always exist w/o dependent.

Shortly, relationship dependents are always optional, hence the left join.