Entity Framework - Why does EF use LEFT OUTER JOIN's in a 1-to-1 relationship?

Question

Why does .NET Entity Framework produce SQL that uses a subquery and left outer join on a simple 1-to-1 relationship? I expected to see a simple join on the two tables. I'm using Devart Dotconnect for Oracle. Any ideas?

Below is the output I see courtesy of the EFTracingProvider:

SELECT
1 AS C1,
"Join1".USER_ID1 AS USER_ID,
...
FROM  "MY$NAMESPACE".MYTABLE1 "Extent1"
INNER JOIN  (...
    FROM  "MY$NAMESPACE".MYTABLE2 "Extent2"
    LEFT OUTER JOIN "MY$NAMESPACE".MYTABLE1 "Extent3" ON "Extent2".OTHER_ID = "Extent3".OTHER_ID ) 
    "Join1" ON "Extent1".OTHER_ID = "Join1".OTHER_ID1
WHERE "Extent1".USER_ID = :EntityKeyValue1
-- EntityKeyValue1 (dbtype=String, size=6, direction=Input) = "000000"

Is it an optional 1-to-1 relationship? That might explain the left join. — Martin Schapendonk, Nov 20 '12 at 14:30
Yes, rephrasing what Martin asked, is your ForeignKey field (MyTable1.Other_ID) NULLable? — C.B., Jun 15 '13 at 07:28
EF uses outer joins when joining views even if the foreign key exists. Are you using views? — Brannon, Sep 24 '13 at 00:01

score 0 · Answer 1 · answered Sep 25 '13 at 22:12

0

In database theory, it is assumed that in a one-to-one relationship, A row on one side must be created before the corresponding row on the other side. If you didn't use an outer join, then you could never list the rows which had no match.

answered Sep 25 '13 at 22:12

SMerrill8

528
4
12

Entity Framework - Why does EF use LEFT OUTER JOIN's in a 1-to-1 relationship?

1 Answers1