Invalid SQL generated for grouped query followed by OrderBy in SQLite and SQL Server

[GeorgDangl]

We've had another integration test failing that worked before in EF Core 7. In SQLite, we're getting an error for a missing column SQLite Error 1: 'no such column: p2.value', whereas SQL Server reports The multi-part identifier "p2.value" could not be bound.. I've put the full exception messages for both databases further down in code blocks.

For SQL Server, we can reproduce it both on Windows via LocalDB and on Linux via SQL Server in Docker (both DB and the EF Core process running on Linux), SQLite was only tested locally in Windows.

Include your code

Below, I've attached the full code required for reproduction, both in SQLite and SQL Server. I've been running with .NET 8 and EF Core 8.0.0. I've verified that the exact same code runs in .NET 7 with EF Core 7.0.0.

<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="8.0.0" />
    <PackageReference Include="Microsoft.EntityFrameworkCore.SqlServer" Version="8.0.0" />
  </ItemGroup>
</Project>

using Microsoft.EntityFrameworkCore;
using Microsoft.Extensions.DependencyInjection;

namespace EfCoreTest
{
    internal class Program
    {
        private static async Task Main(string[] args)
        {
            Console.WriteLine("Checking SQLite");
            await SetupAndReproErrorAsync(options => options.UseSqlite("Data Source=data.db"));
            Console.WriteLine("Checking SQL Server");
            await SetupAndReproErrorAsync(options => options.UseSqlServer("Server=(localdb)\\MSSQLLocalDB;Database=EfCoreRepro;Trusted_Connection=True;MultipleActiveResultSets=true"));
        }

        private static async Task SetupAndReproErrorAsync(Action<DbContextOptionsBuilder> setup)
        {
            var serviceCollection = new ServiceCollection();
            serviceCollection.AddDbContext<ReproDbContext>(setup);
            var services = serviceCollection.BuildServiceProvider();

            // Create a database
            using (var scope = services.CreateScope())
            {
                var dbContext = scope.ServiceProvider.GetRequiredService<ReproDbContext>();
                await dbContext.Database.EnsureDeletedAsync();
                await dbContext.Database.EnsureCreatedAsync();
            }

            // Run the repro that throws the exception
            using (var scope = services.CreateScope())
            {
                var dbContext = scope.ServiceProvider.GetRequiredService<ReproDbContext>();
                try
                {
                    await ReproDatabaseError(dbContext);
                }
                catch (Exception e)
                {
                    Console.WriteLine(e);
                }
            }
        }

        private static async Task ReproDatabaseError(ReproDbContext context)
        {
            var positionIdsWithoutTotalPrice = new[] { Guid.NewGuid() };
            var projectProductsQuery = from rawData in (from calculationRow in context.CalculationRows
                                                        join calculation in context.Calculations
                                                             on calculationRow.CalculationId equals calculation.Id
                                                        where !positionIdsWithoutTotalPrice.Contains(calculation.Id)
                                                        select new
                                                        {
                                                            calculationRow.MasterProductId,
                                                            calculation.Id,
                                                            calculation.Name
                                                        })
                                       group rawData by rawData.MasterProductId
                                into groups
                                       select new
                                       {
                                           groups.FirstOrDefault().MasterProductId,
                                           groups.FirstOrDefault().Name,
                                       };

            var canMaterializeLikeThis = await projectProductsQuery.ToListAsync();
            await projectProductsQuery
                    .OrderBy(p => p.Name)
                    .ToListAsync();
        }
    }

    public class ReproDbContext : DbContext
    {
        public ReproDbContext(DbContextOptions<ReproDbContext> options) : base(options)
        {
        }

        public DbSet<CalculationRow> CalculationRows { get; set; }
        public DbSet<Calculation> Calculations { get; set; }
    }

    public class Calculation
    {
        public Guid Id { get; set; }

        public string Name { get; set; }

        public List<CalculationRow> Rows { get; set; }
    }

    public class CalculationRow
    {
        public Guid Id { get; set; }

        public Guid CalculationId { get; set; }
        public Calculation Calculation { get; set; }

        public Guid MasterProductId { get; set; }
    }
}

Include stack traces

Running the code above, I'm getting exceptions both for SQLite and SQL Server. I did not try any other databases.

SQLite

Checking SQLite
Microsoft.Data.Sqlite.SqliteException (0x80004005): SQLite Error 1: 'no such column: p2.value'.
   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()
   at Microsoft.EntityFrameworkCore.EntityFrameworkQueryableExtensions.ToListAsync[TSource](IQueryable`1 source, CancellationToken cancellationToken)
   at Microsoft.EntityFrameworkCore.EntityFrameworkQueryableExtensions.ToListAsync[TSource](IQueryable`1 source, CancellationToken cancellationToken)
   at EfCoreTest.Program.ReproDatabaseError(ReproDbContext context) in C:\Users\Georg\Downloads\EfCoreTest\EfCoreTest\Program.cs:line 67
   at EfCoreTest.Program.SetupAndReproErrorAsync(Action`1 setup) in C:\Users\Georg\Downloads\EfCoreTest\EfCoreTest\Program.cs:line 36

SQL Server

Checking SQL Server
Microsoft.Data.SqlClient.SqlException (0x80131904): The multi-part identifier "p2.value" could not be bound.
   at Microsoft.Data.SqlClient.SqlCommand.<>c.<ExecuteDbDataReaderAsync>b__209_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 EfCoreTest.Program.ReproDatabaseError(ReproDbContext context) in C:\Users\Georg\Downloads\EfCoreTest\EfCoreTest\Program.cs:line 67
   at EfCoreTest.Program.SetupAndReproErrorAsync(Action`1 setup) in C:\Users\Georg\Downloads\EfCoreTest\EfCoreTest\Program.cs:line 36
ClientConnectionId:dc20f370-a18d-4005-968c-094b1c8406fd
Error Number:4104,State:1,Class:16

Include provider and version information

EF Core version:
Database provider: Microsoft.EntityFrameworkCore.SqlServer and Microsoft.EntityFrameworkCore.Sqlite
Target framework: .NET 8
Operating system: Windows 10
IDE: Visual Studio 2022 17.8.0

[roji]

Probable duplicate of #32234.