已调用ToList仍出现LINQ表达式无法翻译问题求助
报错原因
你在未实际执行的EF Core IQueryable查询中直接引用了已加载到内存的List<AssessmentItem>集合assessmentItems做过滤逻辑:
EF Core会将IQueryable对应的整棵表达式树尝试翻译为SQL语句交由数据库执行,但数据库无法访问你程序内存中的集合数据,EF也无法将内存集合的遍历、过滤逻辑转换为合法SQL语法,因此抛出无法翻译的异常。
解决方法
方法1:先拉取Rubric数据到内存再做投影
这是改动最小的方案,只需要在执行投影前将数据库查询转为内存操作即可,适合Rubric相关数据量不大的场景:
List<RubricDTO> resultToReturn = _context.Rubrics .Include(r => r.RubricItemCategories) .ThenInclude(c => c.RubricItems) // 需新增加载关联的RubricItems,避免内存中数据缺失 .Where(r => r.Id == rubricId) .AsEnumerable() // 新增此行,后续所有操作在程序内存执行,不需要转SQL .Select(r => new RubricDTO { Ranking = r.Ranking, Description = r.Description, RubricItemCategories = r.RubricItemCategories.Select(ric => new RubricItemCategoryDTO { Id = ric.Id, Description = ric.Description, RubricItems = ric.RubricItems.Select(ri => new RubricItemDTO { Id = ri.Id, Title = ri.Title, CurrentScore = assessmentItems.Count > 0 ? assessmentItems .Where(aitem2 => aitem2.RubricItemId == ri.Id && aitem2.Assessment.EvaluatorId == userId) .Select(s => s.CurrentScore) .FirstOrDefault() : 0, }) }).OrderBy(ric => ric.Order).ToList() }).ToList();
方法2:提前转字典优化查询性能
如果assessmentItems数据量较大,每次遍历List查询效率低,可以提前构造复合键字典实现O(1)取值:
// 提前构造字典,键为(RubricItemId, EvaluatorId),值为对应分数 var scoreDict = assessmentItems .GroupBy(x => new {x.RubricItemId, x.Assessment.EvaluatorId}) .ToDictionary(g => g.Key, g => g.First().CurrentScore); List<RubricDTO> resultToReturn = _context.Rubrics .Include(r => r.RubricItemCategories) .ThenInclude(c => c.RubricItems) .Where(r => r.Id == rubricId) .AsEnumerable() .Select(r => new RubricDTO { Ranking = r.Ranking, Description = r.Description, RubricItemCategories = r.RubricItemCategories.Select(ric => new RubricItemCategoryDTO { Id = ric.Id, Description = ric.Description, RubricItems = ric.RubricItems.Select(ri => new RubricItemDTO { Id = ri.Id, Title = ri.Title, CurrentScore = scoreDict.TryGetValue(new {ri.Id, EvaluatorId = userId}, out var score) ? score : 0, }) }).OrderBy(ric => ric.Order).ToList() }).ToList();
方法3:合并逻辑全数据库端执行
如果不想提前加载assessmentItems到内存,可以直接把关联逻辑合并到主查询中,让EF翻译为JOIN语句在数据库端执行,适合数据量较大、希望减少内存占用的场景:
List<RubricDTO> resultToReturn = _context.Rubrics .Include(r => r.RubricItemCategories) .Where(r => r.Id == rubricId) .Select(r => new RubricDTO { Ranking = r.Ranking, Description = r.Description, RubricItemCategories = r.RubricItemCategories.Select(ric => new RubricItemCategoryDTO { Id = ric.Id, Description = ric.Description, RubricItems = ric.RubricItems.Select(ri => new RubricItemDTO { Id = ri.Id, Title = ri.Title, CurrentScore = _context.AssessmentItems .Where(aitem2 => aitem2.IsActive && aitem2.Assessment.PeerReviewAssignmentId == peerReviewAssignmentId && aitem2.Assessment.RubricId == rubricId && aitem2.RubricItemId == ri.Id && aitem2.Assessment.EvaluatorId == userId) .Select(s => s.CurrentScore) .FirstOrDefault() }) }).OrderBy(ric => ric.Order).ToList() }).ToList();
内容的提问来源于stack exchange,提问作者renakre
相关产品推荐
相关产品推荐

