Rewrite query in a form that can be translated, or switch to client evaluation explicitly?

[ajitgoel]

I am getting the rewrite the query in a form that can be translated error when trying to run a entity framework query with a Include and ThenInclude clause. All this code was working before the application was moved from Microsoft.EntityFramework version 3.1 to Microsoft.EntityFramework version 5.0.4.

This seems to be Uri mapping related InMemory provider bug (does not happen with SqlServer). Happens also in the latest official at this time 5.0.7. Can you please advise?

internal IEnumerable<ProductHierarchy> ProductHierarchiesWithDetail
  get
  {
    var item = ProductHierarchies.Include(x => X.BusinessAssocs).ThenInclude(x => X.Business).AsEnumerable();
    return item; 
  }
}
public class ProductHierarchy
{
  public Uri ProductHierarchyURI { get; set; }
  public IList<HierarchyBusinessAssoc> BusinessAssocs{ get; set; } = new List<HierarchyBusinessAssoc>();
}

public class Hierarchy BusinessAssoc
{
  public Uri ProducthierarchyURI { get; set; }
  public Uri BusinessURI { get; set; }
  public ProductHierarchy Hierarchy { get; set; }
  public Business Business { get; set; }
}

public class Business
{
  public Uri BusinessURI { get; set; }
  public IList<HierarchyBusinessAssoc> HierarchyAssocs { get; set; } = new List<HierarchyBusinessAssoc>();
  public IList<ProductType> ProductTypes { get; set; } = new List<ProductType>();
}

public class ProductHierarchyEntityConfiguration : IEntityTypeConfiguration<ProductHierarchy>
{
public void configure(EntityTypeBuilder<ProductHierarchy> builder)
{
  builder. Totable("ProductHierarchy");
  builder.Haskey(e => new { e.ProducthierarchyURI });
  builder.HasMany(x => x.BusinessAssocs).WithOne (assoc => assoc.Hierarchy);
}

public class HierarchyBusinessAssocEntityConfiguration : IEntityTypeConfiguration<HierarchyBusinessAssoc>
{
  public void Configure(EntityTypeBuilder<HierarchyBusinessAssoc> builder)
  {
    builder.ToTable("ProductHieracyBusinessAssociation");
    builder.Haskey(e => new { e.producthierarchyURI, e.BusinessURI });
    builder.Hasone (assoc => assoc.Hierarchy).WithMany (h => h.BusinessAssocs).HasForeignKey(assoc => assoc.ProductHierarchyURI);
    builder.HasOne (assoc => assoc. Business).WithMany (ph => ph.HierarchyAssocs).HasForeignKey(x => x.BusinessURI);
  }
}

public class BusinessEntityConfiguration : IEntityTypeConfiguration<Business>
{
  public void Configure(EntityTypeBuilder<Business> builder)
  {
    builder.ToTable("Business");
    builder.Haskey(e => new {e.BusinessURI});
    builder.HasMany (pt => pt.HierarchyAssocs).WithOne(x => x.Business).HasForeignKey(x => x.BusinessURI);
  }
}

Exception:

The LINQ expression 'DbSet<HierarchyBusinessAssoc> 0.Where(h => EF.Property<Uri> (EntityShaperExpression:Entity Type: ProductHierarchyValueBufferExpression:
ProjectionBindingExpression: EmptyProjection MemberIsNullable: False
,"ProductHierarchyURI") != null && object.Equals(objA: EF.Property<Uri> (EntityShaperExpression:
EntityType: ProductHierarchyValueBufferExpression:ProjectionBindingExpression: Empty Projection MemberIsNullable: False,"ProductHierarchyURI"), objB: EF.Property<Uri>(h, "ProductHierarchyURI")))
Join
inner: DbSet<Business> 0.outerKeySelector: h => EF.Property<Uri>(h, "BusinessURI"),
innerKeySelector: b => EF.Property<Uri>(b, "BusinessURI"),
resultSelector: (0, 1) => new TransparentIdentifier<HierarchyBusinessAssoc, Business>
Outer = 0,Inner = i' 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 'AsEnumerable', 'AsAsyncEnumerable', 'ToList', or 'ToListAsync'. 

[ajcvickers]

We recommend against using the in-memory provider for testing--see Testing EF Core Applications. While we have no plans to remove the in-memory provider, we will not be adding any new features to this provider because we believe valuable development time is better spent in other areas. When feasible, we plan to still fix regressions in existing behavior.