Multiple left join using lambda syntax in Entity Framework

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();

In EF we almost never use manual joins, but navigation properties. With navigation properties the query should be simple Select or SelectMany with Where where needed. For more concrete answer, we need the relevant entity model (classes and configuration), not tables. — Ivan Stoev
– Ivan Stoev, Commented May 29, 2019 at 19:39
LINQ method, DefaultIfEmpty(), which is quite similar to the Left Join of SQL . c-sharpcorner.com/UploadFile/97fc7a/linq-method-defaultifempty — Amin Golmahalleh
– Amin Golmahalleh, Commented May 30, 2019 at 8:50
I am sure EF is capable of doing it. I want to know how. There could be other better approaches, but I want to know how this can be done using lambda. The above link wasn't helpful, but I appreciate the help. — Rajeev Menon
– Rajeev Menon, Commented May 30, 2019 at 12:13

Rajeev Menon · Accepted Answer · 2019-05-31 18:21:36Z

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();

Collectives™ on Stack Overflow

Multiple left join using lambda syntax in Entity Framework

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related