c# - 如何在Entity Framework Core中建立几个左联接查询

假设我有以下实体：

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

现在，在ef中修复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);

或者像这样：

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]

似乎出现了客户端左连接。
我做错什么了？

最佳答案

您需要从所有3个表中进行选择，这样当实体框架从linq ast转换为sql时，左连接就有意义了

select new { school, t, s };

而不是

select school;

然后，如果在程序执行期间从visual studio签入debug并将查询的值复制到剪贴板，则在
勘误表
从ef 6可以看到2个左外连接。
EF核心记录器写入查询…
无法翻译，将在本地计算。
这里唯一要注意的是，如果不选择其他表，就没有理由首先找到多个左连接
EF核心设计
基于github repo中的单元测试，并试图更接近于op需求，我建议如下查询

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

var results = querySO.ToArray();

这一次我从ef core logger中看到了几个LEFT OUTER JOIN。
pragma foreign_keys=on；已执行dbcommand（0ms）[参数=[]，
commandType='Text'，commandTimeout='30']
选择“X”。“学校ID”，
“x”。“studentid”，“x”。“teacherid”，“s”。“studentid”，“s”。“name”，
“t”“教师id”“t”“姓名”
从“学校”变成“X”
左联“学生”
“x”上的“s”。“studentid”=“s”。“studentid”
左加入“教师”为
“x”上的“t”。“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; }
}

关于c# - 如何在Entity Framework Core中建立几个左联接查询，我们在Stack Overflow上找到一个类似的问题：https://stackoverflow.com/questions/38813010/