为什么左外方加入? [英] Why the Left Outer join?

问题描述

奇怪的一件事. (可能一点也不奇怪)

weird one. (Probably not weird, at all)

我有3个对象，即Employee，Rota和Department.

I have 3 objects, Employee, Rota and Department.

public class Employee
{
    public int Id { get; set; }
    public String Name { get; set; }
    public virtual Department Department { get; set; }
}

internal class EmployeeMapping : EntityTypeConfiguration<Employee>
{
    public EmployeeMapping()
    {
        HasKey(a => a.Id);
        Property(a => a.Id).HasColumnName("UserId");

        HasRequired<Department>(a => a.Department).WithOptional().Map(a => a.MapKey("DepartmentId"));
    }
}

public class Department
{
    public int Id { get; set; }
    public String Name { get; set; }
}

internal class DepartmentMapping : EntityTypeConfiguration<Department>
{
    public DepartmentMapping()
    {
        HasKey(a => a.Id);
        Property(a => a.Id).HasColumnName("DepartmentId");
    }
}

public class Rota
{
    public int Id { get; set; }
    public virtual Employee Employee { get; set; }
    public virtual Department Department { get; set; }
}

internal class RotaMapping : EntityTypeConfiguration<Rota>
{
    public RotaMapping()
    {
        HasKey(a => a.Id);
        Property(a => a.Id).HasColumnName("RotaId");

        HasOptional<Employee>(a => a.Employee).WithOptionalDependent().Map(a => a.MapKey("EmployeeId"));
        HasOptional<Department>(a => a.Department).WithOptionalDependent().Map(a => a.MapKey("DepartmentId"));
    }
}

并不复杂，真的. Rota可以分配一个员工和/或部门，所有这些都使用Fluent进行配置.我所有的关联都是正确的(架构是完美的)，但是我有一个奇怪的怪癖.

Not complicated, at all really. Rota can have an Employee and/or a Department assigned to it, all of this is configured using Fluent. All of my associations are correct (the schema is perfect), however I have a weird oddity.

当我执行myContext.Departments.FirstOrDefault()并查看生成的SQL时，Employee&上有一个 LEFT OUTER JOIN .罗塔为什么在那儿?
我不希望它这样做.也许我的Fluent映射不正确?我已经尝试了各种方法，但似乎无法弄清楚.如果我想要一个Rota对象，我会理解，它将加入该部门.但并非相反！

When I do a myContext.Departments.FirstOrDefault() and have a look at the SQL Generated, there is a LEFT OUTER JOIN on Employee & Rota. Why is this there?
I don't want it to do this. Maybe my Fluent mappings are incorrect? I've tried all sorts, but can't seem to figure it out. I would understand it if I want a Rota object, that would join on the Department. But not the other way around!

如果我执行myContext.Departments.AsNoTracking().FirstOrDefault()，则不会执行 LEFT OUTER JOIN的.

If I do myContext.Departments.AsNoTracking().FirstOrDefault() it doesn't do the LEFT OUTER JOIN's.

有什么想法的人吗?

干杯， D

推荐答案

原因是映射不正确.看起来正确，但事实并非如此.改用这些:

The reason is incorrect mapping. It looks correct but it is not. Use these instead:

internal class EmployeeMapping : EntityTypeConfiguration<Employee>
{
    public EmployeeMapping()
    {
        HasKey(a => a.Id);
        Property(a => a.Id).HasColumnName("UserId");

        HasRequired<Department>(a => a.Department).WithMany()
                                                  .Map(a => a.MapKey("DepartmentId"));
    }
}

internal class RotaMapping : EntityTypeConfiguration<Rota>
{
    public RotaMapping()
    {
        HasKey(a => a.Id);
        Property(a => a.Id).HasColumnName("RotaId");

        HasOptional<Employee>(a => a.Employee).WithMany()
                                              .Map(a => a.MapKey("EmployeeId"));
        HasOptional<Department>(a => a.Department).WithMany()
                                                  .Map(a => a.MapKey("DepartmentId"));
    }
}

创建数据库时，您的映射被正确解释，并且数据库看起来正确，但是EF认为您将所有关系一对一映射.这会使EF混淆，它将生成用于一对一创建内部实体引用的查询.当您告诉EF依赖实体是可选的时，这些左联接对于一对一关系是必要的-EF除非加载它们的键，否则不知道它们是否存在.

Your mapping is correctly interpreted when creating database and database looks correct but EF thinks that you map all relations as one-to-one. That confuse EF and it will generate queries used for one-to-one to create internal entity references. These left joins are necessary for one-to-one relation when you tell EF that dependent entities are optional - EF doesn't know if they exist unless it loads their keys.

这篇关于为什么左外方加入?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！