Using Take() in projection no longer generate valid sql query in EF Core 3.1

[bezaou]

The query used to work as expected in EF Core 2.2. However, since migrating to 3.1, the generated query is not valid anymore.

Steps to reproduce

Classes:

public class Group : IEntity
    {
        public string Id { get; set; }
        public string Name { get; set; }
        public string Description { get; set; }
        public ICollection<GroupRelation> GroupRelations { get; set; } = new List<GroupRelation>();
    }

public class GroupRelation : IEntity, IHasDisabled, IHasDeleted
    {
        public string UserId { get; set; }
        public ApplicationUser User { get; set; }
        public string GroupId { get; set; }
        public Group Group { get; set; }
        public GroupRelationType RelationType { get; set; }
        public bool IsDisabled { get; set; }
        public bool IsDeleted { get; set; }
    }

AppContext (in OnModelCreating method):

builder.Entity<GroupRelation>().ToTable(nameof(GroupRelations));
            builder.Entity<GroupRelation>().HasKey(t => new { t.UserId, t.GroupId, t.RelationType });
            builder.Entity<GroupRelation>().Property(p => p.IsDeleted);
            builder.Entity<GroupRelation>().Property(p => p.IsDisabled);
            builder.Entity<GroupRelation>()
                .HasOne(pt => pt.User)
                .WithMany(p => p.GroupRelations)
                .HasForeignKey(pt => pt.UserId);
            builder.Entity<GroupRelation>()
                .HasOne(pt => pt.Group)
                .WithMany(t => t.GroupRelations)
                .HasForeignKey(pt => pt.GroupId);
            builder.Entity<GroupRelation>().Property(p => p.RelationType).IsRequired();
            builder.Entity<GroupRelation>().HasQueryFilter(m => m.IsDeleted == false && m.IsDisabled == false);

Service:

public async Task<Group> GetGroup(string groupId)
        {
            var result = await _context.Groups
                .Include(g => g.GroupRelations)
                .ThenInclude(relation => relation.User).Select(g => new
            {
                Group = g,
                GroupMembers = g.GroupRelations.Take(3),
                GroupMembersCount = g.GroupRelations.Count(r => r.RelationType == GroupRelationType.Member)

            }).FirstOrDefaultAsync(g => g.Group.Id == groupId);
            return result.Group;
        }

Exception

Microsoft.Data.SqlClient.SqlException (0x80131904): Invalid column name 'UserId'.
Invalid column name 'UserId'.
Invalid column name 'GroupId'.
Invalid column name 'RelationType'.
Invalid column name 'IsDeleted'.
Invalid column name 'IsDisabled'.
at Microsoft.Data.SqlClient.SqlCommand.<>c.b__164_0(Task1 result) at System.Threading.Tasks.ContinuationResultTaskFromResultTask2.InnerInvoke()
at System.Threading.Tasks.Task.<>c.<.cctor>b__274_0(Object obj)
at System.Threading.ExecutionContext.RunInternal(ExecutionContext executionContext, ContextCallback callback, Object state)
--- End of stack trace from previous location where exception was thrown ---
at System.Threading.Tasks.Task.ExecuteWithThreadLocal(Task& currentTaskSlot, Thread threadPoolThread)
--- End of stack trace from previous location where exception was thrown ---
at Microsoft.EntityFrameworkCore.Storage.RelationalCommand.ExecuteReaderAsync(RelationalCommandParameterObject parameterObject, CancellationToken cancellationToken)
at Microsoft.EntityFrameworkCore.Storage.RelationalCommand.ExecuteReaderAsync(RelationalCommandParameterObject parameterObject, CancellationToken cancellationToken)
at Microsoft.EntityFrameworkCore.Storage.RelationalCommand.ExecuteReaderAsync(RelationalCommandParameterObject parameterObject, CancellationToken cancellationToken)
at Microsoft.EntityFrameworkCore.Query.Internal.QueryingEnumerable1.AsyncEnumerator.InitializeReaderAsync(DbContext _, Boolean result, CancellationToken cancellationToken) at Microsoft.EntityFrameworkCore.SqlServer.Storage.Internal.SqlServerExecutionStrategy.ExecuteAsync[TState,TResult](TState state, Func4 operation, Func4 verifySucceeded, CancellationToken cancellationToken) at Microsoft.EntityFrameworkCore.Query.Internal.QueryingEnumerable1.AsyncEnumerator.MoveNextAsync()

Produced Sql query

SELECT [t].[Id], [t].[AllowedConversation], [t].[Description], [t].[LocationKey], [t].[Name], [t].[c], [t1].[UserId], [t1].[GroupId], [t1].[RelationType], [t1].[IsDeleted], [t1].[IsDisabled], [t1].[Id], [t1].[AccessFailedCount], [t1].[ConcurrencyStamp], [t1].[DeletedReason], [t1].[DisabledReason], [t1].[Email], [t1].[EmailConfirmed], [t1].[FirstName], [t1].[IsDeleted0], [t1].[IsDisabled0], [t1].[LastName], [t1].[LocationKey], [t1].[LockoutEnabled], [t1].[LockoutEnd], [t1].[NormalizedEmail], [t1].[NormalizedUserName], [t1].[NotificationToken], [t1].[PasswordHash], [t1].[PhoneNumber], [t1].[PhoneNumberConfirmed], [t1].[SecurityStamp], [t1].[TwoFactorEnabled], [t1].[UserName], [t4].[UserId], [t4].[GroupId], [t4].[RelationType], [t4].[IsDeleted], [t4].[IsDisabled], [t4].[Id], [t4].[AccessFailedCount], [t4].[ConcurrencyStamp], [t4].[DeletedReason], [t4].[DisabledReason], [t4].[Email], [t4].[EmailConfirmed], [t4].[FirstName], [t4].[IsDeleted0], [t4].[IsDisabled0], [t4].[LastName], [t4].[LocationKey], [t4].[LockoutEnabled], [t4].[LockoutEnd], [t4].[NormalizedEmail], [t4].[NormalizedUserName], [t4].[NotificationToken], [t4].[PasswordHash], [t4].[PhoneNumber], [t4].[PhoneNumberConfirmed], [t4].[SecurityStamp], [t4].[TwoFactorEnabled], [t4].[UserName]
FROM (
SELECT TOP(1) [g0].[Id], [g0].[AllowedConversation], [g0].[Description], [g0].[LocationKey], [g0].[Name], (
SELECT COUNT(*)
FROM [GroupRelations] AS [g]
WHERE ((([g].[IsDeleted] = CAST(0 AS bit)) AND ([g].[IsDisabled] = CAST(0 AS bit))) AND ([g0].[Id] = [g].[GroupId])) AND ([g].[RelationType] = 2)) AS [c]
FROM [Groups] AS [g0]
WHERE [g0].[Id] = @__groupId_0
) AS [t]
LEFT JOIN (
SELECT [g1].[UserId], [g1].[GroupId], [g1].[RelationType], [g1].[IsDeleted], [g1].[IsDisabled], [t0].[Id], [t0].[AccessFailedCount], [t0].[ConcurrencyStamp], [t0].[DeletedReason], [t0].[DisabledReason], [t0].[Email], [t0].[EmailConfirmed], [t0].[FirstName], [t0].[IsDeleted] AS [IsDeleted0], [t0].[IsDisabled] AS [IsDisabled0], [t0].[LastName], [t0].[LocationKey], [t0].[LockoutEnabled], [t0].[LockoutEnd], [t0].[NormalizedEmail], [t0].[NormalizedUserName], [t0].[NotificationToken], [t0].[PasswordHash], [t0].[PhoneNumber], [t0].[PhoneNumberConfirmed], [t0].[SecurityStamp], [t0].[TwoFactorEnabled], [t0].[UserName]
FROM [GroupRelations] AS [g1]
INNER JOIN (
SELECT [a].[Id], [a].[AccessFailedCount], [a].[ConcurrencyStamp], [a].[DeletedReason], [a].[DisabledReason], [a].[Email], [a].[EmailConfirmed], [a].[FirstName], [a].[IsDeleted], [a].[IsDisabled], [a].[LastName], [a].[LocationKey], [a].[LockoutEnabled], [a].[LockoutEnd], [a].[NormalizedEmail], [a].[NormalizedUserName], [a].[NotificationToken], [a].[PasswordHash], [a].[PhoneNumber], [a].[PhoneNumberConfirmed], [a].[SecurityStamp], [a].[TwoFactorEnabled], [a].[UserName]
FROM [AspNetUsers] AS [a]
WHERE ([a].[IsDeleted] = CAST(0 AS bit)) AND ([a].[IsDisabled] = CAST(0 AS bit))
) AS [t0] ON [g1].[UserId] = [t0].[Id]
WHERE ([g1].[IsDeleted] = CAST(0 AS bit)) AND ([g1].[IsDisabled] = CAST(0 AS bit))
) AS [t1] ON [t].[Id] = [t1].[GroupId]
OUTER APPLY (
SELECT [t].[UserId], [t].[GroupId], [t].[RelationType], [t].[IsDeleted], [t].[IsDisabled], [t3].[Id], [t3].[AccessFailedCount], [t3].[ConcurrencyStamp], [t3].[DeletedReason], [t3].[DisabledReason], [t3].[Email], [t3].[EmailConfirmed], [t3].[FirstName], [t3].[IsDeleted] AS [IsDeleted0], [t3].[IsDisabled] AS [IsDisabled0], [t3].[LastName], [t3].[LocationKey], [t3].[LockoutEnabled], [t3].[LockoutEnd], [t3].[NormalizedEmail], [t3].[NormalizedUserName], [t3].[NotificationToken], [t3].[PasswordHash], [t3].[PhoneNumber], [t3].[PhoneNumberConfirmed], [t3].[SecurityStamp], [t3].[TwoFactorEnabled], [t3].[UserName]
FROM (
SELECT TOP(3) [g2].[UserId], [g2].[GroupId], [g2].[RelationType], [g2].[IsDeleted], [g2].[IsDisabled]
FROM [GroupRelations] AS [g2]
WHERE (([g2].[IsDeleted] = CAST(0 AS bit)) AND ([g2].[IsDisabled] = CAST(0 AS bit))) AND ([t].[Id] = [g2].[GroupId])
) AS [t2]
INNER JOIN (
SELECT [a0].[Id], [a0].[AccessFailedCount], [a0].[ConcurrencyStamp], [a0].[DeletedReason], [a0].[DisabledReason], [a0].[Email], [a0].[EmailConfirmed], [a0].[FirstName], [a0].[IsDeleted], [a0].[IsDisabled], [a0].[LastName], [a0].[LocationKey], [a0].[LockoutEnabled], [a0].[LockoutEnd], [a0].[NormalizedEmail], [a0].[NormalizedUserName], [a0].[NotificationToken], [a0].[PasswordHash], [a0].[PhoneNumber], [a0].[PhoneNumberConfirmed], [a0].[SecurityStamp], [a0].[TwoFactorEnabled], [a0].[UserName]
FROM [AspNetUsers] AS [a0]
WHERE ([a0].[IsDeleted] = CAST(0 AS bit)) AND ([a0].[IsDisabled] = CAST(0 AS bit))
) AS [t3] ON [t].[UserId] = [t3].[Id]
) AS [t4]
ORDER BY [t].[Id], [t1].[UserId], [t1].[GroupId], [t1].[RelationType], [t1].[Id], [t4].[UserId], [t4].[GroupId], [t4].[RelationType], [t4].[Id]

EF Core version: 3.1.1
Database provider: Microsoft.EntityFrameworkCore.SqlServer 3.1.1
Target framework: .NET Core 3.1
Operating system: Windows 10 Pro
IDE: Microsoft Visual Studio Community 2019 16.4.2

[ajcvickers]

@maumar Dupe?

[maumar]

indeed, dupe of #19763