将含子查询的SQL转换为Lambda表达式风格的LINQ求助

原SQL逻辑 #

SELECT c.studentId, c.Name
FROM Classes c
WHERE c.Class = 'Math'
AND c.Grade IN ('yoklama', '2')
AND c.studentId IN (
SELECT c2.studentId
FROM Classes c2
WHERE c2.Class = 'Eng'
AND c2.Grade IN ('yoklama', '2'))

你的代码存在以下几个关键错误：

• 变量混淆：Where(c => pt.Class == 'Math')中pt未定义，应使用参数c
• 逻辑运算符优先级错误：成绩判断未加括号，导致逻辑混乱
• Contains用法颠倒：应为子查询的studentId集合包含当前记录的studentId，而非反过来
• 子查询语法缺失：Select(x => )未指定要选择的字段
• 过早执行ToList()：会提前加载数据到内存，破坏EF的SQL生成逻辑
• 表名不匹配：原SQL子查询基于Classes表，你代码中误用了ParticipTests（若为笔误需修正）

修正后的代码 #

方式一：提取子查询（可读性更高） #

// 先获取选了Eng课程且成绩符合要求的学生ID集合
var eligibleEngStudentIds = context.Classes
    .Where(c2 => c2.Class == "Eng" 
                 && (c2.Grade == "yoklama" || c2.Grade == "2"))
    .Select(c2 => c2.studentId);

var reportDtos = context.Classes
    .Where(c => c.Class == "Math" 
                && (c.Grade == "yoklama" || c.Grade == "2")
                && eligibleEngStudentIds.Contains(c.studentId))
    // 若ReportDto需要导航属性，保留Include，建议用强类型写法
    .Include(c => c.Area)
    .Include(c => c.Questions)
    .Include(c => c.Class.Test) // 注意：需匹配你的实体类导航属性定义
    .Select(c => new ReportDto
    {
        StudentId = c.studentId,
        Name = c.Name,
        // 根据ReportDto补充其他映射字段
        // ...
    })
    .ToList();

方式二：子查询嵌入主查询（更紧凑） #

var reportDtos = context.Classes
    .Where(c => c.Class == "Math" 
                && (c.Grade == "yoklama" || c.Grade == "2")
                && context.Classes
                    .Any(c2 => c2.studentId == c.studentId 
                               && c2.Class == "Eng" 
                               && (c2.Grade == "yoklama" || c2.Grade == "2")))
    .Include(c => c.Area)
    .Include(c => c.Questions)
    .Include(c => c.Class.Test)
    .Select(c => new ReportDto
    {
        StudentId = c.studentId,
        Name = c.Name,
        // 补充其他字段
    })
    .ToList();

补充说明 #

• 若Grade是可空字符串类型，可添加c.Grade.HasValue判断，修改为：(c.Grade.HasValue && (c.Grade == "yoklama" || c.Grade == "2"))
• C#中字符串需用双引号，单引号仅适用于char类型，注意匹配你的实体类字段类型
• Include部分可根据实际需求保留或删除，若不需要导航属性可直接移除

内容的提问来源于stack exchange，提问作者Coko