If you use an casted OrderBy in a SubQuery the following exception is thrown:
Exception thrown: 'System.InvalidOperationException' in System.Core.dll
Rewriting child expression from type
is not allowed, because it would change the meaning of the operation.
If this is intentional, override 'VisitUnary' and change it to allow this rewrite.
This Exception is thrown by SqlPreparationExpressionVisitor.Visit.
The problem seems to be that in SqlPreparationSubStatementTableFactory.GetNewSelectExpressionWithOrderings TupleExpressionBuilder.AggregateExpressionsIntoTuple is called which changes the type of the SubQuery to KeyValuePair if the there is an cast on the OrderBy. Without the cast the query works just fine.
It is reproduceable with the following query: