What is the server evaluation issue causing this EF Core 3.1 query to fail?

Question

I've already learned the hard way that in EF Core 3.0, client evaluation can only happen on the top level projection (i.e. last call to Select). I thought I understood the issue but the failure of one query in particular under 3.1 tells me I don't.

This is the query

using (var ctx = new GsContext())
{
    var recs = ctx.Calibrations
                  .Where(ca => ca.DeviceId == deviceId)
                  .AsNoTracking()
                  .Include(c => c.Cartridge)
                  .OrderByDescending(i => i.CalibratedOn)
                  .GroupBy(r => r.CartridgeId)
                  .Select(q => q.First())
                  .ToList();

I don't see myself doing any complicated projections or anything that should require client side evaluation before the Select, so the exception message confuses me. This was the text of the error message:

The LINQ expression '(GroupByShaperExpression: KeySelector: (c.CartridgeId), ElementSelector:(EntityShaperExpression: EntityType: Calibration ValueBufferExpression: (ProjectionBindingExpression: EmptyProjectionMember) IsNullable: False ) ) .First()' could not be translated. Either rewrite the query in a form that can be translated, or switch to client evaluation explicitly by inserting a call to either AsEnumerable(), AsAsyncEnumerable(), ToList(), or ToListAsync(). See https://go.microsoft.com/fwlink/?linkid=2101038 for more information.

I've already been hit with exceptions like this before when my queries were trying to do some complicated string processing early in the query (like I said, I learned about this issue the hard way) but that's not the case here, at least not that I can see.

I found this related question but in this case the guy was doing a projection within the GroupBy clause. I am not doing that.

If it helps, here it the Calibration class

public class Calibration
{
    [Key]
    [Required]
    public int      Id                 { get; set; }

    [Required]
    public int      DeviceId           { get; set; }

    [Required]
    public int      CartridgeId        { get; set; }

    [ForeignKey(nameof(CartridgeId))]
    public Cartridge Cartridge          { get; set; }

    public DateTime CalibratedOn     { get; set; }
}

I am sure I can work around this. I'm asking to understand client/server evaluation better. Can someone explain to me what I'm missing? What about my query requires client evaluation

Answer 1

I found this related question but in this case the guy was doing a projection within the GroupBy clause. I am not doing that.

Well, that's the problem, in my answer of the linked post I wrote:

Unfortunately currently EF Core 3.0 / 3.1 only supports server translation of GroupBy with projection of key / aggregates (similar to SQL).

In other words, you must have projection containing only key and/or aggregates after GroupBy, otherwise it won't translate.

Here is the related GitHub issue. And some explanation from the official docs under Complex Query Operators - GroupBy:

LINQ GroupBy operators create a result of type IGrouping<TKey, TElement> where TKey and TElement could be any arbitrary type. Furthermore, IGrouping implements IEnumerable<TElement>, which means you can compose over it using any LINQ operator after the grouping. Since no database structure can represent an IGrouping, GroupBy operators have no translation in most cases. When an aggregate operator is applied to each group, which returns a scalar, it can be translated to SQL GROUP BY in relational databases. The SQL GROUP BY is restrictive too. It requires you to group only by scalar values. The projection can only contain grouping key columns or any aggregate applied over a column.