如何在Entity Framework Core中建立几个左联接查询 [英] How to build several left join query in 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中此修复后，我可以建立左连接查询，如下所示:

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，并将查询的值复制到剪贴板，则会在FROM LEFT OUTER JOIN >

Errata Corrige

从EF 6中可以看到2个左外部连接.

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

无法翻译，将在本地进行评估.

这里唯一的说明是-没有选择其他表-没有理由在表中找到多个左联接第一名

EF核心设计

基于github回购中所见的单元测试，并尝试更接近满足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 ='文本'，CommandTimeout = '30']

选择"x"."SchoolId"， "x"."StudentId"，"x"."TeacherId"，"s"."StudentId"，"s".名称"， "t"."TeacherId"，"t".名称"

从学校"为"x"的

左加入学生" AS"s" ON"x"."StudentId" ="s"."StudentId"

左加入教师" AS "t"在"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; }
}

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

解决方案

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

这篇关于如何在Entity Framework Core中建立几个左联接查询的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！