c# 4.0 - Entity Framework and forced Inner Join

Question

I have Table1 with the following relationships (they are not enforced they only create the relationship for the navigation properties)

Table1 (*)->(1) Table2
Table1 (*)->(1) Table3
Table1 (*)->(1) Table4
Table1 (*)->(1) Table5

Using eager loading code looks like

IQueryable<Table1> query = context.Table1s;

query = query.Include(Table1 => Table1.Table2);
query = query.Include(Table1 => Table1.Table3);
query = query.Include(Table1 => Table1.Table4);
query = query.Include(Table1 => Table1.Table5);

query = query.Where(row => row.Table1Id == table1Id);

query.Single();

Every way I try to organize the Include() statements, the first table included has an Inner Join in its generated TSQL and the remaining are Left Outer Join (I expect Left Outer for all of them). I am not Entity Splitting, they are just plain tables with FKs.

If DefaultIfEmpty() is the only solution, can someone explain the reason why when all but the first table included provide the SQL expected?

My understanding is that default behavior for a Navigation Property is LEFT OUTER but I cannot get ALL properties to generate the default.

Any help would be MUCH appreciated.

Thank you in advance!

----- Created TSQL (modified for brevity but structure the same) -------

(@p__linq__0 int)SELECT 
[Limit1].[Table1Id] AS [Table1Id], 
[Limit1].[OtherData] AS [OtherData]
FROM ( SELECT TOP (2) 
    [Extent1].[Table1Id] AS [Table1Id], 
    [Extent1].[OtherData] As [OtherData]
    FROM       [dbo].[Table1] AS [Extent1]
    INNER JOIN [dbo].[Table2] AS [Extent2] ON [Extent1].[Table2Id] = [Extent2].[Table2Id]
    LEFT OUTER JOIN [dbo].[Table3] AS [Extent3] ON [Extent1].[Table3Id] = [Extent3].[Table3Id]
    LEFT OUTER JOIN [dbo].[Table4] AS [Extent4] ON [Extent1].[Table4Id] = [Extent4].[Table4Id]
    LEFT OUTER JOIN [dbo].[Table5] AS [Extent5] ON [Extent1].[Table5Id] = [Extent5].[Table5Id]
    WHERE [Extent1].[Table1Id] = @p__linq__0
)  AS [Limit1]

Answer 1

EF seems to use INNER JOIN for including a required and LEFT OUTER JOIN for including an optional navigation property. Example:

public class Order
{
    public int Id { get; set; }
    public string Details { get; set; }
    public Customer Customer { get; set; }
}

public class Customer
{
    public int Id { get; set; }
    public string Name { get; set; }
}

If I define Customer as a required property on Order...

public class MyContext : DbContext
{
    public DbSet<Order> Orders { get; set; }
    protected override void OnModelCreating(DbModelBuilder modelBuilder)
    {
        modelBuilder.Entity<Order>()
            .HasRequired(o => o.Customer)
            .WithMany();
    }
}

...and issue this query...

using (var ctx = new MyContext())
{
    var result = ctx.Orders
        .Include(o => o.Customer)
        .Where(o => o.Details == "Peanuts")
        .FirstOrDefault();
}

...I get this SQL:

SELECT TOP (1) 
[Extent1].[Id] AS [Id], 
[Extent1].[Details] AS [Details], 
[Extent2].[Id] AS [Id1], 
[Extent2].[Name] AS [Name]
FROM  [dbo].[Orders] AS [Extent1]
INNER JOIN [dbo].[Customers] AS [Extent2] 
    ON [Extent1].[Customer_Id] = [Extent2].[Id]
WHERE N'Peanuts' = [Extent1].[Details]

If I change in the model configuration .HasRequired(o => o.Customer) to...

.HasOptional(o => o.Customer)

... I get exactly the same query except that INNER JOIN [dbo].[Customers] AS [Extent2] is replaced by:

LEFT OUTER JOIN [dbo].[Customers] AS [Extent2]

From model viewpoint it makes sense because you are saying that there can never be an Order without a Customer if you define the relationship as required. If you circumvent this requirement by removing the enforcement in the database and if you actually have then orders without a customer you violate your own model definition.

Only solution is likely to make the relationship optional if you have that situation. I don't think it is possible to control the SQL that is created when you use Include.