dotnet / dotnet/efcore

Missing `_ord` column when translating an inline collection into a queryable SQL VALUES expression

Open
#34,954 4 comments 0 reactions 0 assignees View on GitHub
area-primitive-collections area-query customer-reported
Dominant language
C#
Stars
14.8k
Forks
3.4k
PR merge metrics
PR metrics pending

Description

I tried to start a query from an inline collection using `DbContext.FromExpression`. It is translates to SQL mostly correctly, but then the query is ordered by an `_ord` column that wasn't added to the collection in SQL before, resulting in invalid SQL. I'm not sure if my use of `DbContext.FromExpression` is a valid use case, but even if it isn't, I felt it would likely be a bug that invalid SQL is executed.

### Repro

```C#
using Microsoft.Data.Sqlite;
using Microsoft.EntityFrameworkCore;
using System.Text.Json;

const string ConnectionString = @"Data Source=InMemorySample;Mode=Memory;Cache=Shared";

await using var keepOpenCon = new SqliteConnection(ConnectionString);
await keepOpenCon.OpenAsync();

await using var context = new ReproContext(ConnectionString);
await context.Database.EnsureCreatedAsync();

List blogs = [new Blog(), new Blog(), new Blog()];
context.Blogs.AddRange(blogs);
await context.SaveChangesAsync();

var ids = blogs.Select(b => b.Id).ToList();
var q = from x in context.FromExpression(() => ids.AsQueryable())
let blog = context.Blogs.FirstOrDefault(b => b.Id == x)
select new { blog.Id };
var result = await q.ToListAsync();
Console.WriteLine(JsonSerializer.Serialize(result));

public class ReproContext(string ConnectionString) : DbContext
{
public DbSet Blogs { get; set; }

protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
=> optionsBuilder
.UseSqlite(ConnectionString)
.LogTo(Console.WriteLine, Microsoft.Extensions.Logging.LogLevel.Information, Microsoft.EntityFrameworkCore.Diagnostics.DbContextLoggerOptions.None);
}

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

```csproj


Exe
net8.0
enable
enable



```

I originally noticed this with Npgsql, but thought it could be unrelated to the specific provider and thus created a SQLite repro - just saying it doesn't seem to be related to SQLite specifically.

### Observations

Providing an explicit order makes it work:

```diff
- var result = await q.ToListAsync();
+ var result = await q.OrderBy(x => x.Id).ToListAsync();
```

### Console output / stack trace

```
Executed DbCommand (7ms) [Parameters=[], CommandType='Text', CommandTimeout='30']
SELECT COUNT(*) FROM "sqlite_master" WHERE "type" = 'table' AND "rootpage" IS NOT NULL;
Executed DbCommand (1ms) [Parameters=[], CommandType='Text', CommandTimeout='30']
CREATE TABLE "Blogs" (
"Id" INTEGER NOT NULL CONSTRAINT "PK_Blogs" PRIMARY KEY AUTOINCREMENT
);
Executed DbCommand (1ms) [Parameters=[], CommandType='Text', CommandTimeout='30']
INSERT INTO "Blogs"
DEFAULT VALUES
RETURNING "Id";
Executed DbCommand (0ms) [Parameters=[], CommandType='Text', CommandTimeout='30']
INSERT INTO "Blogs"
DEFAULT VALUES
RETURNING "Id";
Executed DbCommand (0ms) [Parameters=[], CommandType='Text', CommandTimeout='30']
INSERT INTO "Blogs"
DEFAULT VALUES
RETURNING "Id";
Failed executing DbCommand (5ms) [Parameters=[], CommandType='Text', CommandTimeout='30']
SELECT (
SELECT "b"."Id"
FROM "Blogs" AS "b"
WHERE "b"."Id" = "v"."Value"
LIMIT 1) AS "Id"
FROM (SELECT CAST(1 AS INTEGER) AS "Value" UNION ALL VALUES (2), (3)) AS "v"
ORDER BY "v"."_ord"
An exception occurred while iterating over the results of a query for context type 'ReproContext'.
Microsoft.Data.Sqlite.SqliteException (0x80004005): SQLite Error 1: 'no such column: v._ord'.
at Microsoft.Data.Sqlite.SqliteException.ThrowExceptionForRC(Int32 rc, sqlite3 db)
at Microsoft.Data.Sqlite.SqliteCommand.PrepareAndEnumerateStatements()+MoveNext()
at Microsoft.Data.Sqlite.SqliteCommand.GetStatements()+MoveNext()
at Microsoft.Data.Sqlite.SqliteDataReader.NextResult()
at Microsoft.Data.Sqlite.SqliteCommand.ExecuteReader(CommandBehavior behavior)
at Microsoft.Data.Sqlite.SqliteCommand.ExecuteReaderAsync(CommandBehavior behavior, CancellationToken cancellationToken)
at Microsoft.Data.Sqlite.SqliteCommand.ExecuteDbDataReaderAsync(CommandBehavior behavior, 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.SingleQueryingEnumerable`1.AsyncEnumerator.InitializeReaderAsync(AsyncEnumerator enumerator, CancellationToken cancellationToken)
at Microsoft.EntityFrameworkCore.Query.Internal.SingleQueryingEnumerable`1.AsyncEnumerator.MoveNextAsync()
```

### Include provider and version information

EF Core version: `9.0.0-rc.2.24474.1`
Database provider: `Microsoft.EntityFrameworkCore.Sqlite`
Target framework: .NET 8.0
Operating system: Windows 11 23H2
IDE: Visual Studio 2022 17.10.3

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.