The problem i an encountering is this: I need to get multiple navigation properties for the data to display on my frontend. I’m using .Include() to get those.

By default, Include does an inner join, I get that. I also read that if the navigation properties are nullable and the column type in the database can be null, it will do a LEFT JOIN. My query still uses INNER JOIN, even though both my columns which are FK are nullable.

This is my query

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

This is my class :

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

And these are my logs:

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;

EDIT
Configuration
This is the Dimensiuni table

modelBuilder.Entity<Dimensiuni>(entity =>
        {
         
            entity.ToTable("dimensiuni");
            entity.HasKey(e => e.IdDimensiune);

            entity.Property(e => e.IdDimensiune)
                .HasColumnType("integer")
                .HasColumnName("id_dimensiune")
                .HasAnnotation("MySql:ValueGenerationStrategy", MySqlValueGenerationStrategy.IdentityColumn);

            entity.Property(e => e.Lungime)
                .HasColumnType("varchar")
                .HasColumnName("lungime")
                .HasMaxLength(4)
                .IsRequired();
            
            entity.Property(e => e.RecomandarePat)
                .HasColumnType("varchar")
                .HasColumnName("recomandare_pat")
                .HasMaxLength(15);
            
            entity.Property(e => e.Latime)
                .HasColumnType("varchar")
                .HasColumnName("latime")
                .HasMaxLength(4)
                .IsRequired();

            entity.HasMany(e => e.DAsociereSeturi)
                .WithOne(asoc => asoc.AsDimensiune)
                .HasForeignKey(asoc => asoc.IdDimensiune)
                .HasConstraintName("FK_Dimensiune_AsociereSeturi");

            entity.HasMany(e => e.DProduseCuComenzi)
                .WithOne(pc => pc.PcDimensiune)
                .HasForeignKey(pc => pc.IdDimensiune)
                .HasConstraintName("FK_Dimensiune_ProduseComenzi");


        });

This is the Culori table

modelBuilder.Entity<Culori>(entity =>
        {
            entity.ToTable("culori");
            entity.HasKey(e => e.IdCuloare);

            entity.Property(e => e.IdCuloare)
                .HasColumnType("integer")
                .HasColumnName("id_culoare")
                .HasAnnotation("MySql:ValueGenerationStrategy", MySqlValueGenerationStrategy.IdentityColumn);

            entity.Property(e => e.NumeCuloare)
                .HasColumnName("nume_culoare")
                .HasColumnType("varchar")
                .HasMaxLength(20)
                .IsRequired();
            
            entity.Property(e => e.IdCodCuloare)
                .HasColumnName("id_cod_culoare")
                .HasColumnType("integer")
                .IsRequired();

            entity.HasMany(e => e.CAsociereSeturi)
                .WithOne(asoc => asoc.AsCuloare)
                .HasForeignKey(asoc => asoc.IdCuloare)
                .HasConstraintName("FK_Culoare_AsociereSeturi");
            
            entity.HasMany(e => e.CProduseCuComenzi)
                .WithOne(pc => pc.PcCuloare)
                .HasForeignKey(pc => pc.IdCuloare)
                .HasConstraintName("FK_Culoare_ProduseComenzoi")
                .IsRequired();


        });

The relevant parts

// Culori with AsociereSeturi
entity.HasMany(e => e.CAsociereSeturi)
                .WithOne(asoc => asoc.AsCuloare)
                .HasForeignKey(asoc => asoc.IdCuloare)
                .HasConstraintName("FK_Culoare_AsociereSeturi");
// Dimensiuni with AsociereSeturi
  entity.HasMany(e => e.DAsociereSeturi)
                .WithOne(asoc => asoc.AsDimensiune)
                .HasForeignKey(asoc => asoc.IdDimensiune)
                .HasConstraintName("FK_Dimensiune_AsociereSeturi");

EDIT_2
THE PROBLEM!! I FOUND IT!
The problem was that in my AsociereSeturi OnModelCreating i put isRequired.

 entity.Property(e => e.IdSet)
                .HasColumnType("integer")
                .HasColumnName("id_set")
                .IsRequired();
            
            entity.Property(e => e.IdDimensiune)
                .HasColumnType("integer")
                .HasColumnName("id_dimensiune")
                .IsRequired();

REMOVED .IsRequired() and it worked like a charm!

Thank you Steve!