The LINQ expression could not be translated. Either rewrite the query in a form that can be translated, or switch to client evaluation EF Core 3.1

Question

I'm struggling with this for four days already and no progress at all. Having a query, which worked just fine before updating to EF Core 3.1:

var equipments = await this.DbContext.ServContrObjStructEquipment
            .AsNoTracking()
            .Where(e => e.ServContrObjStruct.ServContrObjStructParent.ServContrObjStructParent.Objectuuid == sectionGuid)
            .Where(e => e.ServContrObjStructPlanEquipment.Select(x => x.PkServContrObjStructPlanEquipment).Contains(
                e.ServContrObjStructPlanEquipment.OrderByDescending(x => x.ServContrObjStructPlanVers.ActiveUntil ?? DateTime.MaxValue).ThenByDescending(x => x.ServContrObjStructPlanVers.ActiveFrom).ThenByDescending(x => x.ActiveFrom).Select(x => x.PkServContrObjStructPlanEquipment).FirstOrDefault()
                ))

Now it throws me an Exception that says:

The LINQ expression 'DbSet .Where(s3 => s3.RecStatus == 1) .Where(s3 => EF.Property>((EntityShaperExpression: EntityType: ServContrObjStructEquipment ValueBufferExpression: (ProjectionBindingExpression: Outer.Outer.Outer) IsNullable: False ), "PkServContrObjStructEquipment") != null && EF.Property>((EntityShaperExpression: EntityType: ServContrObjStructEquipment ValueBufferExpression: (ProjectionBindingExpression: Outer.Outer.Outer) IsNullable: False ), "PkServContrObjStructEquipment") == EF.Property>(s3, "FkServContrObjStructEquipment")) .Select(s3 => s3.PkServContrObjStructPlanEquipment) .Contains((MaterializeCollectionNavigation( navigation: Navigation: ServContrObjStructEquipment.ServContrObjStructPlanEquipment, subquery: DbSet .Where(s4 => s4.RecStatus == 1) .Where(i => EF.Property>((EntityShaperExpression: EntityType: ServContrObjStructEquipment ValueBufferExpression: (ProjectionBindingExpression: Outer.Outer.Outer) IsNullable: False ), "PkServContrObjStructEquipment") != null && EF.Property>((EntityShaperExpression: EntityType: ServContrObjStructEquipment ValueBufferExpression: (ProjectionBindingExpression: Outer.Outer.Outer) IsNullable: False ), "PkServContrObjStructEquipment") == EF.Property>(i, "FkServContrObjStructEquipment"))) .AsQueryable() .OrderByDescending(x => x.ServContrObjStructPlanVers.ActiveUntil ?? 12/31/9999 11:59:59 PM) .ThenByDescending(x => x.ServContrObjStructPlanVers.ActiveFrom) .ThenByDescending(x => x.ActiveFrom) .Select(x => x.PkServContrObjStructPlanEquipment) .FirstOrDefault())' 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 am aware about EF Core 3.0-3.1 has breaking changes and that client evaluation is now performed on the top level Select(). Although I'd rather avoid doing that, I've tried calling all the ToList(), AsEnumerable() etc. to make it work, but it also doesn't help: the query is complex and multilevel, so calling ToList() at any point either breaks it or does not get related records (because of the Lazy loading I guess) which are then needed for further execution of the query.

I've also tried splitting the query into separate queries to see what's going on there like this:

var intermEquipments = await this.DbContext.ServContrObjStructEquipment
                .AsNoTracking()
                .Include(e => e.ServContrObjStructPlanEquipment)
             .Where(e=>e.ServContrObjStruct.ServContrObjStructParent.ServContrObjStructParent.Objectuuid == sectionGuid)
                .ToListAsync();

            var intermEquipments1 = await this.DbContext.ServContrObjStructEquipment
                .AsNoTracking()
                .Include(e => e.ServContrObjStructPlanEquipment)
                .Where(e => e.ServContrObjStruct.ServContrObjStructParent.ServContrObjStructParent.Objectuuid == sectionGuid)
                .Select(e => e.ServContrObjStructPlanEquipment.Select(x => x.PkServContrObjStructPlanEquipment))
                .ToListAsync();

            var intermEquipments2 = this.DbContext.ServContrObjStructEquipment
                .AsNoTracking()
                .Include(e => e.ServContrObjStructPlanEquipment)
                .Where(e => e.ServContrObjStruct.ServContrObjStructParent.ServContrObjStructParent.Objectuuid == sectionGuid)
                .Select(e => e.ServContrObjStructPlanEquipment
                    .OrderByDescending(x => x.ServContrObjStructPlanVers.ActiveUntil ?? DateTime.MaxValue)
                                .ThenByDescending(x => x.ServContrObjStructPlanVers.ActiveFrom)
                                    .ThenByDescending(x => x.ActiveFrom)
                                        .Select(x => x.PkServContrObjStructPlanEquipment).ToList());

but it either throws me an Exception saying that

Lambda expression used inside Include is not valid

or NullReference (because some related properties are not loaded, as I mentioned above) or not showing the records at all.

I am familiar with EntityFramework, though I'm not an expert. Is there a way to rewrite this query so it can be translated or just do anything to make it work? Thank you!

Answer 1

This looks to be a bug or some kind of breaking change in EF Core (surprise surprise). At first I suspected the use of DateTime.MaxValue as EF Core had issues with that in the past, but it's not that. It is taking exception to doing the OrderBy sub-select inside the Contains for some reason. One breaking change of EF Core 3 from 2.2.6 was that Core 2 would automatically switch to client-side expressions where Core will throw an exception however I was able to confirm with a Profiler that a query like this was successfully translated to SQL with Core 2.2.6 but in 3.1 it does not translate resulting in the error. At this point you could raise a bug report with the EF Core team.

I was able to reproduce this with a simpler query:

var results = context.Parents.Where(x => x.Children.Select(c => c.ChildId)
   .Contains(x.Children.OrderByDescending(c => c.BirthDate).Select(c => c.ChildId).FirstOrDefault()))
   .ToList();

Run in 2.2.6 it compiles successfully to SQL. 3.1 chucks an exception.

It would probably be beneficial to understand exactly what this query is meant to return because I've read it 3 times now and it still doesn't make sense. :)

You are selecting ServerEquipment where their Struct.Paremt.Parent.ObjectID = the Section ID AND their plan Equipment's contain, from what I can make out, the earliest Equipment record in that set. That Contains check is what makes no sense because it is just looking for the existence of a select row within that set without any external criteria? I mean boiled down it looks like .Where(x => x.Children.Select(c => c.ChildId).Contains(x.Children.OrderByDescending(c2 => c2.BirthDate).Select(c2 => c2.ChildId).FirstOrDefault()) which is pretty much a fancy way of wasting the database's time. I.e. Where Children Contains an oldest child. (Well of course it does.) So I can only assume I've missed something there, but I'd really hate to try and pick up an entity structure and queries like that 6 months from now.. :)

A contains check from it's own child collection without any external criteria doesn't make sense. Normally these types of queries would look at a child collection to see if they contained some matching criteria which would be satisfied by an Any() type check within the overlying Where clause. It might help to look again at exactly what you want to filter to see if this query can be simplified and avoid this breaking change.