Ignored ThenInclude statement on generating query expression EF Core

Question

Foreword:

I have 2 projects on .NET Core 3.1, one EF Core 3.1.17 and another on 5.0.10 and in both I've used Include/ThenInclude the same way and all works good. Now I'm trying upgrade my project on 3.1.17 to version 5.0.10 and got some weird situation.

Problem:

When I'm trying to use ThenInclude statement in expression all entities in ThenInclude are ignoring when EF generate a query.

It's my expression:

var settings = await _dbRepository.GetRegionRoles(systemId)
            .Include(r => r.Region)
            .Include(sr => sr.SystemRole)
            .ThenInclude(ro => ro.Role)
            .Include(gs => gs.GroupSettings)
            .Where(w => w.GroupSettings.Any())
            .ToListAsync(cancellationToken);

And EF recognize all ThenInclude statement before generating query(I tried to add some more where it's possible and got the same result), there is trace log:

 Compiling query expression: 
'DbSet<RegionRole>()
    .Include(x => x.SystemRole)
    .Where(w => w.SystemRole.SystemId.Equals((int)__systemId_0))
    .Include(r => r.Region)
    .Include(sr => sr.SystemRole)
    .ThenInclude(ro => ro.Role)
    .Include(gs => gs.GroupSettings)
    .Include(sr => sr.SystemRole)
    .ThenInclude(s => s.SystemCode)
    .Where(w => w.GroupSettings
        .Any())'

But result of generation looks like:

Generated query execution expression: 
'queryContext => new SingleQueryingEnumerable<RegionRole>(
    (RelationalQueryContext)queryContext, 
    RelationalCommandCache.SelectExpression(
        Projection Mapping:
        SELECT r.Id, r.CreatedByUser, r.CreatedDate //... and many other fields from each table
        FROM Main.RegionRole AS r
        INNER JOIN Main.SystemRole AS s ON r.SystemRoleId == s.Id
        INNER JOIN Main.Region AS r0 ON r.RegionId == r0.Id
        LEFT JOIN Main.GroupSettings AS g ON r.Id == g.RegionRoleId
        WHERE (s.SystemId == CAST(@__systemId_0) AS int)) && EXISTS (
            Projection Mapping:
            SELECT 1
            FROM Main.GroupSettings AS g0
            WHERE (r.Id != NULL) && (r.Id == g0.RegionRoleId))
        ORDER BY r.Id ASC, s.Id ASC, r0.Id ASC, g.Id ASC), 
    Func<QueryContext, DbDataReader, ResultContext, SingleQueryResultCoordinator, RegionRole>, 
    K2Project.BE.DAL.Models.K2DbContext, 
    False, 
    False
)'

So as we can see here, entity "Role" that is in ThenInclude statement is missing in result.

All entities has navigation properties and described in fluent api. All tables have foreign keys.

There is an example of entity "SystemRole"

public class SystemRole
{        
    public Guid Id { get; set; }

    public int RoleId { get; set; }        

    #region Dependencies

    public virtual Role Role { get; set; }

    #endregion
 }

And fluent api:

public class SystemRoleEntityTypeConfiguration : IEntityTypeConfiguration<SystemRole>
{
    public void Configure(EntityTypeBuilder<SystemRole> builder)
    {
        builder.ToTable(nameof(SystemRole), DbConst.MainSchemaName);

        builder.HasKey(p => p.Id);

        builder.Property(p => p.Id)
            .ValueGeneratedOnAdd()
            .HasDefaultValueSql("NEWSEQUENTIALID()");

        builder.HasOne(p => p.Role)
            .WithMany()
            .HasForeignKey(f => f.RoleId)
            .IsRequired(true);

    }
}

Question:

What is difference between usage of ThenInclude in EF Core version 3.1.17 and 5.0.10 and why the same expression works on first version but don't works on another? Maybe I did something wrong or forget to change something when changed the framework version?

P.S. In my second project that was from beginning on EF Core 5.0.10 all expressions with Include/ThenInclude statements doing great.

Answer 1

That shows why it's important to include all code in questions.

This is the GetRegionRoles method :

public IQueryable<RegionRole> GetRegionRoles(SystemCodesEnum systemId)
{
    return _context.RegionRole
        .Include(x => x.SystemRole)
        .Where(w => w.SystemRole.SystemId.Equals((int)systemId));
}

So it already contains an Include and the same Include is repeated in your query:

var settings = await _dbRepository.GetRegionRoles(systemId)
            .Include(r => r.Region)
            .Include(sr => sr.SystemRole) // <= here
                .ThenInclude(ro => ro.Role)
            .Include(gs => gs.GroupSettings)
            .Where(w => w.GroupSettings.Any())
            .ToListAsync(cancellationToken);

When I reproduce this code I see that, apparently, EF is sensitive to placement of Include statements. In this case the similar Includes are separated by a Where statement. Somehow that causes EF (core 5) to ignore the repeated Include (followed by ThenInclude). It works if you remove the separation, simply by moving the Where statement:

public IQueryable<RegionRole> GetRegionRoles(SystemCodesEnum systemId)
{
    return _context.RegionRole
        .Where(w => w.SystemRole.SystemId.Equals((int)systemId))
        .Include(x => x.SystemRole);
}

I assume this is not intended behavior (why would it?). You may want to post a bug report, or see if it still happens in version 6.