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.

What bugs me the most is that EF thinks "CurrentStation" MUST be there, yet "CurrentTransportation" doesn't? They're defined the same way in the model, so where did EF get the idea "CurrentStation" has to be there? — Alex K., Jun 15 '12 at 17:04

score 8 · Accepted Answer · edited May 23 '17 at 11:52

8

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.

edited May 23 '17 at 11:52

Community

1
1

answered Jun 15 '12 at 17:10

mreyeros

4,359
20
24

Oh yeah, I forgot to say that this also is unfortunately not an option for me :( – Alex K. Jun 15 '12 at 17:12
I agree this is in the database-sense. But what just bugs me is the fact that CurrentTransportation is being treated as optional by EF. So, in case I ever DO want both of those properties to be non-optional, they won't. – Alex K. Jun 15 '12 at 17:18
Thank you for the update. I already read through multiple posts about this and that one came up too. I don't know how to use DefaultIfEmpty() though. As you can see I'm not writing my stuff in LINQ directly. This still doesn't explain why only the first is treated as required, not subsequent Includes. This just doesn't make sense to me :'( – Alex K. Jun 15 '12 at 17:22
2

@AlexK.: You apparently don't have *enforced* the foreign key relationship in the DB (FK is `0` (not `NULL`!) but there is no related row with PK = `0`), like here: http://stackoverflow.com/questions/7639417/entity-framework-and-forced-inner-join . If you can't change this situation you can stop using Entity Framework to map your database. EF will create an INNER JOIN if the relationship is *required* in the model (and it is required as long as your PK property is not nullable) and you can't change this behaviour because EF expects that the relationship is enforced. – Slauma Jun 15 '12 at 21:50
Yes, I understand. I'm just confused as to why EF expects only the first Include-property to be enforced and not the subsequent ones... they're also not nullable but 0, yet EF left joins them. For now I just removed all Include()-calls in my code where I know the underlying property is optional. – Alex K. Jun 16 '12 at 08:33
2

@AlexK.: I forgot that we had a similar question and answer about this in the past: http://stackoverflow.com/a/8539616/270591 . The answer is not really satisfying but it indicates that the type of JOINS for Include is apparently a complex subject, has a long history and experienced some changes from EF version to version. – Slauma Jun 16 '12 at 11:53

score 2 · Answer 2 · answered Jun 16 '12 at 15:52

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.

EF Include always produces INNER JOIN for the first Navigation Property

2 Answers2

Linked