none
EF Core: Calling ToList fails to populate navigation property on child object which is part of a collection on a parent object. RRS feed

  • Question

  • I have a data provider that returns IQueryable<Integrator>.  If I call FirstOrDefault on the results, my Integrator object is fully hydrated.  If I call ToList, or iterate over the results, the Integrator object is not fully hydrated - Any Ideas?

    Check this out -

    This test succeeds, Integrator.Applications[0].Application is not null 

    [TestMethod]
    public void DataProvider_Integrator_Get_Table_Should_Have_Non_Null_Application()
    {
    	var result = _dataProvider.GetTable<Integrator>()
    		.Include(o => o.Applications)
    		.ThenInclude(o => o.Application)
    		.Include(o => o.Contacts);
    		
    
    	Assert.IsNotNull(result);
    
    	var integrator = result.FirstOrDefault();
    	var application = integrator.Applications.FirstOrDefault().Application;
    	Assert.IsInstanceOfType(application, typeof(Application));
    }


    This test fails, Integrator.Applications[0].Application is null - It seems calling ToList fails populate the Application navigation property of IntegratorApplication

    [TestMethod]
    public void DataProvider_Integrator_Get_Table_To_List_Should_Have_Non_Null_Application()
    {
    	var result = _dataProvider.GetTable<Integrator>()
    		.Include(o => o.Applications)
    		.ThenInclude(o => o.Application)
    		.Include(o => o.Contacts)
    		.ToList();
    
    	Assert.IsNotNull(result);
    
    	var integrator = result.FirstOrDefault();
    	var application = integrator.Applications.FirstOrDefault().Application;
    	Assert.IsInstanceOfType(application, typeof(Application));
    }

    Here is what I have implemented (Code removed for brevity)

    DataProvider class inherits DbContext

    public IQueryable<TModel> GetTable<TModel>() where TModel : class
    {
    	return Set<TModel>();
    }


    4 POCO classes

    Integrator
    {
        public int IntegratorId { get; set; }
    	...
    	
    	public virtual ICollection<IntegratorApplication> Applications { get; set; }
    	public virtual ICollection<IntegratorContact> Contacts { get; set; }
    }
    
    IntegratorContact
    {
    	public int IntegratorContactId { get; set; }
        public int IntegratorId { get; set; }	
    	...
    	
    	public virtual Integrator Integrator { get; set; }
    }
    
    IntegratorApplication
    {
    	public int ApplicationId { get; set; }
        public int IntegratorId { get; set; }
    	...
    	
    	public virtual Integrator Integrator { get; set; }
    	public virtual Application Application { get; set; }	
    }

    4 Configurations classes:

    IntegratorConfiguration
    public override void Configure(EntityTypeBuilder<Integrator> builder)
    {
    	builder.ToTable("Integrator");
    	builder.HasKey(o => o.IntegratorId);
    	builder.Property(o => o.IntegratorId).HasColumnName("IntegratorId").ValueGeneratedOnAdd();
    	...
    	
    	// One to Many Navigation
    	builder.HasMany(m=>m.Applications).WithOne(o => o.Integrator);
    	builder.HasMany(m => m.Contacts).WithOne(o => o.Integrator);
    }
    
    IntegratorContactConfiguration
    public override void Configure(EntityTypeBuilder<IntegratorContact> builder)
    {
    	builder.ToTable("IntegratorContact");
    	builder.HasKey(o => o.IntegratorContactId);
    	builder.Property(o => o.IntegratorContactId).HasColumnName("IntegratorContactId").ValueGeneratedOnAdd();
    	...
    }
    
    IntegratorApplicationConfiguration
    public override void Configure(EntityTypeBuilder<IntegratorApplication> builder)
    {
    	builder.ToTable("IntegratorApplication");
    	builder.HasKey(o => new { o.ApplicationId, o.IntegratorId });
    	builder.Property(o => o.ApplicationId).HasColumnName("ApplicationId").ValueGeneratedOnAdd();
    	builder.Property(o => o.IntegratorId).HasColumnName("IntegratorId");
    	...
    
    	// One to One
    	builder.HasOne(o => o.Application).WithOne(o => o.IntegratorApplication);
    }
    
    ApplicationConfiguration
    public override void Configure(EntityTypeBuilder<Application> builder)
    {
    	builder.ToTable("Application");
    	builder.HasKey(o => o.ApplicationId);
    	builder.Property(o => o.ApplicationId).HasColumnName("ApplicationId").ValueGeneratedOnAdd();
    	builder.Property(o => o.ApplicationName).HasColumnName("ApplicationName");
    }

    I also captured the sql executed for each test:

    DataProvider_Integrator_Get_Table_Should_Have_Non_Null_Application:

    -- FirstOrDefault causes SQL execution
    
    SELECT TOP(1) [o].[IntegratorId], [o].[Notes], [o].[ORTIntegratorId], [o].[Status], [o].[Vendor]
    FROM [Integrator] AS [o]
    ORDER BY [o].[IntegratorId]
    
    SELECT [o.Applications].[ApplicationId], [o.Applications].[IntegratorId], [o.Applications].[Status], [i.Application].[ApplicationId], [i.Application].[ApplicationName]
    FROM [IntegratorApplication] AS [o.Applications]
    INNER JOIN [Application] AS [i.Application] ON [o.Applications].[ApplicationId] = [i.Application].[ApplicationId]
    INNER JOIN (
        SELECT TOP(1) [o0].[IntegratorId]
        FROM [Integrator] AS [o0]
        ORDER BY [o0].[IntegratorId]
    ) AS [t] ON [o.Applications].[IntegratorId] = [t].[IntegratorId]
    ORDER BY [t].[IntegratorId]
    
    SELECT [o.Contacts].[IntegratorContactId], [o.Contacts].[Email], [o.Contacts].[Fax], [o.Contacts].[FirstName], [o.Contacts].[IntegratorId], [o.Contacts].[LastName], [o.Contacts].[MiddleName], [o.Contacts].[Phone], [o.Contacts].[Role], [o.Contacts].[Salutation], [o.Contacts].[Status], [o.Contacts].[Title]
    FROM [IntegratorContact] AS [o.Contacts]
    INNER JOIN (
        SELECT TOP(1) [o1].[IntegratorId]
        FROM [Integrator] AS [o1]
        ORDER BY [o1].[IntegratorId]
    ) AS [t0] ON [o.Contacts].[IntegratorId] = [t0].[IntegratorId]
    ORDER BY [t0].[IntegratorId]

    public void DataProvider_Integrator_Get_Table_To_List_Should_Have_Non_Null_Application

    -- ToList causes SQL execution
    
    SELECT [o].[IntegratorId], [o].[Notes], [o].[ORTIntegratorId], [o].[Status], [o].[Vendor]
    FROM [Integrator] AS [o]
    ORDER BY [o].[IntegratorId]
    
    SELECT [o.Applications].[ApplicationId], [o.Applications].[IntegratorId], [o.Applications].[Status], [i.Application].[ApplicationId], [i.Application].[ApplicationName]
    FROM [IntegratorApplication] AS [o.Applications]
    INNER JOIN [Application] AS [i.Application] ON [o.Applications].[ApplicationId] = [i.Application].[ApplicationId]
    INNER JOIN (
        SELECT [o0].[IntegratorId]
        FROM [Integrator] AS [o0]
    ) AS [t] ON [o.Applications].[IntegratorId] = [t].[IntegratorId]
    ORDER BY [t].[IntegratorId]
    
    SELECT [o.Contacts].[IntegratorContactId], [o.Contacts].[Email], [o.Contacts].[Fax], [o.Contacts].[FirstName], [o.Contacts].[IntegratorId], [o.Contacts].[LastName], [o.Contacts].[MiddleName], [o.Contacts].[Phone], [o.Contacts].[Role], [o.Contacts].[Salutation], [o.Contacts].[Status], [o.Contacts].[Title]
    FROM [IntegratorContact] AS [o.Contacts]
    INNER JOIN (
        SELECT [o1].[IntegratorId]
        FROM [Integrator] AS [o1]
    ) AS [t0] ON [o.Contacts].[IntegratorId] = [t0].[IntegratorId]
    ORDER BY [t0].[IntegratorId]


    Bill Behning


    • Edited by WRBehning Wednesday, December 27, 2017 4:16 PM
    Wednesday, December 27, 2017 3:35 PM

All replies

  • If the code you have posted is supposed to be some kind of unit test, then it's not a unit test.

    http://www.artima.com/weblogs/viewpost.jsp?thread=126923

    Wednesday, December 27, 2017 4:47 PM
  • Hi WRBehning,

    >>I have a data provider that returns IQueryable<Integrator>.  If I call FirstOrDefault on the results, my Integrator object is fully hydrated.  If I call ToList, or iterate over the results, the Integrator object is not fully hydrated - Any Ideas?

    According to your description, it seems that you could not retrieve the navigate property when you use ToList method, But based on the SQL statement you providing, you have get navigate property when you use ToList method, which use Join in SQL statement.

    Could you please describe it in detailed, it will be beneficial to resolve the issue.

    Best regards,

    Zhanglong Wu


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Thursday, December 28, 2017 8:57 AM
    Moderator