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 :

Unicameral

Answer 1

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.

Imena

I'd swap the words Parent and Child because by convention a "Child" is on the "n" side of a 1:n relationship. – Stylite

Answer 2

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.

Oxy

I currently don't have any project which I can add [required] I just use linqpad and added the Include (Linq). the linqpad showed me Left jOin / Inner Join based on the nullity. and that's my question . why. – Unicameral

Which null-ability, how? – Oxy

Seems clear and obvious to me. Try to imagine the other combinations, esp Not Null and Left join -> invalid objects. – Oxy

Henk , After thinking about it again and again , it does seems logic. – Unicameral

There are cases where a LEFT JOIN is needed instead of an INNER JOIN. I have Company Enitity which has ContactDetails which has Address which has Region. Region is optional (nullable). Whenever I query for all companies including contact, address and regions I get only those whose address has a region. I want all Companies with null Region where there is not one. Why EF forces me to use INNER JOIN where LEFT JOIN is required? I don't want to use DefaultIfEmpty, create a new anonymous instance identical to my Company Entity and copy each property by hand, because Company is expected as a result – Jink

Answer 3

1. If the foreign key is nullable in the dependent entity, Include (ThenInclude) will generate LEFT JOIN

2. If the foreign key is NOT nullable in the dependent entity, Include (ThenInclude) will generate INNER JOIN

For more information, you can read this post: Entity Framework Core Include & ThenInclude Tip

Thalweg

Not entirely true. If the foreign key is NOT nullable in the dependent entity, but the query starts at the principal entity, there will be an outer join. It all falls into place if you see the ground rule: use INNER JOIN if it doesn't cause the query root to get filtered. – Stylite