Navigation property filtering performance

## Ask a question

Hey, I've encountered a performance issue where a LINQ query involving a subquery does not efficiently translate into SQL, which is leading to a large table scan. The issue arises when trying to filter a navigation property's collection within a query.

### Include your code

[Source repo](https://github.com/blundell89/EF.NavigationPropertiesTest)

When executing the following:

```csharp
var policies = await context.Policies
.Where(x => x.PolicyId == createdPolicyId)
.Select(pol => new
{
Policy = pol,
CreatedEvent = pol.Events
.OrderByDescending(x => x.UpdatedAt)
.FirstOrDefault(x => x.Event == "Created")
})
.ToListAsync();
```

The following SQL is generated:

```sql
SELECT [p].[PolicyId], [t0].[PolicyId], [t0].[UpdatedAt], [t0].[Event]
FROM [Policies] AS [p]
LEFT JOIN (
SELECT [t].[PolicyId], [t].[UpdatedAt], [t].[Event]
FROM (
SELECT [p0].[PolicyId], [p0].[UpdatedAt], [p0].[Event], ROW_NUMBER() OVER(PARTITION BY [p0].[PolicyId] ORDER BY [p0].[UpdatedAt] DESC) AS [row]
FROM [PolicyEvents] AS [p0]
WHERE [p0].[Event] = N'Created'
) AS [t]
WHERE [t].[row] <= 1
) AS [t0] ON [p].[PolicyId] = [t0].[PolicyId]
WHERE [p].[PolicyId] = @__createdPolicyId_0
```

The subquery used in the left join doesn't filter on the `PolicyId`, which is causing table scans on the `PolicyEvents` table in this scenario.

### Include provider and version information

EF Core version: 7.0.14
Database provider: Microsoft.EntityFrameworkCore.SqlServer
Target framework: NET 7.0
Operating system: Windows 11
IDE: Jetbrains Rider 2023.2.3