Consider i have below entities:
public class Root
{
public long Id { get; set; }
}
public class School : Root
{
public long StudentId { get; set; }
public Student Student { get; set; }
public Teacher Teacher { get; set; }
public long TeacherId { get; set; }
}
public class Student : Root
{
}
public class Teacher : Root
{
}
Now, after this fix in EF i can build left join query like this:
ctx.Schools
.GroupJoin(ctx.Teachers, school => school.TeacherId, teacher => teacher.Id,
(school, teachers) => new { school, teachers })
.SelectMany(info => info.teachers.DefaultIfEmpty(),
(info, teacher) => new { info.school, teacher })
.Where(info => info.school.Id == someSchoolId)
.Select(r => r.school);
or like this:
from school in ctx.Schools
join teacher in ctx.Teachers on school.TeacherId equals teacher.Id into grouping
from t in grouping.DefaultIfEmpty()
where school.Id == someSchoolId
select school;
The sql produced is:
SELECT [school].[Id], [school].[StudentId], [school].[TeacherId], [teacher].[Id]
FROM [Schools] AS [school]
LEFT JOIN [Teachers] AS [teacher] ON [school].[TeacherId] = [teacher].[Id]
WHERE [school].[Id] = @__someSchoolId_0
ORDER BY [school].[TeacherId]
But(!), when i try to add one more table to left join
ctx.Schools
.GroupJoin(ctx.Teachers, school => school.TeacherId, teacher => teacher.Id,
(school, teachers) => new { school, teachers })
.SelectMany(info => info.teachers.DefaultIfEmpty(),
(info, teacher) => new { info.school, teacher })
.GroupJoin(ctx.Students, info => info.school.StudentId, student => student.Id,
(info, students) => new {info.school, info.teacher, students})
.SelectMany(info => info.students.DefaultIfEmpty(),
(info, student) => new {info.school, info.teacher, student})
.Where(data => data.school.Id == someSchoolId)
.Select(r => r.school);
or
from school in ctx.Schools
join teacher in ctx.Teachers on school.TeacherId equals teacher.Id into grouping
from t in grouping.DefaultIfEmpty()
join student in ctx.Students on school.StudentId equals student.Id into grouping2
from s in grouping2.DefaultIfEmpty()
where school.Id == someSchoolId
select school;
Threre two separate sql queries produced:
SELECT [student].[Id]
FROM [Students] AS [student]
SELECT [school].[Id], [school].[StudentId], [school].[TeacherId], [teacher].[Id]
FROM [Schools] AS [school]
LEFT JOIN [Teachers] AS [teacher] ON [school].[TeacherId] = [teacher].[Id]
WHERE [school].[Id] = @__someSchoolId_0
ORDER BY [school].[TeacherId]
Looks like there are client-side left join appears.
What am i doing wrong?
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With