Redundant inner SELECTs when loading Many-To-Many association

## Description

In our project we use explicit lazy loading of all associations. When loading entities via a many-to-many association (something like **Airports** - **Airlines** where an **Airport** can host many **Airlines** and an **Airline** can fly from many **Airports**) I would expect SQL of the form:

```
SELECT [a].[ID], [a].[Name], [a0].[AirlineID], [a0].[AirportID], [a1].[ID], [a1].[Name]
FROM [Airport] AS [a]
INNER JOIN [Airport_Airline] AS [a0] ON [a0].[AirportID] = [a].[ID]
LEFT JOIN [Airline] AS [a1] ON [a1].[ID] = [a0].[AirlineID]
WHERE [a].[ID] = 1
ORDER BY [a0].[AirportID], [a0].[AirlineID]
```
but instead I get:
```
SELECT [t].[ID], [t].[Name], [a].[ID], [t].[AirlineID], [t].[AirportID], [t0].[AirlineID], [t0].[AirportID], [t0].[ID], [t0].[Name]
FROM [Airport] AS [a]
INNER JOIN (
SELECT [a1].[ID], [a1].[Name], [a0].[AirlineID], [a0].[AirportID]
FROM [Airport_Airline] AS [a0]
INNER JOIN [Airline] AS [a1] ON [a0].[AirlineID] = [a1].[ID]
) AS [t] ON [a].[ID] = [t].[AirportID]
LEFT JOIN (
SELECT [a2].[AirlineID], [a2].[AirportID], [a3].[ID], [a3].[Name]
FROM [Airport_Airline] AS [a2]
INNER JOIN [Airport] AS [a3] ON [a2].[AirportID] = [a3].[ID]
WHERE [a3].[ID] = 1
) AS [t0] ON [t].[ID] = [t0].[AirlineID]
WHERE [a].[ID] = 1
ORDER BY [a].[ID], [t].[AirlineID], [t].[AirportID], [t].[ID], [t0].[AirlineID], [t0].[AirportID]
```
This is overly complex and takes twice as long as the simple version.

### Full Repro Code

```C#
public class Airport {
public int ID { get; set; }
public string Name { get; set; }
public IEnumerable Airlines { get; set; }
}

public class Airline {
public int ID { get; set; }
public string Name { get; set; }
public IEnumerable Airports { get; set; }
}

public class AirportMappings : IEntityTypeConfiguration {
public void Configure(EntityTypeBuilder entity) {
entity.ToTable("Airport");
entity.HasKey(e => e.ID);
entity.Property(e => e.Name);

entity.HasMany("Airlines")
.WithMany("Airports")
.UsingEntity(
"Airport_Airline",
typeof(Dictionary),
entityThis => entityThis
.HasOne(typeof(Airline))
.WithMany()
.HasForeignKey("AirlineID")
.HasConstraintName("FK_Airport_Airline_AirlineID"),
entityOther => entityOther
.HasOne(typeof(Airport))
.WithMany()
.HasForeignKey("AirportID")
.HasConstraintName("FK_Airport_Airline_AirportID"));
}
}

public class Airline {
public int ID { get; set; }
public string Name { get; set; }
public IEnumerable Airports { get; set; }
}

public class AirlineMappings : IEntityTypeConfiguration {
public void Configure(EntityTypeBuilder entity) {
entity.ToTable("Airline");
entity.HasKey(e => e.ID);
entity.Property(e => e.Name);
}
}

public class TestDbContext : DbContext {
public DbSet Airports { get; set; }
public DbSet Airlines { get; set; }

protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder) {
optionsBuilder.LogTo(Console.WriteLine, LogLevel.Information).UseSqlServer(CONNECTION_STRING);
}

protected override void OnModelCreating(ModelBuilder modelBuilder) {
modelBuilder.ApplyConfiguration(new AirportMappings());
modelBuilder.ApplyConfiguration(new AirlineMappings());
}
}
```

Tests

```C#
public class ReproTests {
private TestDbContext context;

[OneTimeSetUp]
public void OneTimeSetUp() {
context = new TestDbContext();
context.Database.EnsureCreated();
SetupTestData();
context.Dispose();
}

[SetUp]
public void Setup() { context = new TestDbContext(); }

[TearDown]
public void TearDown() { context.Dispose(); }

[Test]
public void GetManyFromEnd1_SqlIncludesRedundantSelects() {
var airport = context.Airports.First();
context.Entry(airport).Collection("Airlines").Load();
_ = airport.Airlines.ToList();
}

[Test]
public void GetManyFromEnd2_SqlIncludesRedundantSelects() {
var airline = context.Airlines.First();

// Lazy load the many-to-many association
context.Entry(airline).Collection("Airports").Load();
_ = airline.Airports.ToList();
}

private void SetupTestData() {
var airport = new Airport();
context.Add(airport);

var airline = new Airline();
context.Add(airline);

airport.Airlines = new List { airline };
context.SaveChanges();
}
}
```

### Generated SQL

```
SELECT [t].[ID], [t].[Name], [a].[ID], [t].[AirlineID], [t].[AirportID], [t0].[AirlineID], [t0].[AirportID], [t0].[ID], [t0].[Name]
FROM [Airport] AS [a]
INNER JOIN (
SELECT [a1].[ID], [a1].[Name], [a0].[AirlineID], [a0].[AirportID]
FROM [Airport_Airline] AS [a0]
INNER JOIN [Airline] AS [a1] ON [a0].[AirlineID] = [a1].[ID]
) AS [t] ON [a].[ID] = [t].[AirportID]
LEFT JOIN (
SELECT [a2].[AirlineID], [a2].[AirportID], [a3].[ID], [a3].[Name]
FROM [Airport_Airline] AS [a2]
INNER JOIN [Airport] AS [a3] ON [a2].[AirportID] = [a3].[ID]
WHERE [a3].[ID] = @__p_0
) AS [t0] ON [t].[ID] = [t0].[AirlineID]
WHERE [a].[ID] = @__p_0
ORDER BY [a].[ID], [t].[AirlineID], [t].[AirportID], [t].[ID], [t0].[AirlineID], [t0].[AirportID]
```
Mirror image sql is generated when queried from the other end.

This results in a query plan which looks like:


Whereas this SQL:
```
SELECT [a].[ID], [a].[Name], [a0].[AirlineID], [a0].[AirportID], [a1].[ID], [a1].[Name]
FROM [Airport] AS [a]
INNER JOIN [Airport_Airline] AS [a0] ON [a0].[AirportID] = [a].[ID]
LEFT JOIN [Airline] AS [a1] ON [a1].[ID] = [a0].[AirlineID]
WHERE [a].[ID] = 1
ORDER BY [a0].[AirportID], [a0].[AirlineID]
```
results in this query plan:

### Repro Project
[RedundantJoins-CrossLinks.zip](https://github.com/dotnet/efcore/files/13599121/RedundantJoins-CrossLinks.zip)

### Include provider and version information

EF Core version: 7.0.5
Database provider: Microsoft.EntityFrameworkCore.SqlServer
Target framework: .NET 6.0
Operating system: WIndows 11
IDE: Visual Studio 2022 17.7