entity-framework - 在 Entity Framework 中使用 lambda 语法进行多个左连接

Question

我有以下3张表

类(class)

Id, SortOrder, CourseName, CourseArea, CourseFor

学生

Id, FullName

类(class)学生

CourseId, StudentId, CollegeId

要求:

为“125”学院的“外国”学生获取“医学”领域的所有类(class)学生。即使没有学生注册，也包括类(class)。

工作 SQL 查询:

SELECT cr.Id, cr.CourseName, st.FullName
FROM dbo.Courses cr
LEFT JOIN dbo.CourseStudents cst ON cr.Id = cst.CourseId 
                                 AND cst.CollegeId = 125
LEFT JOIN dbo.Students st ON cst.StudentId = st.Id
WHERE 
    cr.CourseArea = 'Medical'
    AND cr.CourseFor = 'Foreigner'
ORDER BY 
    cr.SortOrder, st.FullName

任何人都可以帮助我使用 lambda 语法(我试过 GroupJoin)吗？虽然我正在寻找的是 lambda 语法，但了解查询语法也很不错。

更新:我非常接近，但仍未完成

    context.Courses
        .GroupJoin(context.CourseStudents,
            x => new { x.Id, CollegeId NOT IN COURSES TABLE :( },
            y => new { Id = y.CourseId, y.CollegeId=125 },
            (x, y) => new { Courses = x, CourseStudents = y })
        .SelectMany(x => x.CourseStudents.DefaultIfEmpty(),
            (x, y) => new { x.Courses, CourseStudents = y })
        .GroupJoin(context.Students,
            x => x.CourseStudents.StudentId,
            y => y.Id,
            (x, y) => new { CoursesCourseStudents = x, Students = y }
        )
        .SelectMany(x => x.Students.DefaultIfEmpty(),
        (x, y) => new { x = x.CoursesCourseStudents, Students = y })
        .Select(x => new
        {
            x.x.Courses.Id,
            x.x.Courses.CourseName,
            x.Students.FullName,
            x.x.CourseStudents.CollegeId,
            x.x.Courses.CourseFor,
            x.x.Courses.CourseArea,
            x.x.Courses.SortOrder
        })
        .Where(x => x.CourseFor == "Foreigner" && x.CourseArea == "Medical")
        .OrderBy(x => x.SortOrder)
        .ToList();

Answer 1

解决方案:我通过执行以下操作使其正常工作。请参阅第 3 行和第 4 行。

context.Courses
    .GroupJoin(context.CourseStudents,
        x => new { x.Id, CollegeId=125 },
        y => new { Id = y.CourseId, y.CollegeId },
        (x, y) => new { Courses = x, CourseStudents = y })
    .SelectMany(x => x.CourseStudents.DefaultIfEmpty(),
        (x, y) => new { x.Courses, CourseStudents = y })
    .GroupJoin(context.Students,
        x => x.CourseStudents.StudentId,
        y => y.Id,
        (x, y) => new { CoursesCourseStudents = x, Students = y }
    )
    .SelectMany(x => x.Students.DefaultIfEmpty(),
    (x, y) => new { x = x.CoursesCourseStudents, Students = y })
    .Select(x => new
    {
        x.x.Courses.Id,
        x.x.Courses.CourseName,
        x.Students.FullName,
        x.x.CourseStudents.CollegeId,
        x.x.Courses.CourseFor,
        x.x.Courses.CourseArea,
        x.x.Courses.SortOrder
    })
    .Where(x => x.CourseFor == "Foreigner" && x.CourseArea == "Medical")
    .OrderBy(x => x.SortOrder)
    .ToList();