EF Core 3.1.4 Fails to read results from generated SQL

[bart-degreed]

When using Skip/Take in a nested query, an exception is thrown along the lines of "column t.AuthorId does not exist". I believe this is related to EF Core itself, because both PostgreSQL and SQL Server generate a similar error.

Steps to reproduce

I'm regenerating the database from code on each run, without adding any data.

Query:

var query = _appDbContext.Blogs
    // .Skip(2).Take(3) // <--- causes crash when BOTH lines are uncommented
    .Select(blog => new Blog
    {
        Articles = blog.Articles
            // .Skip(1).Take(5) // <--- causes crash when BOTH lines are uncommented
            .Select(article => new Article
            {
                Author = article.Author
            })
            .ToList()
    });

Models:

public abstract class Identifiable
{
    public long Id { get; set; }
}

public class Blog : Identifiable
{
    public string Name { get; set; }

    public ICollection<Article> Articles { get; set; }
}

public class Article : Identifiable
{
    public string Title { get; set; }
    public string Url { get; set; }

    public Author Author { get; set; }

    public ICollection<Revision> Revisions { get; set; }
}

public class Author : Identifiable
{
    public string FirstName { get; set; }
    public string LastName { get; set; }
    public int? Age { get; set; }
    public string PrivateEmail { get; set; }
    public string BusinessEmail { get; set; }
}

public class Revision : Identifiable
{
    public DateTime PublishTime { get; set; }
}

Uncomment both lines in code above to cause the crash. Result when using PostgreSQL:

--Microsoft.EntityFrameworkCore.Database.Command: Error: Failed executing DbCommand (293ms) [Parameters=[@__p_1='3', @__p_0='2'], CommandType='Text', CommandTimeout='30']
SELECT t."Id", t1."Id", t1."Age", t1."BusinessEmail", t1."FirstName", t1."LastName", t1."PrivateEmail", t1."Id0"
FROM (
    SELECT b."Id"
    FROM "Blogs" AS b
    ORDER BY (SELECT 1)
    LIMIT @__p_1 OFFSET @__p_0
) AS t
LEFT JOIN LATERAL (
    SELECT a0."Id", a0."Age", a0."BusinessEmail", a0."FirstName", a0."LastName", a0."PrivateEmail", t."Id" AS "Id0"
    FROM (
        SELECT a."Id", a."AuthorId", a."BlogId", a."Title", a."Url"
        FROM "Article" AS a
        WHERE t."Id" = a."BlogId"
        ORDER BY (SELECT 1)
        LIMIT 5 OFFSET 1
    ) AS t0
    LEFT JOIN "Authors" AS a0 ON t."AuthorId" = a0."Id"
) AS t1 ON TRUE
ORDER BY t."Id", t1."Id0"

Stack trace:

Exception thrown: 'Npgsql.PostgresException' in Microsoft.EntityFrameworkCore.Relational.dll
Exception thrown: 'Npgsql.PostgresException' in Npgsql.EntityFrameworkCore.PostgreSQL.dll
'iisexpress.exe' (CoreCLR: clrhost): Loaded 'C:\Program Files\dotnet\shared\Microsoft.NETCore.App\3.1.4\System.Diagnostics.StackTrace.dll'. 
'iisexpress.exe' (CoreCLR: clrhost): Loaded 'C:\Program Files\dotnet\shared\Microsoft.NETCore.App\3.1.4\System.Reflection.Metadata.dll'. 
Microsoft.EntityFrameworkCore.Query: Error: An exception occurred while iterating over the results of a query for context type 'EntityFrameworkWebApplication.Database.AppDbContext'.
Npgsql.PostgresException (0x80004005): 42703: column t.AuthorId does not exist
   at Npgsql.NpgsqlConnector.<>c__DisplayClass160_0.<<DoReadMessage>g__ReadMessageLong|0>d.MoveNext()
--- End of stack trace from previous location where exception was thrown ---
   at Npgsql.NpgsqlConnector.<>c__DisplayClass160_0.<<DoReadMessage>g__ReadMessageLong|0>d.MoveNext()
--- End of stack trace from previous location where exception was thrown ---
   at Npgsql.NpgsqlDataReader.NextResult(Boolean async, Boolean isConsuming)
   at Npgsql.NpgsqlDataReader.NextResult()
   at Npgsql.NpgsqlCommand.ExecuteReaderAsync(CommandBehavior behavior, Boolean async, CancellationToken cancellationToken)
   at Npgsql.NpgsqlCommand.ExecuteReader(CommandBehavior behavior)
   at Npgsql.NpgsqlCommand.ExecuteDbDataReader(CommandBehavior behavior)
   at System.Data.Common.DbCommand.ExecuteReader()
   at Microsoft.EntityFrameworkCore.Storage.RelationalCommand.ExecuteReader(RelationalCommandParameterObject parameterObject)
   at Microsoft.EntityFrameworkCore.Query.Internal.QueryingEnumerable`1.Enumerator.InitializeReader(DbContext _, Boolean result)
   at Npgsql.EntityFrameworkCore.PostgreSQL.Storage.Internal.NpgsqlExecutionStrategy.Execute[TState,TResult](TState state, Func`3 operation, Func`3 verifySucceeded)
   at Microsoft.EntityFrameworkCore.Query.Internal.QueryingEnumerable`1.Enumerator.MoveNext()
  Exception data:
    Severity: ERROR
    SqlState: 42703
    MessageText: column t.AuthorId does not exist
    Hint: Perhaps you meant to reference the column "t0.AuthorId".
    Position: 606
    File: parse_relation.c
    Line: 3359
    Routine: errorMissingColumn

When using SQL Server:

--Microsoft.EntityFrameworkCore.Database.Command: Error: Failed executing DbCommand (184ms) [Parameters=[@__p_0='2', @__p_1='3'], CommandType='Text', CommandTimeout='30']
SELECT [t].[Id], [t1].[Id], [t1].[Age], [t1].[BusinessEmail], [t1].[FirstName], [t1].[LastName], [t1].[PrivateEmail], [t1].[Id0]
FROM (
    SELECT [b].[Id]
    FROM [Blogs] AS [b]
    ORDER BY (SELECT 1)
    OFFSET @__p_0 ROWS FETCH NEXT @__p_1 ROWS ONLY
) AS [t]
OUTER APPLY (
    SELECT [a0].[Id], [a0].[Age], [a0].[BusinessEmail], [a0].[FirstName], [a0].[LastName], [a0].[PrivateEmail], [t].[Id] AS [Id0]
    FROM (
        SELECT [a].[Id], [a].[AuthorId], [a].[BlogId], [a].[Title], [a].[Url]
        FROM [Article] AS [a]
        WHERE [t].[Id] = [a].[BlogId]
        ORDER BY (SELECT 1)
        OFFSET 1 ROWS FETCH NEXT 5 ROWS ONLY
    ) AS [t0]
    LEFT JOIN [Authors] AS [a0] ON [t].[AuthorId] = [a0].[Id]
) AS [t1]
ORDER BY [t].[Id], [t1].[Id0]

Stack trace:

Exception thrown: 'Microsoft.Data.SqlClient.SqlException' in Microsoft.EntityFrameworkCore.Relational.dll
'iisexpress.exe' (CoreCLR: clrhost): Loaded 'C:\Program Files\dotnet\shared\Microsoft.NETCore.App\3.1.4\System.Diagnostics.StackTrace.dll'. 
'iisexpress.exe' (CoreCLR: clrhost): Loaded 'C:\Program Files\dotnet\shared\Microsoft.NETCore.App\3.1.4\System.Reflection.Metadata.dll'. 
Microsoft.EntityFrameworkCore.Query: Error: An exception occurred while iterating over the results of a query for context type 'EntityFrameworkWebApplication.Database.AppDbContext'.
Microsoft.Data.SqlClient.SqlException (0x80131904): Invalid column name 'AuthorId'.
   at Microsoft.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection, Action`1 wrapCloseInAction)
   at Microsoft.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection, Action`1 wrapCloseInAction)
   at Microsoft.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj, Boolean callerHasConnectionLock, Boolean asyncClose)
   at Microsoft.Data.SqlClient.TdsParser.TryRun(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj, Boolean& dataReady)
   at Microsoft.Data.SqlClient.SqlDataReader.TryConsumeMetaData()
   at Microsoft.Data.SqlClient.SqlDataReader.get_MetaData()
   at Microsoft.Data.SqlClient.SqlCommand.FinishExecuteReader(SqlDataReader ds, RunBehavior runBehavior, String resetOptionsString, Boolean isInternal, Boolean forDescribeParameterEncryption, Boolean shouldCacheForAlwaysEncrypted)
   at Microsoft.Data.SqlClient.SqlCommand.RunExecuteReaderTds(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, Boolean isAsync, Int32 timeout, Task& task, Boolean asyncWrite, Boolean inRetry, SqlDataReader ds, Boolean describeParameterEncryptionRequest)
   at Microsoft.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, TaskCompletionSource`1 completion, Int32 timeout, Task& task, Boolean& usedCache, Boolean asyncWrite, Boolean inRetry, String method)
   at Microsoft.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method)
   at Microsoft.Data.SqlClient.SqlCommand.ExecuteReader(CommandBehavior behavior)
   at Microsoft.Data.SqlClient.SqlCommand.ExecuteDbDataReader(CommandBehavior behavior)
   at System.Data.Common.DbCommand.ExecuteReader()
   at Microsoft.EntityFrameworkCore.Storage.RelationalCommand.ExecuteReader(RelationalCommandParameterObject parameterObject)
   at Microsoft.EntityFrameworkCore.Query.Internal.QueryingEnumerable`1.Enumerator.InitializeReader(DbContext _, Boolean result)
   at Microsoft.EntityFrameworkCore.SqlServer.Storage.Internal.SqlServerExecutionStrategy.Execute[TState,TResult](TState state, Func`3 operation, Func`3 verifySucceeded)
   at Microsoft.EntityFrameworkCore.Query.Internal.QueryingEnumerable`1.Enumerator.MoveNext()
ClientConnectionId:3b752ae7-4f85-439a-833c-256f5c4785ff
Error Number:207,State:1,Class:16

[maumar]

related issue: #20813

[bart-degreed]

Thanks for linking. I tried the suggested workaround (add ToListAsync() on the outer query), but I'm still getting the same error.