Why the Left Outer join?

Question

weird one. (Probably not weird, at all)

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

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.

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!

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

Any ideas guys?

Cheers, D

Answer 1

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

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.