Can you exclude reverse navigation properties in EF when eager loading?

Question

When I use the .Include syntax in EF6, reverse navigation properties are always loaded. Is there a way to turn that off? Here is a sample query.

private static IQueryable<Account> GetAccountsEager(this IRepository<Account> repository)
{
    return repository
        .Queryable()
        .Include(e => e.AccountLocations
            .Select(l => l.Address.City.State.Country));
}

This gives me the account's collection of AccountLocations. However, the Address' city has this reverse navigation property on it:

public virtual ICollection<Address> Addresses { get; set; }

..thus it back-load all the reverse navigation addresses to give me much more data than I want in the query. Can I turn off reverse navigation somehow?

I would just delete the reverse navigation property from the entity but in other cases I actually want to look backwards to get Addresses from a city.

Thanks.

---

Edit 1:
I just noticed something odd here. This does NOT load the reverse navigation addresses of a city:

return repository.Queryable()
    .Include(e => e.BillToAddress.AddressType)
    .Include(e => e.BillToAddress.City.State.Country);

However, if I add an include like this:

return repository.Queryable()
    .Include(e => e.BillToAddress.AddressType)
    .Include(e => e.BillToAddress.City.State.Country);
    .Include(e => e.AccountLocations.Select(a => a.Address.City.State.Country));

Then the reverse navigation addresses are loaded for BOTH the e.BillToAddress.City and each AccountLocation's Address.City. Why does adding .Select on the third include affect the 2nd include?

Here is the SQL:

exec sp_executesql N'SELECT 
    //removing [ProjectX].[Property] AS [Property] for brevity
    //essentially a line for each property of the syntax above...
    FROM ( SELECT 
        [Limit1].[AccountId] AS [AccountId], 
        [Limit1].[AccountNumber] AS [AccountNumber], 
        [Limit1].[Name] AS [Name], 
        [Limit1].[Attention] AS [Attention], 
        [Limit1].[BillToAddressId] AS [BillToAddressId], 
        [Limit1].[NickName] AS [NickName], 
        [Limit1].[MainPhoneNumber] AS [MainPhoneNumber], 
        [Limit1].[MainFaxNumber] AS [MainFaxNumber], 
        [Limit1].[SalesFaxNumber] AS [SalesFaxNumber], 
        [Limit1].[AccountClassId] AS [AccountClassId], 
        [Limit1].[DefaultDiscountPercent] AS [DefaultDiscountPercent], 
        [Limit1].[DefaultCommissionPercent] AS [DefaultCommissionPercent], 
        [Limit1].[PaymentTermId] AS [PaymentTermId], 
        [Limit1].[StatementInd] AS [StatementInd], 
        [Limit1].[CarrierId] AS [CarrierId], 
        [Limit1].[TransportationTermId] AS [TransportationTermId], 
        [Limit1].[AccountExecUserId] AS [AccountExecUserId], 
        [Limit1].[Latitude] AS [Latitude], 
        [Limit1].[Longitude] AS [Longitude], 
        [Limit1].[ServCentContractStartDate] AS [ServCentContractStartDate], 
        [Limit1].[ServCentDiscountPercent] AS [ServCentDiscountPercent], 
        [Limit1].[MastDistContractStartDate] AS [MastDistContractStartDate], 
        [Limit1].[MastDistDiscountPercent] AS [MastDistDiscountPercent], 
        [Limit1].[MastDistDiscountDays] AS [MastDistDiscountDays], 
        [Limit1].[MastDistLyDiscountPercent] AS [MastDistLyDiscountPercent], 
        [Limit1].[AccountStartDate] AS [AccountStartDate], 
        [Limit1].[Website] AS [Website], 
        [Limit1].[InvoiceAttention] AS [InvoiceAttention], 
        [Limit1].[PromptPayPercent] AS [PromptPayPercent], 
        [Limit1].[PromptPayDays] AS [PromptPayDays], 
        [Limit1].[Ranking] AS [Ranking], 
        [Limit1].[OrderDiscountInd] AS [OrderDiscountInd], 
        [Limit1].[PromptPayInd] AS [PromptPayInd], 
        [Limit1].[SalesRepAccountBypassInd] AS [SalesRepAccountBypassInd], 
        [Limit1].[StatusReviewBypassInd] AS [StatusReviewBypassInd], 
        [Limit1].[CheckCreditLimitInd] AS [CheckCreditLimitInd], 
        [Limit1].[CantrolInd] AS [CantrolInd], 
        [Limit1].[PrintInvoiceInd] AS [PrintInvoiceInd], 
        [Limit1].[PrintStatementInd] AS [PrintStatementInd], 
        [Limit1].[PromptOrderAckInd] AS [PromptOrderAckInd], 
        [Limit1].[AdminFeeBypassInd] AS [AdminFeeBypassInd], 
        [Limit1].[InsertDatetime] AS [InsertDatetime], 
        [Limit1].[InsertSystemUserId] AS [InsertSystemUserId], 
        [Limit1].[UpdateDatetime] AS [UpdateDatetime], 
        [Limit1].[UpdateSystemUserId] AS [UpdateSystemUserId], 
        [Limit1].[AccountCreditId] AS [AccountCreditId], 
        [Limit1].[AccountGeographicClassId] AS [AccountGeographicClassId], 
        [Limit1].[AccountProductClassId] AS [AccountProductClassId], 
        [Limit1].[SalesTeamId] AS [SalesTeamId], 
        [Limit1].[AddressId] AS [AddressId], 
        [Limit1].[Address1] AS [Address1], 
        [Limit1].[Address2] AS [Address2], 
        [Limit1].[Address3] AS [Address3], 
        [Limit1].[AddressTypeId] AS [AddressTypeId], 
        [Limit1].[PostalCodeId] AS [PostalCodeId], 
        [Limit1].[CityId] AS [CityId], 
        [Limit1].[InsertDatetime1] AS [InsertDatetime1], 
        [Limit1].[InsertSystemUserId1] AS [InsertSystemUserId1], 
        [Limit1].[UpdateDatetime1] AS [UpdateDatetime1], 
        [Limit1].[UpdateSystemUserId1] AS [UpdateSystemUserId1], 
        [Limit1].[AddressTypeId1] AS [AddressTypeId1], 
        [Limit1].[AddressTypeCode] AS [AddressTypeCode], 
        [Limit1].[AddressTypeDesc] AS [AddressTypeDesc], 
        [Limit1].[InsertDatetime2] AS [InsertDatetime2], 
        [Limit1].[InsertSystemUserId2] AS [InsertSystemUserId2], 
        [Limit1].[UpdateDatetime2] AS [UpdateDatetime2], 
        [Limit1].[UpdateSystemUserId2] AS [UpdateSystemUserId2], 
        [Limit1].[AddressSubTypeId] AS [AddressSubTypeId], 
        [Limit1].[CityId1] AS [CityId1], 
        [Limit1].[CityName] AS [CityName], 
        [Limit1].[StateId] AS [StateId], 
        [Limit1].[InsertDatetime3] AS [InsertDatetime3], 
        [Limit1].[InsertSystemUserId3] AS [InsertSystemUserId3], 
        [Limit1].[UpdateDatetime3] AS [UpdateDatetime3], 
        [Limit1].[UpdateSystemUserId3] AS [UpdateSystemUserId3], 
        [Limit1].[CountyId] AS [CountyId], 
        [Limit1].[StateId1] AS [StateId1], 
        [Limit1].[StateCode] AS [StateCode], 
        [Limit1].[StateName] AS [StateName], 
        [Limit1].[CountryId] AS [CountryId], 
        [Limit1].[InsertDatetime4] AS [InsertDatetime4], 
        [Limit1].[InsertSystemUserId4] AS [InsertSystemUserId4], 
        [Limit1].[UpdateDatetime4] AS [UpdateDatetime4], 
        [Limit1].[UpdateSystemUserId4] AS [UpdateSystemUserId4], 
        [Limit1].[CountryId1] AS [CountryId1], 
        [Limit1].[CountryCode] AS [CountryCode], 
        [Limit1].[CountryName] AS [CountryName], 
        [Limit1].[InsertDatetime5] AS [InsertDatetime5], 
        [Limit1].[InsertSystemUserId5] AS [InsertSystemUserId5], 
        [Limit1].[UpdateDatetime5] AS [UpdateDatetime5], 
        [Limit1].[UpdateSystemUserId5] AS [UpdateSystemUserId5], 
        [Join9].[AccountLocationId] AS [AccountLocationId], 
        [Join9].[AccountId] AS [AccountId1], 
        [Join9].[AddressId1] AS [AddressId1], 
        [Join9].[ShipToNumber] AS [ShipToNumber], 
        [Join9].[AccountLocationName] AS [AccountLocationName], 
        [Join9].[Attention] AS [Attention1], 
        [Join9].[ContactId] AS [ContactId], 
        [Join9].[PhoneNumber] AS [PhoneNumber], 
        [Join9].[FaxNumber] AS [FaxNumber], 
        [Join9].[BranchOfficeFlag] AS [BranchOfficeFlag], 
        [Join9].[UPSAccountNumber] AS [UPSAccountNumber], 
        [Join9].[InsertDatetime1] AS [InsertDatetime6], 
        [Join9].[InsertSystemUserId1] AS [InsertSystemUserId6], 
        [Join9].[UpdateDatetime1] AS [UpdateDatetime6], 
        [Join9].[UpdateSystemUserId1] AS [UpdateSystemUserId6], 
        [Join9].[AddressId2] AS [AddressId2], 
        [Join9].[Address1] AS [Address11], 
        [Join9].[Address2] AS [Address21], 
        [Join9].[Address3] AS [Address31], 
        [Join9].[AddressTypeId] AS [AddressTypeId2], 
        [Join9].[PostalCodeId] AS [PostalCodeId1], 
        [Join9].[CityId1] AS [CityId2], 
        [Join9].[InsertDatetime2] AS [InsertDatetime7], 
        [Join9].[InsertSystemUserId2] AS [InsertSystemUserId7], 
        [Join9].[UpdateDatetime2] AS [UpdateDatetime7], 
        [Join9].[UpdateSystemUserId2] AS [UpdateSystemUserId7], 
        [Join9].[CityId2] AS [CityId3], 
        [Join9].[CityName] AS [CityName1], 
        [Join9].[StateId1] AS [StateId2], 
        [Join9].[InsertDatetime3] AS [InsertDatetime8], 
        [Join9].[InsertSystemUserId3] AS [InsertSystemUserId8], 
        [Join9].[UpdateDatetime3] AS [UpdateDatetime8], 
        [Join9].[UpdateSystemUserId3] AS [UpdateSystemUserId8], 
        [Join9].[CountyId] AS [CountyId1], 
        [Join9].[StateId2] AS [StateId3], 
        [Join9].[StateCode] AS [StateCode1], 
        [Join9].[StateName] AS [StateName1], 
        [Join9].[CountryId1] AS [CountryId2], 
        [Join9].[InsertDatetime4] AS [InsertDatetime9], 
        [Join9].[InsertSystemUserId4] AS [InsertSystemUserId9], 
        [Join9].[UpdateDatetime4] AS [UpdateDatetime9], 
        [Join9].[UpdateSystemUserId4] AS [UpdateSystemUserId9], 
        [Join9].[CountryId2] AS [CountryId3], 
        [Join9].[CountryCode] AS [CountryCode1], 
        [Join9].[CountryName] AS [CountryName1], 
        [Join9].[InsertDatetime5] AS [InsertDatetime10], 
        [Join9].[InsertSystemUserId5] AS [InsertSystemUserId10], 
        [Join9].[UpdateDatetime5] AS [UpdateDatetime10], 
        [Join9].[UpdateSystemUserId5] AS [UpdateSystemUserId10], 
        CASE WHEN ([Join9].[AccountLocationId] IS NULL) THEN CAST(NULL AS int) ELSE 1 END AS [C1]
        FROM   (SELECT TOP (1) 
            [Extent1].[AccountId] AS [AccountId], 
            [Extent1].[AccountNumber] AS [AccountNumber], 
            [Extent1].[Name] AS [Name], 
            [Extent1].[Attention] AS [Attention], 
            [Extent1].[BillToAddressId] AS [BillToAddressId], 
            [Extent1].[NickName] AS [NickName], 
            [Extent1].[MainPhoneNumber] AS [MainPhoneNumber], 
            [Extent1].[MainFaxNumber] AS [MainFaxNumber], 
            [Extent1].[SalesFaxNumber] AS [SalesFaxNumber], 
            [Extent1].[AccountClassId] AS [AccountClassId], 
            [Extent1].[DefaultDiscountPercent] AS [DefaultDiscountPercent], 
            [Extent1].[DefaultCommissionPercent] AS [DefaultCommissionPercent], 
            [Extent1].[PaymentTermId] AS [PaymentTermId], 
            [Extent1].[StatementInd] AS [StatementInd], 
            [Extent1].[CarrierId] AS [CarrierId], 
            [Extent1].[TransportationTermId] AS [TransportationTermId], 
            [Extent1].[AccountExecUserId] AS [AccountExecUserId], 
            [Extent1].[Latitude] AS [Latitude], 
            [Extent1].[Longitude] AS [Longitude], 
            [Extent1].[ServCentContractStartDate] AS [ServCentContractStartDate], 
            [Extent1].[ServCentDiscountPercent] AS [ServCentDiscountPercent], 
            [Extent1].[MastDistContractStartDate] AS [MastDistContractStartDate], 
            [Extent1].[MastDistDiscountPercent] AS [MastDistDiscountPercent], 
            [Extent1].[MastDistDiscountDays] AS [MastDistDiscountDays], 
            [Extent1].[MastDistLyDiscountPercent] AS [MastDistLyDiscountPercent], 
            [Extent1].[AccountStartDate] AS [AccountStartDate], 
            [Extent1].[Website] AS [Website], 
            [Extent1].[InvoiceAttention] AS [InvoiceAttention], 
            [Extent1].[PromptPayPercent] AS [PromptPayPercent], 
            [Extent1].[PromptPayDays] AS [PromptPayDays], 
            [Extent1].[Ranking] AS [Ranking], 
            [Extent1].[OrderDiscountInd] AS [OrderDiscountInd], 
            [Extent1].[PromptPayInd] AS [PromptPayInd], 
            [Extent1].[SalesRepAccountBypassInd] AS [SalesRepAccountBypassInd], 
            [Extent1].[StatusReviewBypassInd] AS [StatusReviewBypassInd], 
            [Extent1].[CheckCreditLimitInd] AS [CheckCreditLimitInd], 
            [Extent1].[CantrolInd] AS [CantrolInd], 
            [Extent1].[PrintInvoiceInd] AS [PrintInvoiceInd], 
            [Extent1].[PrintStatementInd] AS [PrintStatementInd], 
            [Extent1].[PromptOrderAckInd] AS [PromptOrderAckInd], 
            [Extent1].[AdminFeeBypassInd] AS [AdminFeeBypassInd], 
            [Extent1].[InsertDatetime] AS [InsertDatetime], 
            [Extent1].[InsertSystemUserId] AS [InsertSystemUserId], 
            [Extent1].[UpdateDatetime] AS [UpdateDatetime], 
            [Extent1].[UpdateSystemUserId] AS [UpdateSystemUserId], 
            [Extent1].[AccountCreditId] AS [AccountCreditId], 
            [Extent1].[AccountGeographicClassId] AS [AccountGeographicClassId], 
            [Extent1].[AccountProductClassId] AS [AccountProductClassId], 
            [Extent1].[SalesTeamId] AS [SalesTeamId], 
            [Extent2].[AddressId] AS [AddressId], 
            [Extent2].[Address1] AS [Address1], 
            [Extent2].[Address2] AS [Address2], 
            [Extent2].[Address3] AS [Address3], 
            [Extent2].[AddressTypeId] AS [AddressTypeId], 
            [Extent2].[PostalCodeId] AS [PostalCodeId], 
            [Extent2].[CityId] AS [CityId], 
            [Extent2].[InsertDatetime] AS [InsertDatetime1], 
            [Extent2].[InsertSystemUserId] AS [InsertSystemUserId1], 
            [Extent2].[UpdateDatetime] AS [UpdateDatetime1], 
            [Extent2].[UpdateSystemUserId] AS [UpdateSystemUserId1], 
            [Extent3].[AddressTypeId] AS [AddressTypeId1], 
            [Extent3].[AddressTypeCode] AS [AddressTypeCode], 
            [Extent3].[AddressTypeDesc] AS [AddressTypeDesc], 
            [Extent3].[InsertDatetime] AS [InsertDatetime2], 
            [Extent3].[InsertSystemUserId] AS [InsertSystemUserId2], 
            [Extent3].[UpdateDatetime] AS [UpdateDatetime2], 
            [Extent3].[UpdateSystemUserId] AS [UpdateSystemUserId2], 
            [Extent3].[AddressSubTypeId] AS [AddressSubTypeId], 
            [Extent4].[CityId] AS [CityId1], 
            [Extent4].[CityName] AS [CityName], 
            [Extent4].[StateId] AS [StateId], 
            [Extent4].[InsertDatetime] AS [InsertDatetime3], 
            [Extent4].[InsertSystemUserId] AS [InsertSystemUserId3], 
            [Extent4].[UpdateDatetime] AS [UpdateDatetime3], 
            [Extent4].[UpdateSystemUserId] AS [UpdateSystemUserId3], 
            [Extent4].[CountyId] AS [CountyId], 
            [Extent5].[StateId] AS [StateId1], 
            [Extent5].[StateCode] AS [StateCode], 
            [Extent5].[StateName] AS [StateName], 
            [Extent5].[CountryId] AS [CountryId], 
            [Extent5].[InsertDatetime] AS [InsertDatetime4], 
            [Extent5].[InsertSystemUserId] AS [InsertSystemUserId4], 
            [Extent5].[UpdateDatetime] AS [UpdateDatetime4], 
            [Extent5].[UpdateSystemUserId] AS [UpdateSystemUserId4], 
            [Extent6].[CountryId] AS [CountryId1], 
            [Extent6].[CountryCode] AS [CountryCode], 
            [Extent6].[CountryName] AS [CountryName], 
            [Extent6].[InsertDatetime] AS [InsertDatetime5], 
            [Extent6].[InsertSystemUserId] AS [InsertSystemUserId5], 
            [Extent6].[UpdateDatetime] AS [UpdateDatetime5], 
            [Extent6].[UpdateSystemUserId] AS [UpdateSystemUserId5]
            FROM      [dbo].[Account] AS [Extent1]
            INNER JOIN [Common].[Address] AS [Extent2] ON [Extent1].[BillToAddressId] = [Extent2].[AddressId]
            INNER JOIN [Common].[AddressType] AS [Extent3] ON [Extent2].[AddressTypeId] = [Extent3].[AddressTypeId]
            INNER JOIN [Common].[City] AS [Extent4] ON [Extent2].[CityId] = [Extent4].[CityId]
            INNER JOIN [Common].[State] AS [Extent5] ON [Extent4].[StateId] = [Extent5].[StateId]
            INNER JOIN [Common].[Country] AS [Extent6] ON [Extent5].[CountryId] = [Extent6].[CountryId]
            WHERE [Extent1].[AccountId] = @p__linq__0 ) AS [Limit1]
        LEFT OUTER JOIN  (SELECT [Extent7].[AccountLocationId] AS [AccountLocationId], [Extent7].[AccountId] AS [AccountId], [Extent7].[AddressId] AS [AddressId1], [Extent7].[ShipToNumber] AS [ShipToNumber], [Extent7].[AccountLocationName] AS [AccountLocationName], [Extent7].[Attention] AS [Attention], [Extent7].[ContactId] AS [ContactId], [Extent7].[PhoneNumber] AS [PhoneNumber], [Extent7].[FaxNumber] AS [FaxNumber], [Extent7].[BranchOfficeFlag] AS [BranchOfficeFlag], [Extent7].[UPSAccountNumber] AS [UPSAccountNumber], [Extent7].[InsertDatetime] AS [InsertDatetime1], [Extent7].[InsertSystemUserId] AS [InsertSystemUserId1], [Extent7].[UpdateDatetime] AS [UpdateDatetime1], [Extent7].[UpdateSystemUserId] AS [UpdateSystemUserId1], [Extent8].[AddressId] AS [AddressId2], [Extent8].[Address1] AS [Address1], [Extent8].[Address2] AS [Address2], [Extent8].[Address3] AS [Address3], [Extent8].[AddressTypeId] AS [AddressTypeId], [Extent8].[PostalCodeId] AS [PostalCodeId], [Extent8].[CityId] AS [CityId1], [Extent8].[InsertDatetime] AS [InsertDatetime2], [Extent8].[InsertSystemUserId] AS [InsertSystemUserId2], [Extent8].[UpdateDatetime] AS [UpdateDatetime2], [Extent8].[UpdateSystemUserId] AS [UpdateSystemUserId2], [Extent9].[CityId] AS [CityId2], [Extent9].[CityName] AS [CityName], [Extent9].[StateId] AS [StateId1], [Extent9].[InsertDatetime] AS [InsertDatetime3], [Extent9].[InsertSystemUserId] AS [InsertSystemUserId3], [Extent9].[UpdateDatetime] AS [UpdateDatetime3], [Extent9].[UpdateSystemUserId] AS [UpdateSystemUserId3], [Extent9].[CountyId] AS [CountyId], [Extent10].[StateId] AS [StateId2], [Extent10].[StateCode] AS [StateCode], [Extent10].[StateName] AS [StateName], [Extent10].[CountryId] AS [CountryId1], [Extent10].[InsertDatetime] AS [InsertDatetime4], [Extent10].[InsertSystemUserId] AS [InsertSystemUserId4], [Extent10].[UpdateDatetime] AS [UpdateDatetime4], [Extent10].[UpdateSystemUserId] AS [UpdateSystemUserId4], [Extent11].[CountryId] AS [CountryId2], [Extent11].[CountryCode] AS [CountryCode], [Extent11].[CountryName] AS [CountryName], [Extent11].[InsertDatetime] AS [InsertDatetime5], [Extent11].[InsertSystemUserId] AS [InsertSystemUserId5], [Extent11].[UpdateDatetime] AS [UpdateDatetime5], [Extent11].[UpdateSystemUserId] AS [UpdateSystemUserId5]
            FROM     [dbo].[AccountLocation] AS [Extent7]
            INNER JOIN [Common].[Address] AS [Extent8] ON [Extent7].[AddressId] = [Extent8].[AddressId]
            INNER JOIN [Common].[City] AS [Extent9] ON [Extent8].[CityId] = [Extent9].[CityId]
            INNER JOIN [Common].[State] AS [Extent10] ON [Extent9].[StateId] = [Extent10].[StateId]
            INNER JOIN [Common].[Country] AS [Extent11] ON [Extent10].[CountryId] = [Extent11].[CountryId] ) AS [Join9] ON [Limit1].[AccountId] = [Join9].[AccountId]
    )  AS [Project2]
    ORDER BY [Project2].[AccountId] ASC, [Project2].[AddressId] ASC, [Project2].[AddressTypeId1] ASC, [Project2].[CityId1] ASC, [Project2].[StateId1] ASC, [Project2].[CountryId1] ASC, [Project2].[C1] ASC',N'@p__linq__0 int',@p__linq__0=21067

Additional Info:

Here is the DbContextConfiguration:

public DataContext(string nameOrConnectionString)
    : base(nameOrConnectionString)
{
    _instanceId = Guid.NewGuid();
    Configuration.LazyLoadingEnabled = false;
    Configuration.ProxyCreationEnabled = false;
}

Here is the method executing the query (using .FirstOrDefault()):

public static Account GetEager(
    this IRepository<Account> repository,
    int id)
{
    var query = repository.GetQueryableEager();

    return query.FirstOrDefault(e => e.AccountId == id);
}

Here is a city:

public partial class City: Entity, IDatabaseMetaData
{
    public int CityId { get; set; } // CityId (Primary key)
    public string CityName { get; set; } // CityName
    public int StateId { get; set; } // StateId
    public DateTime InsertDatetime { get; set; } // InsertDatetime
    public int InsertSystemUserId { get; set; } // InsertSystemUserId
    public DateTime? UpdateDatetime { get; set; } // UpdateDatetime
    public int? UpdateSystemUserId { get; set; } // UpdateSystemUserId
    public int? CountyId { get; set; } // CountyId

    // Reverse navigation
    public virtual ICollection<Address> Addresses { get; set; } // Address.FK_Address_CityId

    // Foreign keys
    public virtual County County { get; set; } // FK_City_CountyId
    public virtual State State { get; set; } // FK_City_StateId

    public City()
    {
        InsertDatetime = System.DateTime.Now;
        Addresses = new List<Address>();
        InitializePartial();
    }

    partial void InitializePartial();
}

Salesgirl

you said: thus it back-load all the reverse navigation addresses to give me much more data than I want in the query. Can I turn off reverse navigation somehow? Are you sure that the generated SQL is returning more data than you want? Maybe the SQL Query is fine, and EF just fill the reverse navigation properties, which shouldn't be a problem. – Harwood

Yes, the query is pulling more data than what I want. If I get an account whose city is Dallas, it returns the reverse navigation property (address) on city, and thus every address containing Dallas as a city gets "back loaded". – Salesgirl

can you post the sql query generated by the 2nd statement. If there is left join to fetch addresses or just inner join. – Plaid

I edited the post to include the SQL generated by EF for the 2nd query as suggested. – Salesgirl

What is the current value of your Configuration.LazyLoadingEnabled property in your DBContext derived class? )You can turn off or on lazy loading for you context there. See also entityframeworktutorial.net/EntityFramework4.3/… ) – Debutante

Could you provide the structure of your main classes? – Harwood

How are you enumerating the objects? Are you calling ToList()? Please, provide the method that is executing the query. – Harwood

I've edited the post to provide all further requested information. – Salesgirl

How do you see the "back loaded" addresses? In Debug Mode, if you expand the object properties, you force EF to load navigation properties that were not loaded before. Retrieve the Account object; call the Dispose() method of your DbContext; Check the City's Addresses collection. Does it still being filled? – Harwood

No, the web api 2 endpoint returns json, which includes the reverse navigation addresses. I did what you said and called dispose on the context before the api returns, and the addresses are still "back loaded". – Salesgirl

Well, I tried to reproduce your problem. I created a console app and followed your table structure and queries. I didn't find anything wrong; the Addresses collection of the City was not "back loaded". In my case, the city has a lot of Addresses, and just one city was returned in the collection, which is the same City that was loaded before. I assume there's something wrong with your table structure and/or queries – Harwood

Why do you need those navigation properties to remain unpopulated? – Serenata

I don't want the db to retrieve that data, nor do I want to serialize it and send it over the wire. – Salesgirl

The data are not retrieved redundantly, they only get connected. So the retrieval is not the main problem, the serialization is. – Iene

Redundantly, no, but I don't need that address data at all, so needlessly yes. – Salesgirl

Answer 1

I think what you're seeing is a result of "relationship fix-up":

Relationship fix-up ensures that related objects are automatically linked when the second entity enters the ObjectContext.

http://blogs.msdn.com/b/alexj/archive/2009/10/13/tip-37-how-to-do-a-conditional-include.aspx

---

In your case, since you're including AccountLocation.Address.City, EF is loading the related Address entities and the related City entites that you requested, and as part of the "fix-up" it's adding the loaded Address entities that would be part of the City->Address one-to-many relationship to the City.Addresses navigation collection.

You can find another example at this link. As in your case, lazy loading and proxy creation were also disabled -- however, that link doesn't include a way to disable this behavior.

Serenata

Answer 2

Can you exclude reverse navigation properties in EF when eager loading?

No.

As explained in another answer, EF has this process of relationship fixup that knits together all entities it has loaded into a context. You can't stop this process in any way. So if you don't take things in your own hands, you'll always end up having this bloated Json string. (I can't explain nor reproduce the eager loading differences you seem to find, but these should be the subject of a new question. I wouldn't count on them to always happen, hoping they will solve your current issue).

So that's the key here: take control. It depends on your situation what is the best strategy, but there are a few options.

1. Project the query into an anonymous type and serialize it into JSON. Just a minimal example:

   repository.Queryable()
             .Select(e => new 
             {
                  Account = e.Name,
                  AccountLocations = 
                      e.AccountLocations
                       .Select(l => new
                       {
                           Address = new { l.Address.Street, l.Address.Number },
                           City = l.Address.City.Name,
                           Country = l.Address.City.Country.Name,
                       })
             });
   

   Some people will frown upon returning anonymous types from a method, even if it's in JSON, but to me it's just a method that returns JSON in some desired format. It only brings the untyped JavaScript world one step closer.

2. Do the same with a structure of named types (DTO, or view model, types.

3. Create a dedicated context without bidirectional associations. This is an option that's often forgotten, but why should there always be one context by which you access your data? Only make sure that the classes used by (or generated by) the context have different names than the ones belonging to the main context.

By the way, by doing this you can also tailor the amount of data you fetch from the database to what you need to serialize.

Iene