Query regression in preview 9: Combination of GroupBy, aggregate and let throws

[divega]

This was reported in the Preview 9 blog post:

This LINQ statement (and other similar statements) was being translated properly till Preview 8:

var queryYearly =
    from h in context.Histories.AsNoTracking() group h by h.HistoryDate.Year into yearGroup
    orderby yearGroup.Key descending
    let Total = yearGroup.Sum(m => m.Fee)
    where Total > 0
    select new
        {
            Year = yearGroup.Key,
            Total
        };

Updated to preview 9 this morning and I am getting the following error:

System.InvalidOperationException HResult=0x80131509 Message=Processing of the LINQ expression 'GroupBy(KeySelector: DATEPART(year, h.HistoryDate), ElementSelector:EntityShaperExpression: EntityType: Histories ValueBufferExpression: ProjectionBindingExpression: EmptyProjectionMember IsNullable: False)' by 'RelationalProjectionBindingExpressionVisitor' failed.....

According to @smitpatel:

Let throws us off. If you remove let then it works:

      SELECT DATEPART(year, [b].[HistoryDate]) AS [Year], SUM([b].[Fee]) AS [Total]
      FROM [Blogs] AS [b]
      GROUP BY DATEPART(year, [b].[HistoryDate])
      HAVING SUM([b].[Fee]) > 0
      ORDER BY DATEPART(year, [b].[HistoryDate]) DESC

With let the expression we get is:

value(Microsoft.EntityFrameworkCore.Query.Internal.EntityQueryable`1[EFSampleApp.Blog]).AsNoTracking().GroupBy(h => h.HistoryDate.Year).OrderByDescending(yearGroup => yearGroup.Key).Select(yearGroup => new <>f__AnonymousType0`2(yearGroup = yearGroup, Total = yearGroup.Sum(m => m.Fee))).Where(<>h__TransparentIdentifier0 => (<>h__TransparentIdentifier0.Total > 0)).Select(<>h__TransparentIdentifier0 => new <>f__AnonymousType1`2(Year = <>h__TransparentIdentifier0.yearGroup.Key, Total = <>h__TransparentIdentifier0.Total))

Since Select contains reference to grouping which cannot be put in Projection in SelectExpression we throw.

[divega]

@smitpatel I couldn't see if you created a bug for this, so went ahead and created it with the information you have me. Feel free to edit or close as duplicate as appropriate.

[jamesport079]

Thanks for reporting this Diego.

I replaced the 'let' clause as a workaround and it's giving the same SQL as with 'let' clause on SQL Profiler. So for the time being i'm good. But it would be nice if it's fixed eventually as the let clause makes LINQ queries cleaner in some cases.

[divega]

Thanks @jamesport079 for validating the workaround.

Triage decision: we are moving this to the backlog because there is a workaround and fixing the issue would be relatively expensive. We will be monitoring feedback on this and revisit the priority as needed.