¿Puedes excluir las propiedades de navegación inversa en EF cuando estás ansioso por cargar?
Cuando uso la sintaxis .Include en EF6, las propiedades de navegación inversa siempre se cargan. ¿Hay alguna manera de apagar eso? Aquí hay una consulta de muestra.
private static IQueryable<Account> GetAccountsEager(this IRepository<Account> repository)
{
return repository
.Queryable()
.Include(e => e.AccountLocations
.Select(l => l.Address.City.State.Country));
}
Esto me da la colección de AccountLocations de la cuenta. Sin embargo, la ciudad de la Dirección tiene esta propiedad de navegación inversa:
public virtual ICollection<Address> Addresses { get; set; }
..que carga todas las direcciones de navegación inversa para darme muchos más datos de los que quiero en la consulta. ¿Puedo desactivar la navegación inversa de alguna manera?
Simplemente eliminaría la propiedad de navegación inversa de la entidad, pero en otros casos realmente quiero mirar hacia atrás para obtener Direcciones de una ciudad.
Gracias.
Editar 1:
Acabo de notar algo extraño aquí. Esto NO carga las direcciones de navegación inversa de una ciudad:
return repository.Queryable()
.Include(e => e.BillToAddress.AddressType)
.Include(e => e.BillToAddress.City.State.Country);
Sin embargo, si agrego un include como este:
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));
Luego, las direcciones de navegación inversa se cargan para AMBAS e.BillToAddress.City y cada AddressLocation's Address.City. ¿Por qué agregar .Select en la tercera inclusión afecta la segunda inclusión?
Aquí está el 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 [Insert,Datetime1],
[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
Aquí está la DbContextConfiguration:
public DataContext(string nameOrConnectionString)
: base(nameOrConnectionString)
{
_instanceId = Guid.NewGuid();
Configuration.LazyLoadingEnabled = false;
Configuration.ProxyCreationEnabled = false;
}
Aquí está el método que ejecuta la consulta (usando .FirstOrDefault ()):
public static Account GetEager(
this IRepository<Account> repository,
int id)
{
var query = repository.GetQueryableEager();
return query.FirstOrDefault(e => e.AccountId == id);
}
Aquí hay una ciudad:
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();
}