Entity Framework (4.2) HasRequired results in unexpected LEFT OUTER JOIN

Question

It appears that the Entity Framework (latest version from NuGet) may be ignoring the HasRequired configuration when constructing joins for Navigation Properties other than the first one defined.

For example, given a POCO object (Person) with the following configuration:

var person = modelBuilder.Entity<Person>();
person.ToTable("The_Peoples");
person.HasKey(i => i.Id);
person.Property(i => i.Id).HasColumnName("the_people_id");
person.HasRequired(i => i.Address)
    .WithMany()
    .Map(map => map.MapKey("address_id"));
person.HasRequired(i => i.WorkPlace)
    .WithMany()
    .Map(map => map.MapKey("work_place_id"));

I'm attempting to load a list of people with the following query:

myContext.Set<People>()
    .Include(o => o.Address)
    .Include(o => o.WorkPlace);

Entity Framework generates the following query:

FROM  [dbo].[The_Peoples] AS [Extent1]
INNER JOIN [dbo].[The_Addresses] AS [Extent2] ON [Extent1].[address_id] = [Extent2].[address_id]
LEFT OUTER JOIN [dbo].[The_Work_Places] AS [Extent3] ON [Extent1].[work_place_id] = [Extent3].[work_place_id]

Notice that the join to the *The_Addresses* table is an inner join (as expected), however, the subsequent join to the *The_Work_Places* is an outer join. Given that both the Address and WorkPlace properties are marked as required, I would expect both joins to be inner joins. I've also attempted marking the Address and WorkPlace properties with the Required attribute, but this had no effect.

Is this a bug or am I perhaps misconfiguring something? Suggestions?

In your db, is The_Peoples.work_place_id marked as non-null? If so, does it matter whether the join is outer or inner, since the column would never contain null values? — danludwig, Dec 16 '11 at 19:22
If you switch the order of the Includes, to make Workplace come first and Address second, does Address then get a LOJ and Workplace an IJ? — danludwig, Dec 16 '11 at 20:18
Switching the order of the includes didn't appear to change the behavior. — mindlessgoods, Dec 16 '11 at 21:12

score 7 · Accepted Answer · answered Dec 16 '11 at 20:14

Your model configuration is correct and I think it is not a bug but it is behaviour by design, but I cannot tell exactly what design. I've also seen that SQL in such queries. Only a few remarks:

The query you are seeing is not specific to EF 4.2. It would also occur for EF 4.1 and EF 4.0. But not for EF 1 (.NET 3.5). In EF 1 every Include, also the first, has been mapped to a LEFT OUTER JOIN, also for required relationships.
I think one cannot say that using an INNER JOIN is "correct" for required navigation properties and LEFT OUTER JOIN is wrong. From a mapping view point it doesn't matter what you use, given that the constraints in the database represent the relationships in the model correctly. For a required navigation property the FK column in the database must not be nullable and there must a constraint in the database which enforces that the FK refers to an existing row in the target table. If that is the case, every JOIN must return a row, no matter if you use INNER JOIN or LEFT OUTER JOIN.
What happens if model and database is "out of sync" regarding the relationships? Basically nonsense happens in both cases: If you use a LEFT OUTER JOIN and the FK is NULL in the DB or refers to a not existing row, you'd get an entity where the navigation property is null, violating the model definition that the property is required. Using an INNER JOIN is not better: You'd get no entity at all, a query result which is at least as wrong as the result with the LEFT OUTER JOIN, if not worse.
So, I think the change in .NET 4 to use INNER JOINs for some Includes has been made not because the SQL in EF 1 was wrong but to create better and more performant SQL. This change actually introduced a breaking change in that some queries returned other results now than they did in EF 1: http://thedatafarm.com/blog/data-access/ef4-breaking-change-ef4-inner-joins-affect-eager-loading-many-to-many/
My understanding is that this has been fixed and that the reason was that INNER JOINs in too many situations have been introduced for eager loading in EF 4. (Perhaps in this phase (beta/release candidate for EF 4) your query would have had two INNER JOINs.) The reply to that problem from the EF team: http://connect.microsoft.com/VisualStudio/feedback/details/534675/ef4-include-method-returns-different-results-than-ef1-include (bold highlight from me):

We are fixing the issue for .net 4 RTM. This was an unintended breaking change. We did not make an intended change where every left outer join produced by an Include became an inner join in .Net 4. But rather the optimization looked at the constraints in the EF metadata and tried to convert those left outer joins which could be safely converted to inner joins based on the constraints. We had a bug in the code where we were reasoning based on the constraints which resulted in more aggressive conversion than what the constraints implied. We have scaled back the optimization so that we convert left outer joins to inner joins only in the places where we are absolutely sure we can do it based on the constraints. We think we can improve this optimization a little more in the future. You will start seeing more left outer joins for some queries in RTM when compared to RC and Beta 2 but in most of these cases this is needed to return correct results.

So, the final release for EF 4 apparently reintroduced some more LEFT OUTER JOINs (compared to beta/release candidate) to avoid a breaking change like that.

Sorry, this is more a historical story than a real explanation why you get an INNER JOIN and then a LEFT OUTER JOIN. As said, it is not wrong to write the query this way - as it wouldn't be wrong to use two INNER JOINs or two LEFT OUTER JOINs. I guess that only the EF team can explain exactly why your query produces that specific SQL.

I'd recommend - if you don't experience serious performance problems - not to worry about that SQL (since the result you get is correct after all) and proceed. Not liking the SQL which EF creates ends up in writing either a lot of feature and change requests or in writing a lot of raw SQL queries or in abandoning EF at all.

Excellent write up, thank you for the detail. You're correct that given the not-null and foreign key constraints, the queries would yield the same results regardless of join type. In the case of the outer join it may result in a slightly less performant query, but it's not a show stopper by any means. — mindlessgoods, Dec 16 '11 at 21:06

score 0 · Answer 2 · edited Oct 23 '13 at 18:16

This is like a neckro but today I got this same issue...

the problem with this:

FROM  [dbo].[The_Peoples] AS [Extent1]
INNER JOIN [dbo].[The_Addresses] AS [Extent2] ON [Extent1].[address_id] = [Extent2].[address_id]
LEFT OUTER JOIN [dbo].[The_Work_Places] AS [Extent3] ON [Extent1].[work_place_id] = [Extent3].[work_place_id]

is when you are applying a filter that comes from [dbo].[The_Peoples], you spec it to apply the filters to the join, so it takes less time, but what we found (you can run the query plan to see this issue) is that it does the join to the full content of the tables, then later it applies the filter.. that makes it take a lot of extra time.. in our case it trigger a time out, a query that should take 1 to 3 seconds was taking 1+ minute

score 0 · Answer 3 · edited Mar 21 '19 at 08:12

0

Try to use WithRequiredDependent() instead of WithMany()

edited Mar 21 '19 at 08:12

tshimkus

1,173
2
17
24

answered Mar 21 '19 at 02:35

Ivan Gomes

1

Entity Framework (4.2) HasRequired results in unexpected LEFT OUTER JOIN

3 Answers3

Linked