How to build several left join query in Entity Framework Core
<p>Consider i have below entities:</p>

<pre><code>    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
    {
    }
</code></pre>

<p>Now, after <a href="https://github.com/aspnet/EntityFramework/commit/a47422fdc05dfb703378214e2a466f0225946f29" rel="noreferrer">this</a> fix in EF i can build left join query like this:</p>

<pre><code>ctx.Schools
    .GroupJoin(ctx.Teachers, school =&gt; school.TeacherId, teacher =&gt; teacher.Id,
        (school, teachers) =&gt; new { school, teachers })
    .SelectMany(info =&gt; info.teachers.DefaultIfEmpty(),
        (info, teacher) =&gt; new { info.school, teacher })
    .Where(info =&gt; info.school.Id == someSchoolId)
    .Select(r =&gt; r.school);
</code></pre>

<p>or like this: </p>

<pre><code>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;
</code></pre>

<p>The sql produced is:</p>

<pre><code>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]
</code></pre>

<p>But(!), when i try to add one more table to left join</p>

<pre><code>ctx.Schools
    .GroupJoin(ctx.Teachers, school =&gt; school.TeacherId, teacher =&gt; teacher.Id,
        (school, teachers) =&gt; new { school, teachers })
    .SelectMany(info =&gt; info.teachers.DefaultIfEmpty(),
        (info, teacher) =&gt; new { info.school, teacher })
    .GroupJoin(ctx.Students, info =&gt; info.school.StudentId, student =&gt; student.Id,
        (info, students) =&gt; new {info.school, info.teacher, students})
    .SelectMany(info =&gt; info.students.DefaultIfEmpty(), 
        (info, student) =&gt; new {info.school, info.teacher, student})
    .Where(data =&gt; data.school.Id == someSchoolId)
    .Select(r =&gt; r.school);
</code></pre>

<p>or</p>

<pre><code>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;
</code></pre>

<p>Threre two separate sql queries produced:</p>

<pre><code>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]
</code></pre>

<p>Looks like there are client-side left join appears.</p>

<p>What am i doing wrong?</p>