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
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>
whereTKey
andTElement
could be any arbitrary type. Furthermore,IGrouping
implementsIEnumerable<TElement>
, which means you can compose over it using any LINQ operator after the grouping. Since no database structure can represent anIGrouping
, 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 SQLGROUP BY
in relational databases. The SQLGROUP 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.