Inefficient T-SQL when using navigation properties instead of foreign keys

[JohnyL]

I have three tables which are connected the following way:

ActConfig entity:

class ActConfig
{
    public byte Id { get; set; } //Primary key
    public bool IsCourt { get; set; }
}

class ActConfigConfiguration : IEntityTypeConfiguration<ActConfig>
{
    public void Configure(EntityTypeBuilder<ActConfig> builder)
    {
        builder.ToTable("act_config", schema: "dic");
        builder.HasKey(p => p.Id);
        builder.Property(p => p.Id)
            .HasColumnName("id")
            .HasColumnType("tinyint");
        builder.Property(p => p.IsCourt)
            .HasColumnName("is_court")
            .HasColumnType("bit");
    }
}

WorkTypeCode entity:

class WorkTypeCode
{
    public byte Id { get; set; } //Primary key
}

class WorkTypeCodeConfiguration : IEntityTypeConfiguration<WorkTypeCode>
{
    public void Configure(EntityTypeBuilder<WorkTypeCode> builder)
    {
        builder.ToTable("work_type_code", schema: "dic");
        builder.HasKey(p => p.Id);
        builder.Property(p => p.Id)
            .HasColumnName("id")
            .HasColumnType("tinyint");
    }
}

WorkerShare entity:

class WorkerShare
{
    // Primary key
    public byte ActConfigId { get; set; }
    public byte WorkTypeCodeId { get; set; }
        
    public byte Share { get; set; }

    // Navigation properties
    public ActConfig ActConfig { get; set; }
    public WorkTypeCode WorkTypeCode { get; set; }
}

class WorkerShareConfiguration : IEntityTypeConfiguration<WorkerShare>
{
    public void Configure(EntityTypeBuilder<WorkerShare> builder)
    {
        builder.ToTable("worker_share", schema: "dic");
        builder.HasKey(p => new { p.ActConfigId, p.WorkTypeCodeId });
        builder.Property(p => p.ActConfigId)
            .HasColumnName("act_config_id")
            .HasColumnType("tinyint");
        builder.Property(p => p.WorkTypeCodeId)
            .HasColumnName("work_type_code_id")
            .HasColumnType("tinyint");
        builder //ActConfig (one) -> WorkerShare (many)
            .HasOne(p => p.ActConfig)
            .WithMany()
            .HasForeignKey(p => p.ActConfigId);
        builder //WorkTypeCode (one) -> WorkerShare (many)
            .HasOne(p => p.WorkTypeCode)
            .WithMany()
            .HasForeignKey(p => p.WorkTypeCodeId);
    }
}

Then I want to fetch all data from WorkerShare and order them by the primary keys which, at the same, are foreign keys. And here's the thing - I get completely different T-SQL when I use:
• Navigation properties (ActConfig and WorkTypeCode)
• Foreign keys [primary keys] (ActConfigId and WorkTypeCodeId)

C# queries:

// Query 1: Using navigation properties:
var shares = db.WorkerShares.OrderBy(p => p.ActConfig).ThenBy(p => p.WorkTypeCode);
// Query 2: Using foreign keys:
var shares = db.WorkerShares.OrderBy(p => p.ActConfigId).ThenBy(p => p.WorkTypeCodeId);

The corresponding T-SQL and execution plans:

As you see, using navigation properties generates inefficient T-SQL with unnecessary joins. The query orders the result by primary keys in referenced tables (ActConfig and WorkTypeCode), but at the same time these keys exist in referencing table (WorkerShare). There's no sense in using joins.

Further technical details

EF Core version: 3.1
Database provider: Microsoft.EntityFrameworkCore.SqlServer
Target framework: .NET Core 3.1
Operating system: Windows 10 Home x64, OS build 18363.535
IDE: Visual Studio 2019 16.5.0 Preview 1.0

[ajcvickers]

@smitpatel to find the duplicate.

[smitpatel]

Duplicate of #15826