Regression: GroupBy with operation on a nullable date fail when there is a where with a condition on a sub collection

[yepeekai]

A query that worked on 6.0.1 doesn't work anymore on 6.0.3

Here is the query:

myDbSet.Where(e => e.MyCollectionOfThings.Any(a => a.SomeId == 2))
            .GroupBy(e => EF.Functions.DateDiffYear(e.Membre.DateNaissance, DateTime.Today)
                    - (e.Membre.DateNaissance.HasValue && e.Membre.DateNaissance.Value.AddYears(EF.Functions.DateDiffYear(e.Membre.DateNaissance, DateTime.Today).Value) > DateTime.Today ? 1 : 0))
            .Select(grp => new { ageYear = grp.Key, Count = grp.Count() }).ToListAsync()

Here is the error thrown:
SqlException: Column 'Membre.DateNaissance' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.

If I remove the "Where" from the query, it works. The where has to be an expression that uses a collection.

I was able to simplify the GroupBy to this with the problem still there:
GroupBy(e => EF.Functions.DateDiffYear(e.Membre.DateNaissance, DateTime.Today))

In the query: DateNaissance has the type: DateTime?

Stacktrace:

Microsoft.Data.SqlClient.SqlCommand+<>c.<ExecuteDbDataReaderAsync>b__188_0(Task<SqlDataReader> result)
System.Threading.Tasks.ContinuationResultTaskFromResultTask<TAntecedentResult, TResult>.InnerInvoke()
System.Threading.Tasks.Task+<>c.<.cctor>b__272_0(object obj)
System.Threading.ExecutionContext.RunInternal(ExecutionContext executionContext, ContextCallback callback, object state)
System.Threading.ExecutionContext.RunInternal(ExecutionContext executionContext, ContextCallback callback, object state)
System.Threading.Tasks.Task.ExecuteWithThreadLocal(ref Task currentTaskSlot, Thread threadPoolThread)
Microsoft.EntityFrameworkCore.Storage.RelationalCommand.ExecuteReaderAsync(RelationalCommandParameterObject parameterObject, CancellationToken cancellationToken)
Microsoft.EntityFrameworkCore.Storage.RelationalCommand.ExecuteReaderAsync(RelationalCommandParameterObject parameterObject, CancellationToken cancellationToken)
Microsoft.EntityFrameworkCore.Query.Internal.SplitQueryingEnumerable<T>+AsyncEnumerator.InitializeReaderAsync(AsyncEnumerator enumerator, CancellationToken cancellationToken)
Microsoft.EntityFrameworkCore.SqlServer.Storage.Internal.SqlServerExecutionStrategy.ExecuteAsync<TState, TResult>(TState state, Func<DbContext, TState, CancellationToken, Task<TResult>> operation, Func<DbContext, TState, CancellationToken, Task<ExecutionResult<TResult>>> verifySucceeded, CancellationToken cancellationToken)
Microsoft.EntityFrameworkCore.Query.Internal.SplitQueryingEnumerable<T>+AsyncEnumerator.MoveNextAsync()
Microsoft.EntityFrameworkCore.EntityFrameworkQueryableExtensions.ToListAsync<TSource>(IQueryable<TSource> source, CancellationToken cancellationToken)
Microsoft.EntityFrameworkCore.EntityFrameworkQueryableExtensions.ToListAsync<TSource>(IQueryable<TSource> source, CancellationToken cancellationToken)

then the trace for my code which calls toListAsync

EF Core version: 6.0.3
Database provider: Microsoft.EntityFrameworkCore.SqlServer
Target framework: .NET 6.0.3
Operating system: Windows 10
IDE: visual studio 2022 17.1.2

[smitpatel]

Duplicate of #27433