Operating on Enum Flags using Enum.GetValues result in wrong SQL generated (_ord)

[melchiork]

Bug description

When using Enum.GetValues together with .HasFlags to produce a collection of enum values in an object from DB int column the SQL is generated, but it contain superfluous _ord column in select, which is not part of VALUES. This results in a runtime exception as selecting the _ord fails.

Example code:

[HttpGet("AllButNone")]
public async Task<IActionResult> GetAllButNone(CancellationToken token)
{
    var query = _context.TestEntities.Select(x => new Dto()
    {
        Id = x.Id,
        Names = Enum.GetValues<SomeEnum>().Where(f => x.SomeEnum.HasFlag(f) && f != SomeEnum.None)
            .Select(f => f.ToString())
    });

    return Ok(await query.ToListAsync(token));
}

Example of generated SQL:

SELECT [t].[Id], [v0].[Value], [v0].[_ord]
FROM [TestEntities] AS [t]
OUTER APPLY (
    SELECT [v].[Value], [v].[_ord]
    FROM (VALUES (CAST(0 AS int)), (1), (2), (4), (8)) AS [v]([Value])
    WHERE [t].[SomeEnum] & [v].[Value] = [v].[Value] AND [v].[Value] <> 0
) AS [v0]
ORDER BY [t].[Id], [v0].[_ord]

Your code

Minimal project to reproduce:
Repli1.zip

[HttpGet("AllButNone")]
public async Task<IActionResult> GetAllButNone(CancellationToken token)
{
    var query = _context.TestEntities.Select(x => new Dto()
    {
        Id = x.Id,
        Names = Enum.GetValues<SomeEnum>().Where(f => x.SomeEnum.HasFlag(f) && f != SomeEnum.None)
            .Select(f => f.ToString())
    });

    return Ok(await query.ToListAsync(token));
}

public class Dto()
{
    public int Id { get; set; }

    public IEnumerable<string> Names { get; set; }
}

public class TestEntity
{
    public int Id { get; set; }

    public SomeEnum SomeEnum { get; set; }
}

[Flags]
public enum SomeEnum
{
    None = 0,
    Some = 1,
    More = 2,
    EvenMore = 4,
    Max = 8
}

Stack traces

Microsoft.Data.SqlClient.SqlException (0x80131904): Invalid column name '_ord'.

   at Microsoft.Data.SqlClient.SqlCommand.<>c.<ExecuteDbDataReaderAsync>b__211_0(Task`1 result)

   at System.Threading.Tasks.ContinuationResultTaskFromResultTask`2.InnerInvoke()

   at System.Threading.ExecutionContext.RunInternal(ExecutionContext executionContext, ContextCallback callback, Object state)

--- End of stack trace from previous location ---

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

   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.SingleQueryingEnumerable`1.AsyncEnumerator.InitializeReaderAsync(AsyncEnumerator enumerator, 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.SingleQueryingEnumerable`1.AsyncEnumerator.MoveNextAsync()

   at Microsoft.EntityFrameworkCore.EntityFrameworkQueryableExtensions.ToListAsync[TSource](IQueryable`1 source, CancellationToken cancellationToken)

   at Microsoft.EntityFrameworkCore.EntityFrameworkQueryableExtensions.ToListAsync[TSource](IQueryable`1 source, CancellationToken cancellationToken)

   at Repli1.Controllers.TestController.GetAllButNone(CancellationToken token) in C:\Users\Melchior\source\repos\experiments\Repli1\Controllers\TestController.cs:line 35

   at Microsoft.AspNetCore.Mvc.Infrastructure.ActionMethodExecutor.TaskOfIActionResultExecutor.Execute(ActionContext actionContext, IActionResultTypeMapper mapper, ObjectMethodExecutor executor, Object controller, Object[] arguments)

   at Microsoft.AspNetCore.Mvc.Infrastructure.ControllerActionInvoker.<InvokeActionMethodAsync>g__Awaited|12_0(ControllerActionInvoker invoker, ValueTask`1 actionResultValueTask)

   at Microsoft.AspNetCore.Mvc.Infrastructure.ControllerActionInvoker.<InvokeNextActionFilterAsync>g__Awaited|10_0(ControllerActionInvoker invoker, Task lastTask, State next, Scope scope, Object state, Boolean isCompleted)

   at Microsoft.AspNetCore.Mvc.Infrastructure.ControllerActionInvoker.Rethrow(ActionExecutedContextSealed context)

   at Microsoft.AspNetCore.Mvc.Infrastructure.ControllerActionInvoker.Next(State& next, Scope& scope, Object& state, Boolean& isCompleted)

   at Microsoft.AspNetCore.Mvc.Infrastructure.ControllerActionInvoker.<InvokeInnerFilterAsync>g__Awaited|13_0(ControllerActionInvoker invoker, Task lastTask, State next, Scope scope, Object state, Boolean isCompleted)

   at Microsoft.AspNetCore.Mvc.Infrastructure.ResourceInvoker.<InvokeFilterPipelineAsync>g__Awaited|20_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.Mvc.Infrastructure.ResourceInvoker.<InvokeAsync>g__Awaited|17_0(ResourceInvoker invoker, Task task, IDisposable scope)

   at Microsoft.AspNetCore.Authorization.AuthorizationMiddleware.Invoke(HttpContext context)

   at Swashbuckle.AspNetCore.SwaggerUI.SwaggerUIMiddleware.Invoke(HttpContext httpContext)

   at Swashbuckle.AspNetCore.Swagger.SwaggerMiddleware.Invoke(HttpContext httpContext, ISwaggerProvider swaggerProvider)

   at Microsoft.AspNetCore.Authentication.AuthenticationMiddleware.Invoke(HttpContext context)

   at Microsoft.AspNetCore.Diagnostics.DeveloperExceptionPageMiddlewareImpl.Invoke(HttpContext context)

ClientConnectionId:8fd19af8-0863-4942-95df-0b88c2d22759

Error Number:207,State:1,Class:16

Verbose output

EF Core version

9.0.3

Database provider

Microsoft.EntityFrameworkCore.SqlServer

Target framework

.NET 8

Operating system

Windows 11

IDE

Visual Studio 2022 17.10.4

[cincuranet]

Related to #34954.

[melchiork]

Related to #34954.

The related issue mentions this behavior being a regression with a nicer error message before. But there is nothing missing in the SQL generated, if not for the superfluous _ord column the SQL should work.

[cincuranet]

#36159 fixed this. Now the query is correct:

SELECT [t].[Id], [v0].[Value], [v0].[_ord]
FROM [TestEntities] AS [t]
OUTER APPLY (
    SELECT [v].[Value], [v].[_ord]
    FROM (VALUES (0, CAST(0 AS int)), (1, 1), (2, 2), (3, 4), (4, 8)) AS [v]([_ord], [Value])
    WHERE [t].[SomeEnum] & [v].[Value] = [v].[Value] AND [v].[Value] <> 0
) AS [v0]
ORDER BY [t].[Id], [v0].[_ord]

[melchiork]

@cincuranet Thanks!