Entity framework Include command - Left or inner join?

.net-4.0 c# entity-framework linq linqpad


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

5/23/2017 12:34:14 PM

Accepted Answer

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.

9/15/2013 7:18:19 AM

Popular Answer

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)

            .HasOptional(a => a.Child) /* LEFT OUTER JOIN */
            .HasForeignKey(a => a.ChildId);

is translated as a LEFT OUTER JOIN whereas

        .HasRequired(a => a.Child) /* INNER JOIN */
        .HasForeignKey(a => a.ChildId);

is translated as an INNER JOIN.

Related Questions


Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow
Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow