dotnet / dotnet/efcore

Unknown column 'a.Value' in 'field list' when accessing array element index of an array parameter in a compiled query

Open
#37,383 1 comment 0 reactions 0 assignees View on GitHub
area-compiled-query area-query customer-reported
Dominant language
C#
Stars
14.8k
Forks
3.4k
PR merge metrics
PR metrics pending

Description

### Bug description

This doesn't work:

```cs
var func = EF.CompileAsyncQuery((ApplicationDbContext context, int[] array) => context.Products
.Where(e => array.Contains(e.Id))
.OrderBy(e => e.Id == array[0] ? 0 : (e.Id == array[1] ? 1 : -1))
.FirstOrDefault());
var result = await func(context, new int[] { 3, 5 });

// Non-compiled version works:
// int[] array = new int[] { 3, 5 };
// var result = await context.Products
// .Where(e => array.Contains(e.Id))
// .OrderBy(e => e.Id == array[0] ? 0 : (e.Id == array[1] ? 1 : -1))
// .FirstOrDefaultAsync();
```

```
MySqlConnector.MySqlException: 'Unknown column 'a.Value' in 'field list''
```

As an alternative, I could replace the array and instead have more parameters, but there is a limit of parameters that can be compiled.

I also tried to put `EF.Parameter` or `EF.Constant` in each `array[n]` just to try, and interestingly, it threw:
```
System.InvalidOperationException: 'The EF.Parameter/EF.Constant method may only be used with an argument that can be evaluated client-side and does not contain any reference to database-side entities.'
````
Which is odd because `array[0]` can be evaluated client-side and it's not a reference to a database-side entity. Not sure if that is relevant, should remove it or belongs to a different issue.

### Your code

```csharp
await using var context = new ApplicationDbContext();
await context.Database.EnsureDeletedAsync();
await context.Database.EnsureCreatedAsync();

var func = EF.CompileAsyncQuery((ApplicationDbContext context, int[] array) => context.Products
.Where(e => array.Contains(e.Id))
.OrderBy(e => e.Id == array[0] ? 0 : (e.Id == array[1] ? 1 : -1))
.FirstOrDefault());

var _ = await func(context, new int[] { 3, 5 });

// Non-compiled version works:
// int[] array = new int[] { 3, 5 };
// var _ = await context.Products
// .Where(e => array.Contains(e.Id))
// .OrderBy(e => e.Id == array[0] ? 0 : (e.Id == array[1] ? 1 : -1))
// .FirstOrDefaultAsync();

public class ApplicationDbContext : DbContext
{
public DbSet Products { get; set; }

protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
=> optionsBuilder
.UseSqlServer(Environment.GetEnvironmentVariable("Test__SqlServer__DefaultConnection"))
.LogTo(Console.WriteLine, LogLevel.Information)
.EnableSensitiveDataLogging();
}

public class Product
{
public int Id { get; set; }
}
```

### Stack traces

```text
[MySqlConnector.MySqlException no controlado]
> [Excepción] MySqlConnector.dll!MySqlConnector.Core.ServerSession.ReceiveReplyAsync(MySqlConnector.Protocol.Serialization.IOBehavior ioBehavior, System.Threading.CancellationToken cancellationToken) Línea 1081 C#
[Excepción] MySqlConnector.dll!MySqlConnector.Core.ResultSet.ReadResultSetHeaderAsync(MySqlConnector.Protocol.Serialization.IOBehavior ioBehavior) Línea 37 C#
[Excepción] MySqlConnector.dll!MySqlConnector.MySqlDataReader.ActivateResultSet(System.Threading.CancellationToken cancellationToken) Línea 131 C#
[Excepción] MySqlConnector.dll!MySqlConnector.MySqlDataReader.InitAsync(MySqlConnector.Core.CommandListPosition commandListPosition, MySqlConnector.Core.ICommandPayloadCreator payloadCreator, System.Collections.Generic.IDictionary cachedProcedures, MySqlConnector.Core.IMySqlCommand command, System.Data.CommandBehavior behavior, System.Diagnostics.Activity activity, MySqlConnector.Protocol.Serialization.IOBehavior ioBehavior, System.Threading.CancellationToken cancellationToken) Línea 487 C#
[Excepción] MySqlConnector.dll!MySqlConnector.Core.CommandExecutor.ExecuteReaderAsync(MySqlConnector.Core.CommandListPosition commandListPosition, MySqlConnector.Core.ICommandPayloadCreator payloadCreator, System.Data.CommandBehavior behavior, System.Diagnostics.Activity activity, MySqlConnector.Protocol.Serialization.IOBehavior ioBehavior, System.Threading.CancellationToken cancellationToken) Línea 56 C#
[Excepción] MySqlConnector.dll!MySqlConnector.MySqlCommand.ExecuteReaderAsync(System.Data.CommandBehavior behavior, MySqlConnector.Protocol.Serialization.IOBehavior ioBehavior, System.Threading.CancellationToken cancellationToken) Línea 357 C#
[Excepción] MySqlConnector.dll!MySqlConnector.MySqlCommand.ExecuteDbDataReaderAsync(System.Data.CommandBehavior behavior, System.Threading.CancellationToken cancellationToken) Línea 350 C#
[Excepción] Microsoft.EntityFrameworkCore.Relational.dll!Microsoft.EntityFrameworkCore.Storage.RelationalCommand.ExecuteReaderAsync(Microsoft.EntityFrameworkCore.Storage.RelationalCommandParameterObject parameterObject, System.Threading.CancellationToken cancellationToken) Desconocido
[Excepción] Microsoft.EntityFrameworkCore.Relational.dll!Microsoft.EntityFrameworkCore.Storage.RelationalCommand.ExecuteReaderAsync(Microsoft.EntityFrameworkCore.Storage.RelationalCommandParameterObject parameterObject, System.Threading.CancellationToken cancellationToken) Desconocido
[Excepción] StockManager.Server.dll!StockManager.Server.HostConfig.Initialize(Microsoft.Extensions.Hosting.IHost host) Desconocido
[Excepción] StockManager.Server.dll!StockManager.Server.Program.Main(string[] args) Desconocido
System.Private.CoreLib.dll!System.Runtime.ExceptionServices.ExceptionDispatchInfo.Throw() Desconocido
System.Private.CoreLib.dll!System.Runtime.CompilerServices.TaskAwaiter.ThrowForNonSuccess(System.Threading.Tasks.Task task) Desconocido
System.Private.CoreLib.dll!System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(System.Threading.Tasks.Task task, System.Threading.Tasks.ConfigureAwaitOptions options) Desconocido
[En espera de una operación asincrónica, haga doble clic o presione ENTRAR para ver las pilas de llamadas asincrónicas]
StockManager.Server.dll!StockManager.Server.Program.(string[] args) Desconocido
```

### Verbose output

```text

```

### EF Core version

9.0.8

### Database provider

Pomelo.EntityFrameworkCore.MySql

### Target framework

.NET 9

### Operating system

Windows 11

### IDE

Visual Studio 2026 18.1

Contributor guide

Open the contributing guide

Assessment

This issue has not been assessed yet.

Get new issues in your inbox

A short digest of beginner-friendly GitHub issues.