When I use .Include syntax in EF6, reverse navigation properties are always loaded. Is there any way to disable this? Here is an example request.
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 an AccountLocations account. However, the address "city" has this reverse navigation property:
public virtual ICollection<Address> Addresses { get; set; }
.. so it loads all the return navigation addresses to provide me with much more data than I want in the request. Can I turn off reverse navigation?
I would just remove the reverse navigation property from the object, but in other cases I really want to look back to get addresses from the city.
Thanks.
Change 1:
I just noticed something strange here. This does NOT load the city’s return navigation addresses:
return repository.Queryable() .Include(e => e.BillToAddress.AddressType) .Include(e => e.BillToAddress.City.State.Country);
However, if I add 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, reverse navigation addresses are downloaded for BOTH e.BillToAddress.City and each AccountLocation Address.City. Why add. Select on the third enable effect of the second enable?
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 Information:
Here is the DbContextConfiguration:
public DataContext(string nameOrConnectionString) : base(nameOrConnectionString) { _instanceId = Guid.NewGuid(); Configuration.LazyLoadingEnabled = false; Configuration.ProxyCreationEnabled = false; }
Here is the method that executes the request (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 the 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(); }