Query: Include annotation is not ignored for group by aggregate causing client eval

[cdarrigo]

Running a simple group by expression to compute a sum of a value is failing to translate and as a result, running the group by locally.

Query:

  var summedCharges = await dataContext.Charges
                .Include(i => i.BillingItem)
                .Where(i => !i.Deleted &&
                            i.UserId == userId &&
                            i.SchoolId == schoolId &&
                            i.BillingItem.SchoolYearId == schoolYearId &&
                            i.IncludeInTrp &&
                            i.BillingItem.TypeCode != trpBillingItemTypeCode

                )
                .GroupBy(i => i.UserId)
                .Select(g => g.Sum(i => i.Amount))
                .OrderBy(i=>i.Value)
                .FirstOrDefaultAsync();

Warning Messages:

[12:53:20 WRN] The LINQ expression 'GroupBy([i].UserId, _Include(queryContext, [i], new [] {[i.BillingItem]}, (queryContext, entity, included) => { ... }))' could not be translated and will be evaluated locally.
[12:53:20 WRN] The LINQ expression 'Sum()' could not be translated and will be evaluated locally.
[12:53:20 WRN] The LINQ expression 'orderby Convert({from ChargeEntity i in [g] select [i].Amount => Sum()}, Decimal) asc' could not be translated and will be evaluated locally.
[12:53:20 WRN] The LINQ expression 'Sum()' could not be translated and will be evaluated locally.
[12:53:20 WRN] The LINQ expression 'Sum()' could not be translated and will be evaluated locally.
[12:53:20 WRN] The LINQ expression 'Sum()' could not be translated and will be evaluated locally.
[12:53:20 WRN] The LINQ expression 'FirstOrDefault()' could not be translated and will be evaluated locally.

Further technical details

EF Core version: 2.1.0-rc1-final
Database Provider: Microsoft.EntityFrameworkCore.SqlServer
Operating system: Win 10
IDE: (e.g. Visual Studio 2017 15.4)

[smitpatel]

You are using Include which means you are asking for some entity hence SQL group by cannot be used. What is result you are trying to compute with the query? Rewriting the query differently could achieve server eval using SQL group by.

[cdarrigo]

so I don't need to Include the child objects unless I want them in the projection? I thought it would be required since I'm filtering on a child object's property (billingitem.schoolyearid in this example).

[smitpatel]

@cdarrigo - Include is only required when you are projecting out an entity and you want navigation on that entity object to be populated. If you are not projecting out entity then include will be ignored. All navigation access inside the query doesn't require any Include. We add appropriate joins automatically to compute results.

@maumar @anpete - Ideally this Include should be ignored because we are not projecting out entity - GroupBy Aggregate operation case. I believe our logic around GroupBy + Include is weak that we are adding Include here which causes client eval instead of server eval.

[anpete]

@smitpatel Sounds like it.

[cdarrigo]

@smitpatel That was it. I removed the unnecessary include and its grouping on the server. Thanks!

[ajcvickers]

@smitpatel Is this a regression from 2.0?

[smitpatel]

@ajcvickers - Not a regression. We fail to detect that Include is unnecessary and put that in our QueryModel which does streaming group by instead of SQL group by translation.

[maumar]

fixed in 66d135f