EF Core Inner join instead Left

Question

My query with Include generates sql with Inner join instead Left. My FK is nullable, so I can't explain such behavior. With nullable FK I am expect normal Left join.

Have I missed something?

Linq query:

     var projectEntity = await _context.Projects
            // few more includes were omitted
            .Include(p => p.Invoice)
            .FirstOrDefaultAsync(c => c.ProjectId == id);

Classes:

[Table("InvoicedHistory")]
public class InvoiceHistory
{
    [Key]
    [Column("InvoicedHistory_ID")]
    public int InvoicedHistoryId { get; set; }
    // few properties omitted
    [Column("Project_ID")]
    public int? ProjectId { get; set; }
}

public class Project
{
    public int ProjectId { get; set; }
    
    // few properties were omitted
    
    [ForeignKey(nameof(InvoiceHistory.ProjectId))]
    public virtual InvoiceHistory Invoice { get; set; }
}

Project class also use fluent api:

        modelBuilder.Entity<Project>(entity =>
        {
            entity.ToTable("Projects");

            entity.HasKey(e => e.ProjectId)
                .HasName("PK_Project_Project_ID_Project");

           // few statements were omitted

        });

Sql which was generated: (Was hard to clean up this query. It contains several joins to include data for properties I have omitted)

    SELECT [t].[Project_ID], [t].[Project_Client], [t].[Project_IrsDate], [t].[Project_Name], [t].[Client_ID], [t].[Client_Name], [t].[InvoicedHistory_ID], [t].[DateSubmitted], [t].[Project_ID0], [t0].[Debitor_ID], [t0].[Project_ID], [t0].[Debitor_ID0], [t0].[Address_Number], [t0].[Alias], [t0].[Alpha_Name], [t0].[Co], [t0].[Country_ID], [t0].[Currency_ID], [t0].[Havi_YesOrNo]
  FROM (
      SELECT TOP(1) [p].[Project_ID], [p].[Project_Client], [p].[Project_IrsDate], [p].[Project_Name], [c].[Client_ID], [c].[Client_Name], [i].[InvoicedHistory_ID], [i].[DateSubmitted], [i].[Project_ID] AS [Project_ID0]
      FROM [Projects] AS [p]
      INNER JOIN [Clients] AS [c] ON [p].[Project_Client] = [c].[Client_ID]
      INNER **<<<<<<<<(expect LEFT)** JOIN [InvoicedHistory] AS [i] ON [p].[Project_ID] = [i].[InvoicedHistory_ID]
      WHERE [p].[Project_ID] = 19922
  ) AS [t]
  LEFT JOIN (
      SELECT [p0].[Debitor_ID], [p0].[Project_ID], [d].[Debitor_ID] AS [Debitor_ID0], [d].[Address_Number], [d].[Alias], [d].[Alpha_Name], [d].[Co], [d].[Country_ID], [d].[Currency_ID], [d].[Havi_YesOrNo]
      FROM [ProjectDebitors] AS [p0]
      INNER JOIN [Debitors] AS [d] ON [p0].[Debitor_ID] = [d].[Debitor_ID]
  ) AS [t0] ON [t].[Project_ID] = [t0].[Project_ID]
  ORDER BY [t].[Project_ID], [t].[Client_ID], [t].[InvoicedHistory_ID], [t0].[Debitor_ID], [t0].[Project_ID], [t0].[Debitor_ID0]

Look at this line -

   INNER <<<<<<<<(expect LEFT)<<<<<< JOIN [InvoicedHistory] AS [i] ON [p].[Project_ID] = [i].[InvoicedHistory_ID]

Inner join makes my query return nothing, because I have no invoice info. If I manually replace it with Left join, sql query will return me all necessary data.

Answer 1

I think you can use Fluent API to get your desired result:

modelBuilder.Entity<Project>()
  .HasOne(p => p.Invoice)
  .WithOne()
  .HasForeignKey(ih => ih.ProjectId);

This should change it to a left join because we didn't specify .IsRequired()

As mentioned in the following SO Answer - Equivalent for .HasOptional in Entity Framework Core 1 (EF7)

You will not find an equivalent method in EF 7. By convention, a property whose CLR type can contain null will be configured as optional. So what decide if the relationship is optional or not is if the FK property is nullable or not respectively.

and

In case of your FK property is value type like int, you should declare it as nullable (int?).

Now most likely your problem with annotations is that the following is not doing what you think it is:

[ForeignKey(nameof(InvoiceHistory.ProjectId))]

//Does not resolve to:
[ForeignKey("InvoiceHistory.ProjectId")]

//Does resolve to:
[ForeignKey("ProjectId")]

Now even if that is what you are looking for, the order of operations for the ForeignKey detection is to check the parent type then the property type.

public class InvoiceHistory
{
    public int? ProjectId { get; set; }
}

public class Project
{
    public int ProjectId { get; set; }
    
    // this is pointing to Project.ProjectId
    // and Project.ProjectId is not nullable
    // so the join becomes an inner join
    // and really only works because they both have the same name
    [ForeignKey(nameof(InvoiceHistory.ProjectId))]
    public virtual InvoiceHistory Invoice { get; set; }
}

If you wanted this to work as pointing to the Property Type, you need to rename the InvoiceHistory name:

public class InvoiceHistory
{
    public int? ProjectFk { get; set; }
}

public class Project
{
    public int ProjectId { get; set; }
    
    // this is pointing to InvoiceHistory.ProjectFk 
    // because there is no Project.ProjectFk 
    [ForeignKey(nameof(InvoiceHistory.ProjectFk))]
    public virtual InvoiceHistory Invoice { get; set; }
}

EntityFramework Data Annotations

If you wanted to see it create bad SQL you could do this:

public class InvoiceHistory
{
    public int? ProjectId { get; set; }
}

public class Project
{
    public int ProjectFk { get; set; }
    
    [ForeignKey("ProjectFk")]
    public virtual InvoiceHistory Invoice { get; set; }
}

EF will then create:

 INNER JOIN [InvoicedHistory] AS [i] ON [p].[Project_ID] = [i].[ProjectFk]

And will cause a SqlException with the message something like Invalid column name.