Getting exception "The multi-part identifier could not be bound." when using GroupBy ,Contains and OrderBy

[Ben555555]

File a bug

I'm using the following query which throws an exception:

dbContext.PaymentRuns
.Where(paymentRun => filterPaging.Statuses.Contains(paymentRun.Status))
.GroupBy(paymentRun => paymentRun.PaymentItemReceiverType == PaymentItemReceiverType.Institution ?
    paymentRun.InstitutionId :
    paymentRun.Child.HouseholdId)
.Select(group => new PaymentRunModel
{
    Id = group.First().Id,
    Receivers = group.First().Receivers,
    Address = group.First().PaymentItemReceiverType == PaymentItemReceiverType.Institution ?
        AddressEntityProjections.GetAddressStringProjection().Invoke(group.First().Institution.Address) :
        AddressEntityProjections.GetAddressStringProjection().Invoke(group.First().Child.Household.Address),
    Iban = group.First().PaymentItemReceiverType == PaymentItemReceiverType.Institution ?
        group.First().Institution.PaymentInformation.Iban! :
        group.First().Child.Household.PaymentInformation.Iban!,
    ChildFullName = null,
    ChildBirthDate = null,
    Amount = group.Sum(paymentRun => paymentRun.Amount),
    Status = group.First().Status,
    StatusName = TranslationEntityProjections.GetTranslation(dbContext).Invoke(languageCode, $"Enums.{nameof(PaymentRunStatus)}." + group.First().Status),
    PaymentRunGroupCreateDate = group.First().PaymentRunGroup != null ?
        group.First().PaymentRunGroup!.CreateDate :
        null,
    CreateDate = group.First().CreateDate,
    IsDeletable = false,
    Ids = group.Select(paymentRun => paymentRun.Id).ToList(),
    Statuses = group.Select(paymentRun => paymentRun.Status).ToList()
})
.OrderBy(paymentRun => paymentRun.Receivers)
.ToList();

It will build a wrong query with a table alias that doesn't exist.

Include stack traces

Microsoft.Data.SqlClient.SqlException (0x80131904): The multi-part identifier "f2.value" could not be bound.
The multi-part identifier "t3.Key" could not be bound.
The multi-part identifier "t3.Key" could not be bound.
The multi-part identifier "t3.Receivers" could not be bound.
The multi-part identifier "f2.value" could not be bound.
The multi-part identifier "t3.Key" could not be bound.
The multi-part identifier "t3.Key" could not be bound.
The multi-part identifier "t3.Receivers" could not be bound.
   at Microsoft.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection, Action`1 wrapCloseInAction)
   at Microsoft.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection, Action`1 wrapCloseInAction)
   at Microsoft.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj, Boolean callerHasConnectionLock, Boolean asyncClose)
   at Microsoft.Data.SqlClient.TdsParser.TryRun(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj, Boolean& dataReady)
   at Microsoft.Data.SqlClient.SqlDataReader.TryConsumeMetaData()
   at Microsoft.Data.SqlClient.SqlDataReader.get_MetaData()
   at Microsoft.Data.SqlClient.SqlCommand.FinishExecuteReader(SqlDataReader ds, RunBehavior runBehavior, String resetOptionsString, Boolean isInternal, Boolean forDescribeParameterEncryption, Boolean shouldCacheForAlwaysEncrypted)
   at Microsoft.Data.SqlClient.SqlCommand.RunExecuteReaderTds(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, Boolean isAsync, Int32 timeout, Task& task, Boolean asyncWrite, Boolean inRetry, SqlDataReader ds, Boolean describeParameterEncryptionRequest)
   at Microsoft.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, TaskCompletionSource`1 completion, Int32 timeout, Task& task, Boolean& usedCache, Boolean asyncWrite, Boolean inRetry, String method)
   at Microsoft.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method)
   at Microsoft.Data.SqlClient.SqlCommand.ExecuteReader(CommandBehavior behavior)
   at Microsoft.Data.SqlClient.SqlCommand.ExecuteDbDataReader(CommandBehavior behavior)
   at Microsoft.EntityFrameworkCore.Storage.RelationalCommand.ExecuteReader(RelationalCommandParameterObject parameterObject)
   at Microsoft.EntityFrameworkCore.Query.Internal.SingleQueryingEnumerable`1.Enumerator.InitializeReader(Enumerator enumerator)
   at Microsoft.EntityFrameworkCore.Query.Internal.SingleQueryingEnumerable`1.Enumerator.<>c.<MoveNext>b__21_0(DbContext _, Enumerator enumerator)
   at Microsoft.EntityFrameworkCore.SqlServer.Storage.Internal.SqlServerExecutionStrategy.Execute[TState,TResult](TState state, Func`3 operation, Func`3 verifySucceeded)
   at Microsoft.EntityFrameworkCore.Query.Internal.SingleQueryingEnumerable`1.Enumerator.MoveNext()
   at System.Collections.Generic.List`1..ctor(IEnumerable`1 collection)
   at System.Linq.Enumerable.ToList[TSource](IEnumerable`1 source)

The only workaround I found so far is instead of using .Where(x => x.Y.Contains()) to use a custom Where-Or query.

Include provider and version information

EF Core version:
Database provider: Microsoft.EntityFrameworkCore.SqlServer
Target framework: .NET 8.0
Operating system: Windows 11
IDE: Visual Studio 2022

[maumar]

@Ben555555 can you provide full standalone repro that shows the error? The query/model is complex enough that we can't effectively investigate the problem otherwise

[maumar]

@Ben555555 actually, before you put in the time, you can try running your code on EF Core 9.0 RC1 bits. We have made significant changes to how aliases are handled (#32784) - the bug might be fixed already.

[Ben555555]

@maumar You can find the repro here:
34801.zip

Also I found out that depending on how you use Where it works or not:

.Where(paymentRun => filterPaging.Statuses.Contains(paymentRun.Status)) // Doesn't work

.Where(paymentRun => new[] { PaymentRunStatus.New, PaymentRunStatus.Retry }.Contains(paymentRun.Status)) // Works

[Ben555555]

@Ben555555 actually, before you put in the time, you can try running your code on EF Core 9.0 RC1 bits. We have made significant changes to how aliases are handled (#32784) - the bug might be fixed already.

Yes it actually works with this version.

[maumar]

thanks for the quick follow up, closing as dupe of #32784