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?
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
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();
}
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.