EF Reduce Outer Joins for Nested Types

Question

I am having trouble with my configurations for one of my projects and the SQL EF is generating seems to be showing some unexpected (to me, at least) nesting. This question will be a little long-winded but bear with me please.

So, say I have the following set up:

public class Order
{
    public int Id { get; set; }
    public decimal Amount { get; set; }
    public Address ShippingAddress { get; set; }
}

public class Person
{
    public int Id { get; set; }
    public string FirstName { get; set; }
    public string LastName { get; set; }
    public Address Address { get; set; }
}

public class Address
{
    public int Id { get; set; }
    public string StreetAddress { get; set; }
    public string City { get; set; }
    public string State { get; set; }
    public string ZipCode { get; set; }
}

with the entities configured as such:

public class OrderConfiguration : EntityTypeConfiguration<Order>
{
    public OrderConfiguration()
    {
        ToTable("Orders");

        HasRequired(order => order.ShippingAddress)
            .WithRequiredDependent()
            .Map(mapping => mapping.MapKey("ShippingAddressId"));
    }
}

public class PersonConfiguration : EntityTypeConfiguration<Person>
{
    public PersonConfiguration()
    {
        ToTable("Persons");

        HasRequired(person => person.Address)
            .WithRequiredDependent()
            .Map(mapping => mapping.MapKey("AddressId"));
    }
}

My goal is to get the orders with the addresses included, so I run a simple query such as:

var orders = context.Orders
    .Include(order => order.ShippingAddress)
    .Where(order => order.ShippingAddress.State == "FL")
    .ToList();

As I monitor SQL Profiler, I am expecting to see generated SQL which selects from the Orders table and doing an inner join on the Address table

 SELECT * FROM [dbo].[Orders] AS [Extent1] 
     INNER JOIN [dbo].[Addresses] AS [Extent2] ON [Extent1].[AddressId] = [Extentd2].[Id]
     WHERE [Extent2].[State] == 'FL'

instead, I am finding this generated:

SELECT 
[Extent1].[Id] AS [Id], 
[Extent1].[Amount] AS [Amount], 
[Join5].[Id1] AS [Id1], 
[Join5].[StreetAddress] AS [StreetAddress], 
[Join5].[City] AS [City], 
[Join5].[State] AS [State], 
[Join5].[ZipCode] AS [ZipCode], 
[Join8].[Id2] AS [Id2], 
[Join11].[Id3] AS [Id3]
FROM     [dbo].[Orders] AS [Extent1]
INNER JOIN  (SELECT [Extent2].[State] AS [State], [Extent3].[Id] AS [Id4]
    FROM   [dbo].[Addresses] AS [Extent2]
    LEFT OUTER JOIN [dbo].[Orders] AS [Extent3] ON [Extent2].[Id] = [Extent3].[ShippingAddressId]
    LEFT OUTER JOIN [dbo].[Persons] AS [Extent4] ON [Extent2].[Id] = [Extent4].[AddressId] ) AS [Join2] ON [Extent1].[Id] = [Join2].[Id4]
LEFT OUTER JOIN  (SELECT [Extent5].[Id] AS [Id1], [Extent5].[StreetAddress] AS [StreetAddress], [Extent5].[City] AS [City], [Extent5].[State] AS [State], [Extent5].[ZipCode] AS [ZipCode], [Extent6].[Id] AS [Id5]
    FROM   [dbo].[Addresses] AS [Extent5]
    LEFT OUTER JOIN [dbo].[Orders] AS [Extent6] ON [Extent5].[Id] = [Extent6].[ShippingAddressId]
    LEFT OUTER JOIN [dbo].[Persons] AS [Extent7] ON [Extent5].[Id] = [Extent7].[AddressId] ) AS [Join5] ON [Extent1].[Id] = [Join5].[Id5]
LEFT OUTER JOIN  (SELECT [Extent9].[Id] AS [Id2]
    FROM   [dbo].[Addresses] AS [Extent8]
    LEFT OUTER JOIN [dbo].[Orders] AS [Extent9] ON [Extent8].[Id] = [Extent9].[ShippingAddressId]
    LEFT OUTER JOIN [dbo].[Persons] AS [Extent10] ON [Extent8].[Id] = [Extent10].[AddressId] ) AS [Join8] ON [Extent1].[Id] = [Join8].[Id2]
LEFT OUTER JOIN  (SELECT [Extent12].[Id] AS [Id6], [Extent13].[Id] AS [Id3]
    FROM   [dbo].[Addresses] AS [Extent11]
    LEFT OUTER JOIN [dbo].[Orders] AS [Extent12] ON [Extent11].[Id] = [Extent12].[ShippingAddressId]
    LEFT OUTER JOIN [dbo].[Persons] AS [Extent13] ON [Extent11].[Id] = [Extent13].[AddressId] ) AS [Join11] ON [Extent1].[Id] = [Join11].[Id6]
WHERE N'FL' = [Join2].[State]

Obviously, this is a lot more work than needs to be done. Is there an easier way to do this or do I have the entities configured incorrectly? I do not understand why it is pulling back all of these extra joins, or how I can limit them just to the tables I need right then. Any insight would be tremendously appreciated!

Thanks, Derek

Answer 1

As you have noticed .Include() can sometimes generate horrendous SQL!

I have found the easiest solution in this situation is to explicitly load the required related entities directly after the initial load. The downside to this is the additional database calls - it will call once for each order.

var orders = (
    from order in context.Orders
    where order.ShippingAddress.State == "FL"
    select order)
    .ToList();

var t1 = (
    from order in orders
    from address in order.ShippingAddress
    select address)
    .ToList();