c# - 如何在 Entity Framework Core 中构建多个左连接查询

Question

考虑我有以下实体:

    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
    {
    }

现在，在 this 之后在 EF 中修复我可以像这样构建左连接查询:

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

或者像这样:

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;

产生的sql是:

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]

但是(!)，当我尝试向左连接添加一个表时

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

或

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;

产生了两个独立的 sql 查询:

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]

看起来有客户端左连接出现。

我做错了什么？

Answer 1

您需要从 所有 3 个表中选择，以便在 Entity Framework 从 Linq AST 转换时左连接有意义到 SQL

select new { school, t, s };

代替

select school;

然后，如果您在程序执行期间从 Visual Studio 检入调试并将查询的值复制到剪贴板，您会发现 - 正如预期的那样 - 2 LEFT OUTER JOIN 之后FROM

勘误更正

2 个左外部联接在 EF 6 中可见。

EF Core 记录器写入查询...

could not be translated and will be evaluated locally.

这里唯一要注意的是 - 没有选择其他表 - 没有理由在第一名

EF 核心设计

基于在 github repo 中看到的单元测试并试图更接近 OP 要求，我会建议以下查询

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

var results = querySO.ToArray();

这次我从 EF Core Logger 看到了几个 LEFT JOIN

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"

定义了一个模型

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

}

和类

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; }
}