Multiple left join using lambda syntax in Entity Framework

entity-framework entity-framework-core

Question

I have the following 3 tables

Courses

Id, SortOrder, CourseName, CourseArea, CourseFor

Students

Id, FullName

CourseStudents

CourseId, StudentId, CollegeId

Requirement:

Get all course students from the 'Medical' area for 'foreign' students available in College '125'. Include courses even if there are no students enrolled in it.

Working SQL query:

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

Can anyone help me with the lambda syntax (I tried GroupJoin)? While what I am looking for is the lambda syntax, the query syntax is also good to know.

UPDATE: I am very close, but still not complete

    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();
1
0
5/31/2019 6:18:16 PM

Popular Answer

SOLUTION: I got it working by doing the following. See line 3 and 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();
0
5/31/2019 6:21:36 PM


Related Questions





Related

Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow
Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow