How to build several left join query in Entity Framework Core

Question

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 are 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?

Answer 1

You need to select from all the 3 tables so that the left joins would make sense when the Entity Framework translates from the Linq AST to SQL

select new { school, t, s };

instead of

select school;

Then, if you check in Debug from Visual Studio during the program execution and you copy the value of the query to the clipboard, you'll find - as expected - 2 LEFT OUTER JOINs after the FROM

Errata Corrige

The 2 left outer joins were visible from EF 6.

EF Core logger writes that the query ...

could not be translated and will be evaluated locally.

The only remark here is that - without selecting the other tables - there would have been no reasons to find multiple left joins in the first place

EF Core design

Based on the unit testing seen in the github repo and trying to closer meet the OP requirements, I would suggest the following query

var querySO = ctx.Schools
        .Include(x => x.Student)
        .Include(x => x.Teacher)
        ;

var results = querySO.ToArray();

This time I see a couple of LEFT JOIN from EF Core Logger

PRAGMA foreign_keys=ON;Executed DbCommand (0ms) [Parameters=[], CommandType='Text', CommandTimeout='30']

SELECT "x"."SchoolId", "x"."StudentId", "x"."TeacherId", "s"."StudentId", "s"."name", "t"."TeacherId", "t"."name"

FROM "Schools" AS "x"

LEFT JOIN "Students" AS "s" ON "x"."StudentId" = "s"."StudentId"

LEFT JOIN "Teachers" AS "t" ON "x"."TeacherId" = "t"."TeacherId"

having defined a model

protected override void OnModelCreating(ModelBuilder modelBuilder)
{
    base.OnModelCreating(modelBuilder);

    modelBuilder.Entity<School>().HasKey(p => p.SchoolId);
    modelBuilder.Entity<Teacher>().HasKey(p => p.TeacherId);
    modelBuilder.Entity<Student>().HasKey(p => p.StudentId);


    modelBuilder.Entity<School>().HasOne<Student>(s => s.Student)
        .WithOne().HasForeignKey<School>(s => s.StudentId);
    modelBuilder.Entity<School>().HasOne<Teacher>(s => s.Teacher)
        .WithOne().HasForeignKey<School>(s => s.TeacherId);

}

and classes

public class School 
{
    public long SchoolId { get; set; }
    public long? StudentId { get; set; }
    public Student Student { get; set; }
    public Teacher Teacher { get; set; }
    public long? TeacherId { get; set; }
}

public class Student 
{
    public long StudentId { get; set; }
    public string name { get; set; }
}

public class Teacher 
{
    public long TeacherId { get; set; }
    public string name { get; set; }
}