Entity framework Include command - Left or inner join?

Question

As I was investigating the difference between Include and Join I found that :

If the DB does not include a Foreign Keys -it has no navigation props so it's better to use Join

If It does have a navigation props - then use Include. ( it also save a db hit.)

But one answer here caught my attention:

Include is implemented as a join. Depending on the nullability of the included link it is an inner or left join.

Question :

How does the nullity affects the left / inner join ?

In Sql server I can have a Cities table and Persons table and a person can have a NULL CityID.

Why does entity Framework decides for me what kind of join it is ?

edit : visualization :

Now lets change CityId to not null :

And here is the change :

Answer 1

Suppose that in your class there is a [Required] constraint on City or CityID. And suppose there are Person records without a (valid) City. The only way to satisfy the [Required] is to perform an inner join.

But as long as the constraints in your Db and model match (ie CityID INT NOT NULL) it wouldn't really matter what kind of Join is used. That should be the normal case.

And without the constraint you would of course expect a Left Join.

Answer 2

I know this is an old question, but if anyone else lands here using EF Code First as I am, my issue was in the fluent mappings:

    protected override void OnModelCreating(DbModelBuilder modelBuilder)     {         base.OnModelCreating(modelBuilder);          modelBuilder.Entity<Parent>()             .HasOptional(a => a.Child) /* LEFT OUTER JOIN */             .WithMany()             .HasForeignKey(a => a.ChildId);     } 

is translated as a LEFT OUTER JOIN whereas

    modelBuilder.Entity<Parent>()         .HasRequired(a => a.Child) /* INNER JOIN */         .WithMany()         .HasForeignKey(a => a.ChildId); 

is translated as an INNER JOIN.