EF Include always produces INNER JOIN for the first Navigation Property

Question

I'm using the Code First approach and have the following Model:

public class Person
{
    public int ID {get; set;}
    public string Name {get; set;}

    public int CurrentStationID {get; set;}
    public virtual Station CurrentStation {get; set;}

    public int CurrentTransportationID {get; set;}
    public virtual Transportation CurrentTransporation {get; set;}
}

And the following code in my controller:

Models.Person model = myDBContext.Persons
                        .Include("CurrentStation")
                        .Include("CurrentTransportation")
                        .Where(p => p.ID == 1)
                        .FirstOrDefault();

"model" is going to be NULL even though a row with (1, "Testname", 0, 0) exists in the DB table "persons".

The above generates a SQL-statement with [...] INNER JOIN stations AS [...] LEFT JOIN transportations [...]. It always does an INNER JOIN for the first navigational property, for all my models, regardless what the underlying table/type is or in which order I specify them, it's always the first Include().

I do not want an INNER JOIN but a LEFT JOIN on all of them, since "CurrentStation" is not required to be in the database.

Why does EF do this??? I don't understand but I want to understand. I have not specified a [Required]-attribute on any of the properties, yet it behaves as if I did.

Unfortunately there's no [Optional]-attribute and doing a "HasOptional()" via the OnModelCreate-override is not an option for me.

Answer 1

If I am not mistaken, in order to allow a property to be optional or in the database sense, nullable, you must make the property nullable. So in the case of your CurrentStationId property you can try declaring it as follows:

 public int? CurrentStationId {get;set;}

UPDATE This post may be of some help for your situation.

Answer 2

What should bug you in the first place is design of your database. If you can have CurrentStationId = 0 and in the same time there is no Station with Id = 0 it means that you database doesn't use referential integrity.

Referential integrity is prerequisite for using EF. If you want to have relation between entities make sure that such relation exist in the database and that referential constraint is enforced otherwise I believe you will meat many other unexpected behaviors anyway.

If you can have person without Station the only correct approach is to have the CurrentStationId column and property nullable. Because EF believes that constraints are enforced in the database (and you cannot turn it off) it can use INNER JOIN and you cannot change it without making the FK nullable. If you cannot change this in the database don't map relations in EF - or better don't use EF.

If you have control over database and moreover if you designed it this way you should stop coding and return to a white board to think about the design and improve it to follow database design best practices. It will save you a lot of problems which you can have in the future.