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