title: How to build several left join query in Entity Framework Core
body: <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>