我有以下3个表格
课程
Id, SortOrder, CourseName, CourseArea, CourseFor
学生
Id, FullName
课程学生
CourseId, StudentId, CollegeId
要求:
获取学院“125”中所有“医学”领域的“留学生”可参加的课程的学生。即使该课程没有学生注册也要包含在内。
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();
Where
的Select
或者SelectMany
。为了得到更具体的答案,我们需要相关的实体模型(类和配置),而不是表格。 - Ivan Stoev