linq - 尝试获取计数时 Linq 中的子查询
问题描述
我试图让一些子查询在我的电话中工作。我试图让这个调用一次访问数据库,但我一生无法解决如何。查询在 GoodSections 部分中断。我尝试了许多不同的方法来做到这一点。我不断收到此消息:
无法翻译。以可翻译的形式重写查询,或通过插入对 AsEnumerable()、AsAsyncEnumerable()、ToList() 或 ToListAsync() 的调用显式切换到客户端评估
有人能帮我吗?
var test = context.UserAssessments.Include(n => n.Assessment).Include(n => n.UserSections).ThenInclude(userSection => userSection.Section)
.OrderBy(n => n.StartDateTime);
MyAssessments = await test.Select(assessment => new MyAssessmentVM()
{
Assessment = assessment.Assessment.Name,
CompletedDateTime = assessment.CompletedDateTime,
StartedDateTime = assessment.StartDateTime,
UserAssessmentID = assessment.ID,
GoodSections = assessment.UserSections.Where(userSection => userSection.Section.SectionType != SectionTypeEnum.Reading)
.Count(n => n.Percentage < n.Section.ReadinessRangeHigh && n.Percentage > n.Section.ReadinessRangeLow)
}).ToListAsync();
解决方案
你的代码:
GoodSections = assessment.UserSections
.Where(userSection => userSection.Section.SectionType != SectionTypeEnum.Reading)
// End of Where!
.Count(n => n.Percentage < n.Section.ReadinessRangeHigh
&& n.Percentage > n.Section.ReadinessRangeLow)
显然,每个 Assesment 都有一个由零个或多个 UserSection 组成的序列。在我看来,每个 UserSection 都有一个百分比和一个部分。
您使用 Include 来访问此 Section 的值,因此我假设 Section 在不同的表中,具有一对多关系:每个 Section 是零个或多个 UserSection 的部分;每个 UserSection 都属于确切的一个 Section,即外键所指的 Section。
首先,尝试简化您的计数,如果这没有帮助,请考虑使用 GroupJoin。
GoodSections = assessment.UserSections
.Where(userSection => userSection.Section.SectionType != SectionTypeEnum.Reading
&& userSection.Percentage < userSection.Section.ReadinessRangeHigh
&& userSection.Percentage > userSection.Section.ReadinessRangeLow)
.Count(),
自己做 GroupJoin:
var test = dbContext.UserAssessments.GroupJoin(
dbContext.UserSections,
userAssessment => userAssesment.Id // from every Assessment take the primary key
userSection => userSection.AssesmentId, // from every UserSection take the foreign key
// parameter resultSelector: from every UserAssesment, with all its UserSections
// make one new
(userAssessment, userSectionsOfThisAssessment) => new
{
UserAssessmentID = userAssessment.ID,
StartedDateTime = userAssessment.StartDateTime,
CompletedDateTime = userAssessment.CompletedDateTime,
// To get the name, we need to get the Assesment that my foreign key refers to
AssessmentName = dbContext.Assessments
.Where(assessment => assessment.AssessmentId == userAssesment.Id)
.Select(assessment => assessment.Name)
.FirstOrDefault(),
GoodSections = ... // TODO
});
我不确定,但在我看来,Sections 和 UserSections 之间存在一对多的关系:每个 Section 都有零个或多个 UserSections;每个 UserSection 只属于一个 Section,即外键所指的 Section。
因此,对于每个 userSectionOfThisAssessment,我们需要获取外键引用的 Section:标准内部连接
GoodSections = userSectionsOfThisAssessment.Join(
dbContext.Sections
userSection => userSection.SectionId, // take the foreign key to the section
section => section.Id, // take the sections's primary key
(userSection, section) => new
{
SectionType = section.SectionType,
Percentage = userSection.Percentage,
MaxPercentage = section.ReadinessRangeHigh,
MinPercentage = section.ReadinessRangeLow,
})
.Where(joinResult => joinResult.SectionType != SectionTypeEnum.Reading
&& joinResult.Percentage < MaxPercentage
&& joinResult.Percentage > MinPercentage)
.Count(),
对于 GoodSection,我们需要userSectionsOfThisAssessment
与所有 Section 进行 GroupJoin。我不确定这是一对多的关系,还是一个男人
MyAssessments = await test.Select(assessment => new MyAssessmentVM()
{
Assessment = assessment.Assessment.Name,
CompletedDateTime = assessment.CompletedDateTime,
StartedDateTime = assessment.StartDateTime,
UserAssessmentID = assessment.ID,
GoodSections = assessment.UserSections.Where(userSection => userSection.Section.SectionType != SectionTypeEnum.Reading)
.Count(n => n.Percentage < n.Section.ReadinessRangeHigh && n.Percentage > n.Section.ReadinessRangeLow)
}).ToListAsync();
推荐阅读
- python-3.x - 如何对 csv 文件中的一行或一列的值求和?
- r - 数值积分和优化 R 的问题
- python - 如何以最快的方式对填充有列表的数组(大 N)进行排序?
- java - 正则表达式在Java中的括号内获取字符串?(包括括号内的另一个括号)
- sequence-diagram - 如何增加 ZenUML 序列图中参与者之间的空间?
- jquery - 在循环内更改多个下拉列表值
- laravel - 将 laravel 表单更改为 html 表单
- angular - 如何使用 ng2-stompjs 向许多订阅者广播消息?
- google-cloud-messaging - 在 GCM 弃用后,我能否获得新的 GCM 令牌?
- pytorch - 我不明白在 pytorch 中训练分类器的代码