"Invalid column name" in nested collection $expand with $top

[davidyee]

After upgrading to EF Core 3.1 from 2.2 alongside the latest OData 7.3, OData appears to now generate invalid SQL when running an $expand on a collection with $top limiting both the top-level entity and the nested, expanded collection. This issue is likely related to an issue in EF Core though I don't know what LINQ is being passed from OData to cause such an issue.

This OData controller method generates an invalid SQL:

[HttpGet]
[EnableQuery]
public IQueryable<Employer> Get()
{
    return _dbContext.Employer;
}

When queried via http://localhost:5000/employer?$expand=employees($expand=department;$top=100)&$top=100

Assemblies affected

• Microsoft.AspNetCore.OData 7.3.0
• Microsoft.EntityFrameworkCore 3.1.1

Local environment

• dotnet 3.1.101
• LocalDB 2019 CU1 15.0.4003.23

Reproduce steps

Please see this repository for a reproducible sample:
https://github.com/davidyee/CollectionNavigationSample

Model layout:

public class Employer
{
    [Key]
    public int Id { get; set; }

    public string Name { get; set; }

    public virtual ICollection<Employee> Employees { get; set; }
}
	
public class Employee
{
    [Key]
    public int Id { get; set; }

    public string Name { get; set; }

    public virtual Employer Employer { get; set; }
    public int EmployerId { get; set; }

    public virtual Department Department { get; set; }
    public int DepartmentId { get; set; }
}
	
public class Department
{
    [Key]
    public int Id { get; set; }

    public string Name { get; set; }
}

Expected result

I expected the result with expansions:

[{"employees":[{"department":{"id":1,"name":"Science"},"id":1,"name":"Jack","employerId":1,"departmentId":1},{"department":{"id":2,"name":"Arts"},"id":2,"name":"Jane","employerId":1,"departmentId":2}],"id":1,"name":"Steven"}]

Actual result

Exception stacktrace:

Microsoft.Data.SqlClient.SqlException (0x80131904): Invalid column name 'DepartmentId'.
Invalid column name 'DepartmentId'.
Invalid column name 'EmployerId'.
   at Microsoft.Data.SqlClient.SqlCommand.<>c.<ExecuteDbDataReaderAsync>b__164_0(Task`1 result)
   at System.Threading.Tasks.ContinuationResultTaskFromResultTask`2.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.ExecutionContext.RunInternal(ExecutionContext executionContext, ContextCallback callback, Object state)
   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.QueryingEnumerable`1.AsyncEnumerator.InitializeReaderAsync(DbContext _, Boolean result, CancellationToken cancellationToken)
   at Microsoft.EntityFrameworkCore.SqlServer.Storage.Internal.SqlServerExecutionStrategy.ExecuteAsync[TState,TResult](TState state, Func`4 operation, Func`4 verifySucceeded, CancellationToken cancellationToken)
   at Microsoft.EntityFrameworkCore.Query.Internal.QueryingEnumerable`1.AsyncEnumerator.MoveNextAsync()
   at Microsoft.AspNetCore.Mvc.Infrastructure.AsyncEnumerableReader.ReadInternal[T](IAsyncEnumerable`1 value)
   at Microsoft.AspNetCore.Mvc.Infrastructure.AsyncEnumerableReader.ReadInternal[T](IAsyncEnumerable`1 value)
   at Microsoft.AspNetCore.Mvc.Infrastructure.ObjectResultExecutor.ExecuteAsyncEnumerable(ActionContext context, ObjectResult result, IAsyncEnumerable`1 asyncEnumerable)
   at Microsoft.AspNetCore.Mvc.Infrastructure.ResourceInvoker.<InvokeNextResultFilterAsync>g__Awaited|29_0[TFilter,TFilterAsync](ResourceInvoker invoker, Task lastTask, State next, Scope scope, Object state, Boolean isCompleted)
   at Microsoft.AspNetCore.Mvc.Infrastructure.ResourceInvoker.Rethrow(ResultExecutedContextSealed context)
   at Microsoft.AspNetCore.Mvc.Infrastructure.ResourceInvoker.ResultNext[TFilter,TFilterAsync](State& next, Scope& scope, Object& state, Boolean& isCompleted)
   at Microsoft.AspNetCore.Mvc.Infrastructure.ResourceInvoker.<InvokeResultFilters>g__Awaited|27_0(ResourceInvoker invoker, Task lastTask, State next, Scope scope, Object state, Boolean isCompleted)
   at Microsoft.AspNetCore.Mvc.Infrastructure.ResourceInvoker.<InvokeFilterPipelineAsync>g__Awaited|19_0(ResourceInvoker invoker, Task lastTask, State next, Scope scope, Object state, Boolean isCompleted)
   at Microsoft.AspNetCore.Mvc.Infrastructure.ResourceInvoker.<InvokeAsync>g__Awaited|17_0(ResourceInvoker invoker, Task task, IDisposable scope)
   at Microsoft.AspNetCore.Builder.RouterMiddleware.Invoke(HttpContext httpContext)
   at Microsoft.AspNetCore.Authorization.AuthorizationMiddleware.Invoke(HttpContext context)
   at Microsoft.AspNetCore.Diagnostics.DeveloperExceptionPageMiddleware.Invoke(HttpContext context)
ClientConnectionId:59aefd83-794b-4d05-8127-95a991ffb9c3
Error Number:207,State:1,Class:16

Generated SQL as retrieved from SQL Profiler:

exec sp_executesql N'SELECT [t].[Id], [t].[Name], [t1].[c], [t1].[Id], [t1].[DepartmentId], [t1].[EmployerId], [t1].[Name], [t1].[c0], [t1].[c1], [t1].[c2], [t1].[Id0], [t1].[Name0], [t1].[c3]
FROM (
    SELECT TOP(@__TypedProperty_4) [e].[Id], [e].[Name]
    FROM [Employer] AS [e]
    ORDER BY [e].[Id]
) AS [t]
OUTER APPLY (
    SELECT @__TypedProperty_2 AS [c], [t].[Id], [t].[DepartmentId], [t].[EmployerId], [t].[Name], CAST(1 AS bit) AS [c0], N''Department'' AS [c1], @__TypedProperty_3 AS [c2], [d].[Id] AS [Id0], [d].[Name] AS [Name0], CAST(0 AS bit) AS [c3]
    FROM (
        SELECT TOP(@__TypedProperty_1) [e0].[Id], [e0].[DepartmentId], [e0].[EmployerId], [e0].[Name]
        FROM [Employee] AS [e0]
        WHERE [t].[Id] = [e0].[EmployerId]
        ORDER BY [e0].[Id]
    ) AS [t0]
    INNER JOIN [Department] AS [d] ON [t].[DepartmentId] = [d].[Id]
) AS [t1]
ORDER BY [t].[Id], [t1].[Id], [t1].[Id0]',N'@__TypedProperty_4 int,@__TypedProperty_2 nvarchar(4000),@__TypedProperty_3 nvarchar(4000),@__TypedProperty_1 int',@__TypedProperty_4=100,@__TypedProperty_2=N'e981a185-886b-4d22-9614-151280e31234',@__TypedProperty_3=N'e981a185-886b-4d22-9614-151280e31234',@__TypedProperty_1=100

It would appear that the three references to [t] in [t].[DepartmentId], [t].[EmployerId] and the INNER JOIN [Department] AS [d] ON [t].[DepartmentId] = [d].[Id] should actually be [t0]. Replacing these references from [t] to [t0] appears to allow the SQL to execute without error.

Additional detail

This issue might be related to the same root cause of dotnet/efcore#17809. Ultimately this issue may actually be an EF Core 3.1 issue although I could only create a reproducible sample in conjunction with an OData controller.

[joakimriedel]

Seen this @smitpatel ? Looks very similar to my issue.

[Terriuss]

Can confirm this issue. This also happening when expanding multiple levels. The SQL Statement get's wrong alias names.

(Bad) workaround: Return IEnumerable instead of IQueryable and use ".ToList()". Then it will get all items from sql and filter in memory (this is working, but not performance wise). But I think this confirms that the sql generation is wrong.

[gathogojr]

@davidyee @Shigerua Kindly note that I was able to reproduce the issue. I made the same observations as you did and filed an issue with EF Core team to investigate. I also provided them with a repro stripped off OData bells and whistles that could create unnecessary distraction

[shammelburg]

Also getting this error, its concatenating the ClassName + PrimaryKey for me.

Only when using $expand

{
    "Type": "SQL Exception",
    "Exceptions": [
        {
            "Message": "Invalid column name 'ProductProductId'.",
            "Procedure": "",
            "LineNumber": 7,
            "Source": "Core Microsoft SqlClient Data Provider",
            "Server": "mydb.database.windows.net"
        },
        {
            "Message": "Invalid column name 'ProductProductId'.",
            "Procedure": "",
            "LineNumber": 1,
            "Source": "Core Microsoft SqlClient Data Provider",
            "Server": "mydb.database.windows.net"
        }
    ]
}

StackTrace
Microsoft.Data.SqlClient.SqlException (0x80131904): Invalid column name 'ProductProductId'. Invalid column name 'ProductProductId'. at Microsoft.Data.SqlClient.SqlCommand.<>c.<ExecuteDbDataReaderAsync>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.ExecutionContext.RunInternal(ExecutionContext executionContext, ContextCallback callback, Object state) 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() at Microsoft.AspNetCore.Mvc.Infrastructure.AsyncEnumerableReader.ReadInternal[T](IAsyncEnumerable1 value) at Microsoft.AspNetCore.Mvc.Infrastructure.AsyncEnumerableReader.ReadInternal[T](IAsyncEnumerable1 value) at Microsoft.AspNetCore.Mvc.Infrastructure.ObjectResultExecutor.ExecuteAsyncEnumerable(ActionContext context, ObjectResult result, IAsyncEnumerable1 asyncEnumerable) at Microsoft.AspNetCore.Mvc.Infrastructure.ResourceInvoker.g__Awaited|29_0[TFilter,TFilterAsync](ResourceInvoker invoker, Task lastTask, State next, Scope scope, Object state, Boolean isCompleted) at Microsoft.AspNetCore.Mvc.Infrastructure.ResourceInvoker.Rethrow(ResultExecutedContextSealed context) at Microsoft.AspNetCore.Mvc.Infrastructure.ResourceInvoker.ResultNext[TFilter,TFilterAsync](State& next, Scope& scope, Object& state, Boolean& isCompleted) at Microsoft.AspNetCore.Mvc.Infrastructure.ResourceInvoker.g__Awaited|27_0(ResourceInvoker invoker, Task lastTask, State next, Scope scope, Object state, Boolean isCompleted) at Microsoft.AspNetCore.Mvc.Infrastructure.ResourceInvoker.g__Awaited|19_0(ResourceInvoker invoker, Task lastTask, State next, Scope scope, Object state, Boolean isCompleted) at Microsoft.AspNetCore.Mvc.Infrastructure.ResourceInvoker.g__Logged|17_1(ResourceInvoker invoker) at Microsoft.AspNetCore.Builder.RouterMiddleware.Invoke(HttpContext httpContext) at Swashbuckle.AspNetCore.SwaggerUI.SwaggerUIMiddleware.Invoke(HttpContext httpContext) at Swashbuckle.AspNetCore.Swagger.SwaggerMiddleware.Invoke(HttpContext httpContext, ISwaggerProvider swaggerProvider) at CoreIdentity.API.Middleware.ErrorHandlingMiddleware.Invoke(HttpContext httpContext, IEmailService _emailService) in '':line 26 ClientConnectionId:1868f085-a868-4067-93d7-339560de648a Error Number:207,State:1,Class:16
`

[msdmcmurrian]

Yep same here.

[arilishu]

Hi! Any workaround?
I am having the same issue:
public class Leads
{
[Key]
[DatabaseGenerated(DatabaseGeneratedOption.Identity)]
public int id { get; set; }
public string Name { get; set; }
public virtual ICollection Cars { get; set; }
}
public class Cars
{
[Key]
public int id { get; set; }
[ForeignKey("FK_Cars_Leads")]
public int Lead { get; set; }
public virtual Leads Leads { get; set; }
}
Invalid column name 'Leadsid'.

[kgamecarter]

same problem

[mixa3607]

have same problem
/odata/VersionedFileAttachment?$expand=Files($top=10)

System.Reflection.TargetInvocationException: Exception has been thrown by the target of an invocation.
 ---> Npgsql.PostgresException (0x80004005): 42703: column t.Extension does not exist

[korygin]

Any ETA on when this will be fixed? We are seeing the same error with any $expand that has $top query option.

[stephen-wood83]

I'm seeing this issue when using $expand.
In my case I am not attempting to skip/top the expanded collection just the outer collection e.g.
$expand=Teams&$skip=0&$top=10

I get invalid column errors with the columns coming from the Teams collection.

[szabolcs-kun]

I'm facing with the same issue with my N:1 tables when i'm using nested $expand like /api/GetAllUsers?$expand=accessManagementGroupXUser($expand=group).

The higher versions of the assemblies are also affected:

• Microsoft.AspNetCore.OData 7.5.6
• Microsoft.EntityFrameworkCore 5.0.3

Framework: .NET Core 3.1
DB: Azure SQL Database
The EF Core models and DB context in my project is scaffolded (DB-first).

Temporary work-around: Eager loading the references with Include and ThenInclude methods. Of course, eager loading multiple collection navigations in a single query without any scoping can cause performance issues, expecially with big tables.

await _accessManagementUserRepository
            .GetAll()
            .Where(x => !x.DeletedFlag)
            .Include(x => x.AccessManagementGroupXUser.Where(y => !y.DeletedFlag)
            .ThenInclude(x => x.Group)
            .ToListAsync();

[spaasis]

As another workaround for our nested $expand (A->B->C) issue we have instructed our users to query B with $expand=A,C which works but is not intuitive for the specific use case

[Sergey-Terekhin]

Same issue, but it's reproduced only if PageSize property is set in the EnableQuery. Without this property another query is generated which completes successfully

[szabolcs-kun]

Same issue, but it's reproduced only if PageSize property is set in the EnableQuery. Without this property another query is generated which completes successfully

Hmmm, interesting! Do you have any suggestions then where we can limit the page size?

[omnilogix]

Same issue, but it's reproduced only if PageSize property is set in the EnableQuery. Without this property another query is generated which completes successfully

I am having the same issue with nested $expand
Removing PageSize from the [EnableQuery] provided a workaround for me

//        [EnableQuery(MaxTop = 200, PageSize = 200]
        [EnableQuery(MaxTop = 200)]
        public override async Task<ActionResult> Get(int? id)
...

[yogesh2021-Indore]

anyone have the solution or not? Please help me out in the code I have shared...

[yogesh2021-Indore]

var departments= new List();
var lstDepartment = await _machineTestContext.Department.ToListAsync();

//1. ----------------------I tried this also getting error as in point 2
var lstEmployees = _machineTestContext.Employee.FirstOrDefault();
//----------------------

        foreach (var item in lstDepartment )
        {

//2. Getting error here as i have foreign key in Employee Table i.e DepartmentId
//Error Invalid column name 'MyProperty'. OR invalid column DepartmentDepartmentId
var employers = await _machineTestContext.Employer.Where(x => x.DepartmentId== item.DepartmentId).ToListAsync();
departments.Add(
new Department
{
AltId = item.AltId,
Employees = employees,
});
}

[korygin]

I believe Microsoft has it fixed in EF Core 6. They are not going to make any fixes in prior versions.

[Reena-Patel]

It is not working with Microsoft.EntityFrameworkCore.SqlServer 5.0.10 also.

[gathogojr]

@Reena-Patel Can you try 6.x?

[Reena-Patel]

@gathogojr Yes, it is working with 6.x.