Linq GroupJoin (join...into) results in INNER JOIN?

Question

I am referencing the accepted answer to this question: LINQ to SQL multiple tables left outer join

In my example, I need all of the Person records regardless if there is a matching Staff record.

I am using the following query (simplified for illustation's sake):

var result = from person in context.Person
                     join staffQ in context.Staff
                         on person.StaffID equals staffQ.ID into staffStaffIDGroup
                     from staff in staffStaffIDGroup.DefaultIfEmpty()
                     select new PersonModel()
                     {
                         ID = person.ID,
                         Fname = person.Fname,
                         Lname = person.Lname,
                         Sex = person.Sex,
                         Username = staff != null ? staff.Username : ""
                     };

However, contrary to my expectations, the query results in the following SQL with an INNER JOIN, which eliminates records I need in the the result set.

SELECT 
[Extent1].[ID] AS [ID], 
[Extent1].[fname] AS [fname], 
[Extent1].[lname] AS [lname], 
[Extent1].[sex] AS [sex], 
[Extent2].[username] AS [username]
FROM  [dbo].[Person] AS [Extent1]
INNER JOIN [dbo].[Staff] AS [Extent2] ON [Extent1].[StaffID] = [Extent2].[ID]

I thought that GroupJoin (or join...into) is supposed to get around this? I know I must have made a dumb mistake here, but I can't see it.

Answer 1

In general the query should generate left outer join.

But remember, this is EF, and it has additional information coming from the model. In this case looks like the StaffID property of Person is an enforced FK constraint to Stuff, so EF knows that there is always a corresponding record in Staff table, hence ignoring your left outer join construct and generates inner join instead.

Again, the model (properties, whether they are required or not, the relationships - required or not etc.) allows EF to perform similar smart decisons and optimizations.