title: 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
body: <p>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:</p>
  
  <pre><code>var equipments = await this.DbContext.ServContrObjStructEquipment
              .AsNoTracking()
              .Where(e =&gt; e.ServContrObjStruct.ServContrObjStructParent.ServContrObjStructParent.Objectuuid == sectionGuid)
              .Where(e =&gt; e.ServContrObjStructPlanEquipment.Select(x =&gt; x.PkServContrObjStructPlanEquipment).Contains(
                  e.ServContrObjStructPlanEquipment.OrderByDescending(x =&gt; x.ServContrObjStructPlanVers.ActiveUntil ?? DateTime.MaxValue).ThenByDescending(x =&gt; x.ServContrObjStructPlanVers.ActiveFrom).ThenByDescending(x =&gt; x.ActiveFrom).Select(x =&gt; x.PkServContrObjStructPlanEquipment).FirstOrDefault()
                  ))
  </code></pre>
  
  <p>Now it throws me an Exception that says:</p>
  
  <blockquote>
    <p>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 &amp;&amp; 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 &amp;&amp; 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
    <a href="https://go.microsoft.com/fwlink/?linkid=2101038" rel="noreferrer">https://go.microsoft.com/fwlink/?linkid=2101038</a> for more information.</p>
  </blockquote>
  
  <p>I am aware about EF Core 3.0-3.1 has breaking changes and that client evaluation is now performed on the top level <code>Select()</code>. Although I'd rather avoid doing that, I've tried calling all the <code>ToList()</code>, <code>AsEnumerable()</code> etc. to make it work, but it also doesn't help: the query is complex and multilevel, so calling <code>ToList()</code> 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.</p>
  
  <p>I've also tried splitting the query into separate queries to see what's going on there like this:</p>
  
  <pre><code>var intermEquipments = await this.DbContext.ServContrObjStructEquipment
                  .AsNoTracking()
                  .Include(e =&gt; e.ServContrObjStructPlanEquipment)
               .Where(e=&gt;e.ServContrObjStruct.ServContrObjStructParent.ServContrObjStructParent.Objectuuid == sectionGuid)
                  .ToListAsync();
  
              var intermEquipments1 = await this.DbContext.ServContrObjStructEquipment
                  .AsNoTracking()
                  .Include(e =&gt; e.ServContrObjStructPlanEquipment)
                  .Where(e =&gt; e.ServContrObjStruct.ServContrObjStructParent.ServContrObjStructParent.Objectuuid == sectionGuid)
                  .Select(e =&gt; e.ServContrObjStructPlanEquipment.Select(x =&gt; x.PkServContrObjStructPlanEquipment))
                  .ToListAsync();
  
              var intermEquipments2 = this.DbContext.ServContrObjStructEquipment
                  .AsNoTracking()
                  .Include(e =&gt; e.ServContrObjStructPlanEquipment)
                  .Where(e =&gt; e.ServContrObjStruct.ServContrObjStructParent.ServContrObjStructParent.Objectuuid == sectionGuid)
                  .Select(e =&gt; e.ServContrObjStructPlanEquipment
                      .OrderByDescending(x =&gt; x.ServContrObjStructPlanVers.ActiveUntil ?? DateTime.MaxValue)
                                  .ThenByDescending(x =&gt; x.ServContrObjStructPlanVers.ActiveFrom)
                                      .ThenByDescending(x =&gt; x.ActiveFrom)
                                          .Select(x =&gt; x.PkServContrObjStructPlanEquipment).ToList());
  </code></pre>
  
  <p>but it either throws me an Exception saying that </p>
  
  <blockquote>
    <p>Lambda expression used inside Include is not valid</p>
  </blockquote>
  
  <p>or NullReference (because some related properties are not loaded, as I mentioned above) or not showing the records at all.</p>
  
  <p>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!</p>