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

Fastest Entity Framework Extensions

Bulk Insert
Bulk Delete
Bulk Update
Bulk Merge

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

Brad M

Related Questions

entity framework does not load navigation property after adding object

new Stage()...Don't do this with EF objects. You won't get the correct proxy object that can lazy load navigation properties. You need to use something like ...dbContext.Set<Stage>().Create()
c# entity-framework entity-framework-6
asked by Brad M

Determine if property is a Navigation Property in EF Core

I'm building a simple change tracker to capture all the edits to a Sql Azure database (unfortunately, Sql Azure doesn't support this natively, so far as I can tell)....I'm walking the list of modified entries returned by ChangeTracker():...foreach( EntityEntry entry in _context.ChangeTracker.Entries() .Where( e => e.State == EntityState.Modifie...
c# entity-framework-core
asked by Mark Olbert

How to load the navigation property with EF Core?

You have 3 methods:...1. Eager loading...e.g....var blogs = context.Blogs .Include(blog => blog.Posts) .ToList(); ...2. Explicit loading...e.g....var blog = context.Blogs .Single(b => b.BlogId == 1); context.Posts .Where(p => p.BlogId == blog.BlogId) .Load(); ...3. Lazy loading (as of EF Core 2.1)...protected override void OnCo...
entity-framework entity-framework-core
asked by Sampath

Include after select not load navigation property in Entity Framework core

I use entity framework core 1.1....I have a query like below, and I expect to users who have UserProfile by using Include, load UserProfile. But this query always return UserProfile null ....Query:...var user = dbContext.UserMappers .Where(e => e.OldUserId == id) .Select(e => e.User) .Include(e=>e.UserProfile) .FirstOrDefault(); ...
.net-core entity-framework entity-framework-core
asked by Mohammad Akbari

navigation property should be virtual - not required in ef core?

virtual... was never ...required... in EF. It was needed only if you want lazy loading support....Since ...Lazy loading is not yet supported by EF Core..., currently ...virtual... have no special meaning. It would when (and if) they add lazy loading support (there is a ...plan... for doing so)....Update:... Starting with EF Core 2.1, ...Lazy loadi...
c# entity-framework entity-framework-core navigation-properties virtual
asked by Ivan Stoev

EF core: cannot insert object with foreign key using ID properties (only navigation properties work)

I'm using Entity Framework Core to create my (code first) database model.... In the model I have these classes:...[Table("groups")] public class Group { public int? Id { get; set; } [JsonIgnore] public virtual ICollection<Membership> Members { get; set; } } [Table("identities")] public class Identity { public int? Id { get; set; } ...
c# entity-framework-core
asked by Paolo Tedesco

ASP.NET Core EF Not Finding Navigation Property

I'm making a survey app and have 3 domain models:...ActivityItem... for each activity I want to ask about....ItemResponse... to tie together an ...ActivityItem... and ...Checklist...Checklist... to hold all the ...ItemResponses...I want to get the ...Checklist.......ItemResponse.......ActivityItem.......Name... in the view, but ...ActivityItem... i...
asp.net-core entity-framework-core
asked by Kyle Carlson

EF Core - Removing a related entity from collection navigation property does not update database

I am trying to understand this sentence:...By default, for required relationships, a cascade delete behavior is configured and the child/dependent entity will be deleted from the database....found in the "Removing relationships" paragraph in this Microsoft documentation of EF Core: ...https://docs.microsoft.com/en-us/ef/core/saving/related-data#rem...
asp.net-core c# entity-framework entity-framework-core
asked by Ray Roy

Virtual property not getting loaded after lazy loading too in ASP.NET Core

The version I'm currently using ...<PackageReference Include="Microsoft.EntityFrameworkCore.Proxies" Version="2.2.4" /> ...Using below code work for me...services.AddDbContextPool<ApplicationDbContext>(options => options.UseLazyLoadingProxies() .UseSqlServer(configuration.GetConnectionString("DefaultConnection")); ...You can remove this line of c...
.net-core asp.net-core asp.net-core-mvc c# entity-framework-core
asked by Tony Ngo

Setup EF core to not create Database Foreign Key for specific Navigation Property

Consider entities ...Company... and ...Product...:...public class Company { prop Guid Id {get; set;} } public class Product { prop Guid CompanyId {get; set;} prop Company Company {get; set;} } ...When creating migrations, I want the relationship from ...Product... to ...Company... not to create a foreign key constraint in the database....
c# ef-migrations entity-framework entity-framework-core
asked by Alireza

Fastest Entity Framework Extensions

Bulk Insert
Bulk Delete
Bulk Update
Bulk Merge
View more on Stack Overflow

Prime Library

Performance

Expression Evaluator

More Projects...

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