40

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 :

enter image description here

enter image description here

Now lets change CityId to not null :

enter image description here

And here is the change :

enter image description here

Community
  • 1
  • 1
Royi Namir
  • 144,742
  • 138
  • 468
  • 792

3 Answers3

16

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.

madannes
  • 523
  • 1
  • 8
  • 14
15

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.

H H
  • 263,252
  • 30
  • 330
  • 514
  • 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. – Royi Namir Sep 15 '13 at 07:36
  • Which null-ability, how? – H H Sep 15 '13 at 07:44
  • 2
    Seems clear and obvious to me. Try to imagine the other combinations, esp Not Null and Left join -> invalid objects. – H H Sep 15 '13 at 10:03
  • 1
    Henk , After thinking about it again and again , it does seems logic. – Royi Namir Sep 15 '13 at 11:16
  • 1
    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 – Thanasis Ioannidis Apr 07 '17 at 15:06
0

  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

Ramil Aliyev 007
  • 4,437
  • 2
  • 31
  • 47
  • 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. – Gert Arnold May 14 '23 at 18:25