我已经苦苦挣扎了四天,但一点进展都没有。 在更新到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()
))
现在它抛出一个异常,说:
LINQ表达式'DbSet.Where(s3 => s3.RecStatus == 1).Where(s3 => EF.Property, "PkServContrObjStructEquipment") != null && EF.Property, "PkServContrObjStructEquipment") == EF.Property).Select(s3 => s3.PkServContrObjStructPlanEquipment).Contains((MaterializeCollectionNavigation(navigation: Navigation: ServContrObjStructEquipment.ServContrObjStructPlanEquipment, subquery: DbSet.Where(s4 => s4.RecStatus == 1).Where(i => EF.Property, "PkServContrObjStructEquipment") != null && EF.Property, "PkServContrObjStructEquipment") == EF.Property)).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())'无法翻译。请重新编写查询,以便可以翻译,或者通过插入对AsEnumerable()、AsAsyncEnumerable()、ToList()或ToListAsync()的调用来明确切换到客户端评估。有关更多信息,请参见https://go.microsoft.com/fwlink/?linkid=2101038。
我知道EF Core 3.0-3.1有重大变化,现在客户端评估是在顶层的
Select()
上执行的。虽然我宁愿避免这样做,但我尝试调用所有ToList()
、AsEnumerable()
等来使其工作,但也没有帮助:查询是复杂和多级的,因此在任何时候调用ToList()
要么会破坏它,要么不会获取相关记录(可能是由于惰性加载),而这些记录随后需要进一步执行查询。我还尝试将查询拆分成单独的查询,以查看那里发生了什么,像这样:
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());
但是它要么抛出一个异常,说
在Include中使用的Lambda表达式无效
要么NullReference(因为一些相关属性没有加载,如我上面提到的),或者根本不显示记录。
我熟悉EntityFramework,虽然我不是专家。有没有办法重写这个查询,使它可以被翻译或者做任何事情使它工作? 谢谢!
Contains
表达式,我已经确认过了。 - Yurii Proniuk