如何在Entity Framework Core中構建多個左連接查詢

c# entity-framework-core left-join

考慮我有以下實體:

    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中進行修復後,我可以構建左連接查詢,如下所示:

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個表中進行選擇 ,以便當Entity Framework從Linq AST轉換為SQL時, 左連接是有意義

select new { school, t, s };

代替

select school;

然後,如果您在程序執行期間從Visual Studio檢入Debug並將查詢的值複製到剪貼板,您將找到 - 如預期的那樣 - 在FROM之後的2個LEFT OUTER JOIN

勘誤表

從EF 6可以看到2個左外連接。

EF Core記錄器寫入查詢...

無法翻譯,將在本地進行評估。

這裡唯一的注意事項是 - 在沒有選擇其他表的情況下 - 首先沒有理由找到多個左連接

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;執行DbCommand(0ms)[Parameters = [],CommandType ='Text',CommandTimeout = '30']

選擇“x”。“SchoolId”,“x”。“StudentId”,“x”。“TeacherId”,“s”。“StudentId”,“s”。“name”,“t”。“TeacherId”,“ T“,”名“

來自“學校”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; }
}


Related

許可下: CC-BY-SA with attribution
不隸屬於 Stack Overflow
許可下: CC-BY-SA with attribution
不隸屬於 Stack Overflow