EF core navigation property not loading

c# entity-framework-core navigation-properties

Question

I'm modifying my application to be able to specify navigation properties to load in the repository.

Model: Team and TeamTunerUser can be found in the domain entites.

Repository:

namespace Sppd.TeamTuner.Infrastructure.DataAccess.EF.Repositories
{
    internal class Repository<TEntity> : IRepository<TEntity>
        where TEntity : BaseEntity
    {
        /// <summary>
        ///     Gets the entity set.
        /// </summary>
        protected DbSet<TEntity> Set => Context.Set<TEntity>();

        /// <summary>
        ///     Gets the DB context.
        /// </summary>
        protected TeamTunerContext Context { get; }

        public Repository(TeamTunerContext context)
        {
            Context = context;
        }

        public async Task<TEntity> GetAsync(Guid entityId, IEnumerable<string> includeProperties = null)
        {
            TEntity entity;
            try
            {
                entity = await GetQueryableWithIncludes(includeProperties).SingleAsync(e => e.Id == entityId);
            }
            catch (InvalidOperationException)
            {
                throw new EntityNotFoundException(typeof(TEntity), entityId.ToString());
            }

            return entity;
        }

        protected IQueryable<TEntity> GetQueryableWithIncludes(IEnumerable<string> includeProperties = null)
        {
            var queryable = Set;

            if (includeProperties == null)
            {
                return queryable;
            }

            foreach (var propertyName in includeProperties)
            {
                queryable.Include(propertyName);
            }

            return queryable;
        }
    }
}

Test:

    [Fact]
    public async Task TestNavigationPropertyLoading()
    {
        // Arrange
        var teamId = Guid.Parse(TestingConstants.Team.HOLY_COW);

        // Act
        Team createdTeamWithoutUsers;
        Team createdTeamWithUsers;
        using (var scope = ServiceProvider.CreateScope())
        {
            var teamRepository = scope.ServiceProvider.GetService<IRepository<Team>>();

            createdTeamWithoutUsers = await teamRepository.GetAsync(teamId);
            createdTeamWithUsers = await teamRepository.GetAsync(teamId, new[] {nameof(Team.Users)});
        }

        // Assert
        Assert.Null(createdTeamWithoutUsers.Leader);
        Assert.False(createdTeamWithoutUsers.Users.Any());
        Assert.False(createdTeamWithUsers.CoLeaders.Any());

        Assert.NotNull(createdTeamWithUsers.Leader);
        Assert.True(createdTeamWithUsers.Users.Any());
        Assert.True(createdTeamWithUsers.CoLeaders.Any());
    }

My issue is that the Users navigation property never gets loaded and the second assertion block fails.

The Team is configured here (class):

    private static void ConfigureTeam(EntityTypeBuilder<Team> builder)
    {
        ConfigureDescriptiveEntity(builder);

        builder.HasMany(e => e.Users)
               .WithOne(e => e.Team);

        // Ignore calculated properties
        builder.Ignore(e => e.Members)
               .Ignore(e => e.Leader)
               .Ignore(e => e.CoLeaders);
    }

The (debug) logs don't contain anything useful, except that I see that the join required to load navigation properties is not being executed on SQL level:

2019-04-11 16:02:43,896 [14] DEBUG Microsoft.EntityFrameworkCore.Database.Connection - Opening connection to database 'Sppd.TeamTuner-TEST' on server '.\SQLEXPRESS'.
2019-04-11 16:02:43,901 [14] DEBUG Microsoft.EntityFrameworkCore.Database.Connection - Opened connection to database 'Sppd.TeamTuner-TEST' on server '.\SQLEXPRESS'.
2019-04-11 16:02:43,903 [14] DEBUG Microsoft.EntityFrameworkCore.Database.Command - Executing DbCommand [Parameters=[@__entityId_0='?' (DbType = Guid)], CommandType='Text', CommandTimeout='30']
SELECT TOP(2) [m].[Id], [m].[Avatar], [m].[CreatedById], [m].[CreatedOnUtc], [m].[DeletedById], [m].[DeletedOnUtc], [m].[Description], [m].[FederationId], [m].[IsDeleted], [m].[ModifiedById], [m].[ModifiedOnUtc], [m].[Name]
FROM [Team] AS [m]
WHERE ([m].[IsDeleted] = 0) AND ([m].[Id] = @__entityId_0)
2019-04-11 16:02:43,920 [12] INFO  Microsoft.EntityFrameworkCore.Database.Command - Executed DbCommand (16ms) [Parameters=[@__entityId_0='?' (DbType = Guid)], CommandType='Text', CommandTimeout='30']
SELECT TOP(2) [m].[Id], [m].[Avatar], [m].[CreatedById], [m].[CreatedOnUtc], [m].[DeletedById], [m].[DeletedOnUtc], [m].[Description], [m].[FederationId], [m].[IsDeleted], [m].[ModifiedById], [m].[ModifiedOnUtc], [m].[Name]
FROM [Team] AS [m]
WHERE ([m].[IsDeleted] = 0) AND ([m].[Id] = @__entityId_0)
2019-04-11 16:02:43,945 [12] DEBUG Microsoft.EntityFrameworkCore.Database.Command - A data reader was disposed.
2019-04-11 16:02:43,985 [12] DEBUG Microsoft.EntityFrameworkCore.Database.Connection - Closing connection to database 'Sppd.TeamTuner-TEST' on server '.\SQLEXPRESS'.
2019-04-11 16:02:43,988 [12] DEBUG Microsoft.EntityFrameworkCore.Database.Connection - Closed connection to database 'Sppd.TeamTuner-TEST' on server '.\SQLEXPRESS'.
2019-04-11 16:02:45,054 [12] DEBUG Microsoft.EntityFrameworkCore.Database.Connection - Opening connection to database 'Sppd.TeamTuner-TEST' on server '.\SQLEXPRESS'.
2019-04-11 16:02:45,057 [12] DEBUG Microsoft.EntityFrameworkCore.Database.Connection - Opened connection to database 'Sppd.TeamTuner-TEST' on server '.\SQLEXPRESS'.
2019-04-11 16:02:45,060 [12] DEBUG Microsoft.EntityFrameworkCore.Database.Command - Executing DbCommand [Parameters=[@__entityId_0='?' (DbType = Guid)], CommandType='Text', CommandTimeout='30']
SELECT TOP(2) [m].[Id], [m].[Avatar], [m].[CreatedById], [m].[CreatedOnUtc], [m].[DeletedById], [m].[DeletedOnUtc], [m].[Description], [m].[FederationId], [m].[IsDeleted], [m].[ModifiedById], [m].[ModifiedOnUtc], [m].[Name]
FROM [Team] AS [m]
WHERE ([m].[IsDeleted] = 0) AND ([m].[Id] = @__entityId_0)
2019-04-11 16:02:45,067 [14] INFO  Microsoft.EntityFrameworkCore.Database.Command - Executed DbCommand (7ms) [Parameters=[@__entityId_0='?' (DbType = Guid)], CommandType='Text', CommandTimeout='30']
SELECT TOP(2) [m].[Id], [m].[Avatar], [m].[CreatedById], [m].[CreatedOnUtc], [m].[DeletedById], [m].[DeletedOnUtc], [m].[Description], [m].[FederationId], [m].[IsDeleted], [m].[ModifiedById], [m].[ModifiedOnUtc], [m].[Name]
FROM [Team] AS [m]
WHERE ([m].[IsDeleted] = 0) AND ([m].[Id] = @__entityId_0)
2019-04-11 16:02:45,092 [14] DEBUG Microsoft.EntityFrameworkCore.Database.Command - A data reader was disposed.
2019-04-11 16:02:45,143 [14] DEBUG Microsoft.EntityFrameworkCore.Database.Connection - Closing connection to database 'Sppd.TeamTuner-TEST' on server '.\SQLEXPRESS'.
2019-04-11 16:02:45,153 [14] DEBUG Microsoft.EntityFrameworkCore.Database.Connection - Closed connection to database 'Sppd.TeamTuner-TEST' on server '.\SQLEXPRESS'.

What I've tried:

  • Do not specify string but an expression to specify navigation property to load:

    protected IQueryable<TEntity> GetQueryableWithIncludes(IEnumerable<string> includeProperties = null)
    {
        var queryable = Set;
    
        if (includeProperties == null)
        {
            return queryable;
        }
    
        if (typeof(TEntity) == typeof(Team))
        {
            // TODO: Remove this block once it works by including by string properties
            foreach (var propertyName in includeProperties)
            {
                if (propertyName == "Users")
    
                {
                    queryable.OfType<Team>().Include(entity => entity.Users);
                }
            }
        }
        else
        {
            foreach (var propertyName in includeProperties)
            {
                queryable.Include(propertyName);
            }
        }
    
        return queryable;
    }
    
  • Explicitly configure the relation for the user entity as well:

    private static void ConfigureTeamTunerUser(EntityTypeBuilder<TeamTunerUser> builder)
    {
        ConfigureDescriptiveEntity(builder);
    
        builder.HasMany(e => e.CardLevels)
               .WithOne(e => e.User);
    
        builder.HasOne(e => e.Team)
               .WithMany(e => e.Users);
    
        // Indexes and unique constraint
        builder.HasIndex(e => e.Name)
               .IsUnique();
        builder.HasIndex(e => e.SppdName)
               .IsUnique();
        builder.HasIndex(e => e.Email)
               .IsUnique();
    }
    

What am I missing?

1
3
4/11/2019 2:23:57 PM

Accepted Answer

Include / ThenInclude (and all other EF Core Queryable extensions) are like regular LINQ Queryable methods (Select, Where, OrderBy etc.) which modify the source IQueryable<> and return the modified IQueryable<>.

Here you simply forgot to use the resulting query, so

queryable.Include(propertyName);

has the same effect as

queryable.Where(e => false);

i.e. no effect.

Simply change the code to

queryable = queryable.Include(propertyName);
5
4/11/2019 3:57:03 PM

Popular Answer

I noticed a few issues.

Your approach only works for loading the first level of navigation properties.

foreach (var propertyName in includeProperties)
{
    queryable.Include(propertyName);
} 

You must use .ThenInclude() for loading nested navigation properties. That breaks your approach of IEnumerable<string> includeProperties = null as a constructor though.

The second issue is with your test itself. It only checks .Any(), but per the name of the test, that is the wrong assertion. (We don't know if the test is failing because the navigation property never loaded OR it did load successfully, but there are zero Users. You should only be checking that the navigation property was loaded. Something like the following.

DbContext.Entry(createdTeamWithUsers).Navigation("Users").IsLoaded



Related Questions





Related

Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow
Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow