My .Include statement doing INNER JOIN instead of LEFT JOIN (EF Core 3.1)

Question

I have seen that basically I need to make my navigational property foreign key nullable. I do this, but it still does an inner join.

Here is the Menu table that is trying to join to Organizations. Note that MenuId is actually the same value as the OrgId in the Organizations table.

public partial class Menus
{
    [ForeignKey(nameof(Organizations))] 
    public int? MenuId { get; set; } //This is what I made nullable, but it still does an inner join
    public string MenuXml { get; set; }

    public Organizations Organizations { get; set; }
}

For reference, here is a simplified version of my Organizations model:

public partial class Organizations
{
    [Key]
    public int? OrgId { get; set; } //Also made this nullable, but I don't think I have to

    [Required]
    [MaxLength(20)]
    public string Code { get; set; }

    [Required]
    [MaxLength(70)]
    public string Name { get; set; }
}

This is my .Include statement that I want to be a left join, but it is doing inner join. There are MenuId records which do not have a corresponding OrgId in the Organization table:

var listForView = menus.AsNoTracking().Include(menus => menus.Organizations);

Sounds simple. Other examples on Google and SO say that it will be a left join if I make the foreign key a nullable type, but it isn't changing the query for me.

Answer 1

Your code runs very well with me. The generated sql statement is leftjoin instead of innerjoin. I wonder if you have a problem with the configuration, here is my code: Menus class:

public partial class Menus
    {

        [ForeignKey(nameof(Organizations))]
        public int? MenuId { get; set; } 
        public string MenuXml { get; set; }

        public Organizations Organizations { get; set; }

    }

Organizations class:

public partial class Organizations
{
    [Key]
    public int OrgId { get; set; }

    [Required]
    [MaxLength(20)]
    public string Code { get; set; }

    [Required]
    [MaxLength(70)]
    public string Name { get; set; }
}

Your context:

modelBuilder.Entity<Menus>().HasNoKey();

and

 public DbSet<Menus> Menus { get; set; }
 public DbSet<Organizations> Organizations { get; set; }

Linq:

var listForView =context.Menus.AsNoTracking().Include(menus => menus.Organizations).ToList();

Result display: enter image description here