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?
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);
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