EF10 Upgrade cause Regression in query times due to linq-translations

### Bug description

After upgrading to EF10 from EF9 we saw some significant regression in query performance and found it to be caused by the way EF10 translate Linq queries to SQL.

```
var query = db.Events
.Select(event => new Response
{
// Adding the below forces EF10 to use APPLY in its translation
// When not present, the below SQL is generated instead, which takes performance
// From approximately 100ms to 11 seconds.
Id = event.Id,

LatestComment = event.Comments
.Where(comment => comment.IsVisible)
.OrderByDescending(comment => comment.CreatedDate)
.Take(1)
.Select(comment => new Comment
{
comment.CreatedDate,
comment.Text
})
.FirstOrDefault()
});
```

The slow SQL translation:
```
SELECT [w].[Id], [w2].[CreatedDate], [w2].[Text], [w2].[c]
FROM [Events] AS [w]
LEFT JOIN (
SELECT
[w1].[CreatedDate],
[w1].[Text],
[w1].[c],
[w1].[Id]
FROM (
SELECT
[w0].[CreatedDate],
[w0].[Text],
1 AS [c],
[w0].[Id],
ROW_NUMBER() OVER (
PARTITION BY [w0].[Id]
ORDER BY [w0].[CreatedDate] DESC
) AS [row]
FROM [Events] AS [w0]
WHERE [w0].[HasComment] = CAST(1 AS bit)
) AS [w1]
WHERE [w1].[row] <= 1
) AS [w2]
ON [w].[Id] = [w2].[Id]
```

The "forced" SQL translation (Adding the event.Id to the selector):
```
SELECT [w].[Id],
[w1].[CorrelationId],
[w1].[CreatedDate],
[w1].[Text],
[w1].[c]
FROM [Events] AS [w]
OUTER APPLY (
SELECT TOP(1)
[w].[Id] AS [CorrelationId],
[w0].[CreatedDate],
[w0].[Text],
1 AS [c]
FROM [Events] AS [w0]
WHERE [w0].[Id] = [w].[Id]
AND [w0].[HasComment] = CAST(1 AS bit)
ORDER BY [w0].[CreatedDate] DESC
) AS [w1]
```

I am currently not sure if I have been doing it wrong and need to adapt to another way of projecting my selections to get an ideal translation, or if this is a bug in EF10, as EF9 generates a very efficient SQL query.

### Your code

```csharp
var query = db.Events
.Select(event => new Response
{
// Adding the below forces EF10 to use APPLY in its translation
// When not present, the below SQL is generated instead, which takes performance
// From approximately 100ms to 11 seconds.
Id = event.Id,

LatestComment = event.Comments
.Where(comment => comment.IsVisible)
.OrderByDescending(comment => comment.CreatedDate)
.Take(1)
.Select(comment => new Comment
{
comment.CreatedDate,
comment.Text
})
.FirstOrDefault()
});
```

### Stack traces

```text

```

### Verbose output

```text

```

### EF Core version

10.0.11

### Database provider

_No response_

### Target framework

_No response_

### Operating system

_No response_

### IDE

_No response_

Start by reproducing the supplied LINQ projection with EF Core 10.0.11 and compare its generated SQL and query time with EF9, recording the database provider and target framework that are currently missing. Trace the query translation entry point responsible for the window-function versus OUTER APPLY shapes; done means the regression is explained and covered by a focused test or a confirmed issue with a reliable reproduction.

Written by the indexing model from the issue text.