efcore
efcore copied to clipboard
dotnet
Missing `_ord` column when translating an inline collection into a queryable SQL VALUES expression
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
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<Blog> 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<Blog> 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; }
}
<Project Sdk="Microsoft.NET.Sdk">
<PropertyGroup>
<OutputType>Exe</OutputType>
<TargetFramework>net8.0</TargetFramework>
<ImplicitUsings>enable</ImplicitUsings>
<Nullable>enable</Nullable>
</PropertyGroup>
<ItemGroup>
<PackageReference Include="Microsoft.EntityFrameworkCore.Sqlite" Version="9.0.0-rc.2.24474.1" />
</ItemGroup>
</Project>
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:
- 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
@cincuranet Is this a confirmed bug? Do you know if it is a regression in EF9?
I don't think it is a regression. I originally noticed this with 8.x, but decided to file the bug against the most recent version (to ensure I wouldn't file something that was already fixed).
Note for team: confirmed this isn't a functional regression, but we used to throw a better exception message.
Unhandled exception. System.InvalidOperationException: The LINQ expression 'EnumerableQuery<int> { 1, 2, 3 }
.Select(x => new {
x = x,
blog = DbSet<Blog>()
.FirstOrDefault(b => b.Id == x)
})' could not be translated. Either rewrite the query in a form that can be translated, or switch to client evaluation explicitly by inserting a call to 'AsEnumerable', 'AsAsyncEnumerable', 'ToList', or 'ToListAsync'. See https://go.microsoft.com/fwlink/?linkid=2101038 for more information.
at Microsoft.EntityFrameworkCore.Query.Internal.NavigationExpandingExpressionVisitor.VisitMethodCall(MethodCallExpression methodCallExpression)
at Microsoft.EntityFrameworkCore.Query.Internal.NavigationExpandingExpressionVisitor.VisitMethodCall(MethodCallExpression methodCallExpression)
at Microsoft.EntityFrameworkCore.Query.Internal.NavigationExpandingExpressionVisitor.Expand(Expression query)
at Microsoft.EntityFrameworkCore.Query.QueryTranslationPreprocessor.Process(Expression query)
at Microsoft.EntityFrameworkCore.Query.QueryCompilationContext.CreateQueryExecutor[TResult](Expression query)
at Microsoft.EntityFrameworkCore.Storage.Database.CompileQuery[TResult](Expression query, Boolean async)
at Microsoft.EntityFrameworkCore.Query.Internal.QueryCompiler.CompileQueryCore[TResult](IDatabase database, Expression query, IModel model, Boolean async)
at Microsoft.EntityFrameworkCore.Query.Internal.QueryCompiler.<>c__DisplayClass12_0`1.<ExecuteAsync>b__0()
at Microsoft.EntityFrameworkCore.Query.Internal.CompiledQueryCache.GetOrAddQuery[TResult](Object cacheKey, Func`1 compiler)
at Microsoft.EntityFrameworkCore.Query.Internal.QueryCompiler.ExecuteAsync[TResult](Expression query, CancellationToken cancellationToken)
at Microsoft.EntityFrameworkCore.Query.Internal.EntityQueryProvider.ExecuteAsync[TResult](Expression expression, CancellationToken cancellationToken)
at Microsoft.EntityFrameworkCore.Query.Internal.EntityQueryable`1.GetAsyncEnumerator(CancellationToken cancellationToken)
at System.Runtime.CompilerServices.ConfiguredCancelableAsyncEnumerable`1.GetAsyncEnumerator()
at Microsoft.EntityFrameworkCore.EntityFrameworkQueryableExtensions.ToListAsync[TSource](IQueryable`1 source, CancellationToken cancellationToken)
at Program.<Main>$(String[] args) in D:\code\DoomSlug\DoomSlug\Program.cs:line 34
at Program.<Main>$(String[] args) in D:\code\DoomSlug\DoomSlug\Program.cs:line 34
at Program.<Main>$(String[] args) in D:\code\DoomSlug\DoomSlug\Program.cs:line 34
at Program.<Main>(String[] args)
I'm not sure if I'm encountering this exactly or if this is a slightly different variation on the same issue.
I have this simplified query that is generating invalid SQL due to this missing _ord column:
Context.Accounts
.Select(e => new SearchResult()
{
AccountOwner = e.CustomerName1 ?? "",
Address = String.Join(", ",
new string?[]
{
e.AddressLine1, e.AddressLine2, e.AddressLine3, e.AddressLine4,
e.AddressCity, e.AddressStateCode, e.AddressZipCode
}
.Where(s => !string.IsNullOrEmpty(s))
),
}).ToListAsync();
is resulting in this snippet of invalid SQL due to the non-existent _ord column
OUTER APPLY (
SELECT [v].[Value], [v].[_ord]
FROM (VALUES ([c].[ADDR_LINE_1]), ([c].[ADDR_LINE_2]), ([c].[ADDR_LINE_3]), ([c].[ADDR_LINE_4]), ([c].[ADDR_CITY]), ([c].[ADDR_STATE_CD]), ([c].[ADDR_ZIP_CODE])) AS [v]([Value])
WHERE [v].[Value] IS NOT NULL AND [v].[Value] NOT LIKE N''
) AS [v0]
