Invalid translation of a Query with GroupBy

[benjaminjnr]

File a bug

I kept getting stumped by this query that just won't run even though I validated that everything was as it should be. The entities are properly mapped to their tables and they hold up under other circumstances.

Please see the actual code from the project and the translated output. If I need to provide the entities involved, please give me an email to send to.

For now, I will work around this by issuing a raw query to the DB and projecting into the DTO.

Note: namespace and class for ReconStockHoldingAtDate in the StackTrace redacted.

Include your code

		var gatherList =
			dc.Set<EQAccountHistory>()
				.Where(h => h.EQAccount.ExchangeId == exchangeId && h.EQAccount.AcctClass != EQAccountClassEnum.Nominee && !string.IsNullOrEmpty(h.EQAccount.ExchangeAcctNo))
				.Where(h => h.JournalDate <= atDate)
				.GroupBy(h => new NTuple<long, long> { Element1 = h.EQAccount.Id, Element2 = h.EQInstrument.Id })
				.Select(hg =>
					new VALHoldingForRecon
					{
						AccountId = hg.Key.Element1,
						ExchangeAcctNo = hg.Max(h => h.EQAccount.ExchangeAcctNo),
						InstrumentId = hg.Key.Element2,
						InstrumentCode = hg.Max(h => h.EQInstrument.InstrumentCode),
						QuantityHeld = hg.Sum(h => h.Quantity)
					}
				);

		return await gatherList.ToListAsync();

Include stack traces

Include the full exception message and stack trace for any exception you encounter.

Use triple-tick fences for stack traces. For example:

Microsoft.Data.SqlClient.SqlException (0x80131904): Invalid column name 'InstrumentCode'.
   at Microsoft.Data.SqlClient.SqlCommand.<>c.<ExecuteDbDataReaderAsync>b__188_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.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.Storage.ExecutionStrategy.<>c__DisplayClass33_0`2.<<ExecuteAsync>b__0>d.MoveNext()
--- End of stack trace from previous location ---
   at Microsoft.EntityFrameworkCore.Storage.ExecutionStrategy.ExecuteImplementationAsync[TState,TResult](Func`4 operation, Func`4 verifySucceeded, TState state, CancellationToken cancellationToken)
   at Microsoft.EntityFrameworkCore.Storage.ExecutionStrategy.ExecuteImplementationAsync[TState,TResult](Func`4 operation, Func`4 verifySucceeded, TState state, CancellationToken cancellationToken)
   at Microsoft.EntityFrameworkCore.Storage.ExecutionStrategy.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 ReconStockHoldingAtDate(GriffinDomainContext dc, Int64 exchangeId, DateTime atDate)

Include verbose output

Translated output gotten by inspecting gatherList.DebugView.Query:

DECLARE @__exchangeId_0 bigint = CAST(3220 AS bigint);
DECLARE @__atDate_1 datetime2 = '2022-01-31T00:00:00.0000000';


SELECT [e0].[Id] AS [AccountId], MAX([e2].[ExchangeAcctNo]) AS [ExchangeAcctNo], [e1].[Id] AS [InstrumentId], MAX([e2].[InstrumentCode]) AS [InstrumentCode], COALESCE(SUM([e].[Quantity]), 0.0) AS [QuantityHeld]
FROM [EQAccountHistory] AS [e]
LEFT JOIN [EQAccount] AS [e0] ON [e].[AccountId] = [e0].[Id]
INNER JOIN [EQInstrument] AS [e1] ON [e].[InstrumentId] = [e1].[Id]
LEFT JOIN [EQAccount] AS [e2] ON [e].[AccountId] = [e2].[Id]
WHERE ((([e0].[ExchangeId] = @__exchangeId_0) AND (([e0].[AcctClass] <> 2) OR [e0].[AcctClass] IS NULL)) AND ([e0].[ExchangeAcctNo] IS NOT NULL AND NOT ([e0].[ExchangeAcctNo] LIKE N''))) AND ([e].[JournalDate] <= @__atDate_1)
GROUP BY [e0].[Id], [e1].[Id]

Include provider and version information

EF Core version:
Database provider: (e.g. Microsoft.EntityFrameworkCore.SqlServer)
Target framework: (e.g. .NET 6.0)
Operating system:
IDE: (e.g. Visual Studio 2022 17.0)

[smitpatel]

Duplicate of #27163