EF Core中Include()未生成LEFT JOIN问题排查及解决

在开发中需要获取多个导航属性数据用于前端展示，使用.Include()进行关联查询。已知当导航属性可为空、数据库列允许为null时，EF Core应该生成LEFT JOIN，但实际查询仍使用INNER JOIN，尽管对应的外键列都是可空类型。

查询代码 #

var listOfProductsOnSets = await productWithSetsRepository
            .FindQueryable(asoc => asoc.IdSet == idSet)
            .Include(c => c.AsCuloare)
                .ThenInclude(cc => cc!.CodCuloare)
            .Include(d => d.AsDimensiune)
            .AsSplitQuery()
            .ToListAsync();

实体类定义 #

public class AsociereSeturi
{
    // Attributes
    [Key]
    [DatabaseGenerated(DatabaseGeneratedOption.Identity)]
    public int IdAsociereSet { get; init; }
    
    // Foreign Keys
    public int IdProdus { get; init; }
    public Produse Produs { get; init; } = null!;
    
    public int IdSet { get; init; }
    public Seturi Set { get; init; } = null!;
    
    public int? IdCuloare { get; init; }
    public Culori? AsCuloare { get; init; } 
    public int? IdDimensiune { get; init; }
    public Dimensiuni? AsDimensiune { get; init; } 
    
    public ICollection<ProduseCuComenzi> CombinatieSetPeComanda { get; } 
}

生成的SQL日志 #

SELECT a.id_asociere_set, a.id_culoare, a.id_dimensiune, a.id_produs, a.id_set, 
       c.id_culoare, c.id_cod_culoare, c.nume_culoare, 
       d.id_dimensiune, d.latime, d.lungime, d.PerdeaEstePereche, d.recomandare_pat
FROM asociere_seturi AS a
INNER JOIN culori AS c ON a.id_culoare = c.id_culoare
INNER JOIN dimensiuni AS d ON a.id_dimensiune = d.id_dimensiune
WHERE a.id_set = @__idSet_0;

排查结果 #

问题根源在于AsociereSeturi的OnModelCreating配置中，给IdDimensiune添加了.IsRequired()约束。即使实体类中IdDimensiune定义为可空的int?，这个配置会强制EF Core将该外键视为必填，从而生成INNER JOIN。移除.IsRequired()配置后，查询正常生成LEFT JOIN。

内容的提问来源于stack exchange，提问作者Misu Stefan