Ho le seguenti 3 tabelle
corsi
Id, SortOrder, CourseName, CourseArea, CourseFor
Studenti
Id, FullName
CourseStudents
CourseId, StudentId, CollegeId
Requisiti:
Ottieni tutti gli studenti del corso nell'area "Medicina" per gli studenti "stranieri" disponibili nel College "125". Includi corsi anche se non ci sono studenti iscritti.
Query SQL funzionante:
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
Qualcuno può aiutarmi con la sintassi lambda (ho provato GroupJoin
)? Mentre quello che sto cercando è la sintassi lambda, anche la sintassi della query è buona da sapere.
AGGIORNAMENTO: Sono molto vicino, ma ancora non completo
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();
SOLUZIONE: ho funzionato nel modo seguente. Vedi le linee 3 e 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();