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

user_8537213 0
Viewed 2752

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?

entity-framework-core asp.net-core .net-core sql-server c#
2 Answers

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.

user_52025630

How about a where clause "t => t.Id == teamId && t.TeamSettings != null". This should ofcouse be implemented along with the Includes command. This way you force it to behave like an inner join.

user_106213230
Related