Possible optimization of split querys with window functions in included entities

Some combinations of `AsSplitQuery()` and `FirstOrDefault()` with `Take()` in `Include()` produce querys that execute slowly on SQLite and Postgres (I didn't test others). I'm unsure if EF Core does the kind of optimization I think of in general, but it seems easy to handcraft SQL that is semantically equalivalent and executes much faster.

### Repro

```csharp
using Bogus;
using Microsoft.Data.Sqlite;
using Microsoft.EntityFrameworkCore;

const string ConnectionString = @"Data Source=InMemorySample;Mode=Memory;Cache=Shared";
// const string NpgsqlConnectionString = @"Host=localhost;Database=splitqueryperformance;Username=npgsql_tests;Password=npgsql_tests;Include Error Detail=True";

await using var keepOpenCon = new SqliteConnection(ConnectionString);
await keepOpenCon.OpenAsync();

await using var ctx = new ReproContext(ConnectionString);
await ctx.Database.EnsureDeletedAsync();
await ctx.Database.EnsureCreatedAsync();

Console.WriteLine(ctx.Database.GenerateCreateScript());
Console.WriteLine("----");

Randomizer.Seed = new Random(8675309);

Faker blogFaker = new();
blogFaker.RuleFor(b => b.Content, f => f.Rant.Review());

Faker reviewerFaker = new();
reviewerFaker.RuleFor(r => r.Name, f => f.Name.FullName());

var blogs = blogFaker.GenerateBetween(10_000, 15_000);
var reviewers = reviewerFaker.GenerateBetween(30_000, 45_000);
var faker = new Faker();

foreach (var blog in blogs)
{
var cnt = faker.Random.Int(0, 20);
blog.Reviewers = [.. faker.PickRandom(reviewers, cnt)];
}

ctx.Blogs.AddRange(blogs);
ctx.Reviewers.AddRange(reviewers);
await ctx.SaveChangesAsync();

Console.WriteLine("-- Slow FirstOrDefault --");
var toSelect = 1824; //faker.PickRandom(blogs).Id;
var q = ctx.Blogs
.Include(b => b.Reviewers.OrderBy(r => r.Name).ThenBy(r => r.Id).Take(5))
.Where(b => b.Id == toSelect)
.AsSplitQuery();

Console.WriteLine();
Console.WriteLine("FirstOrDefault:");
await q.FirstOrDefaultAsync();

Console.WriteLine();
Console.WriteLine("ToList:");
await q.ToListAsync();

// naively reduce the chance ToListAsync was just able to re-use something cached
Console.WriteLine();
Console.WriteLine("FirstOrDefault 2nd:");
await q.FirstOrDefaultAsync();

Console.WriteLine("-- Fast FirstOrDefault --");
var toSelect2 = toSelect + 1000; //faker.PickRandom(blogs).Id;
var q2 = ctx.Blogs
.Include(b => b.Reviewers.OrderBy(r => r.Name).ThenBy(r => r.Id)) // no Take(5) here
.Where(b => b.Id == toSelect2)
.AsSplitQuery();

Console.WriteLine();
Console.WriteLine("FirstOrDefault:");
await q2.FirstOrDefaultAsync();

Console.WriteLine();
Console.WriteLine("ToList:");
await q2.ToListAsync();

// naively reduce the chance ToListAsync was just able to re-use something cached
Console.WriteLine();
Console.WriteLine("FirstOrDefault 2nd:");
await q2.FirstOrDefaultAsync();

// This translates to a more complex query that should be sematically equivalent to the slower q query.
// On my machine it executes ~3x faster with SQLite and a bit more than 10x faster on Postgres.
Console.WriteLine("-- Workaround Fast FirstOrDefault --");
var toSelect3 = toSelect2 + 1000; //faker.PickRandom(blogs).Id;
var q3 = ctx.Blogs
.Include(b => b.Reviewers.Where(r => r.BlogReviewers.Any(x => x.BlogId == toSelect3)).OrderBy(r => r.Name).ThenBy(r => r.Id).Take(5))
.Where(b => b.Id == toSelect3)
.AsSplitQuery();

Console.WriteLine();
Console.WriteLine("FirstOrDefault:");
await q3.FirstOrDefaultAsync();

Console.WriteLine();
Console.WriteLine("ToList:");
await q3.ToListAsync();

// naively reduce the chance ToListAsync was just able to re-use something cached
Console.WriteLine();
Console.WriteLine("FirstOrDefault 2nd:");
await q3.FirstOrDefaultAsync();

Console.WriteLine("-- Raw Sql --");
var toSelect4 = toSelect3 + 1000; //faker.PickRandom(blogs).Id;

await ctx.Database.ExecuteSqlAsync(@$"SELECT ""s0"".""BlogId"", ""s0"".""ReviewerId"", ""s0"".""Id"", ""s0"".""Name"", ""b1"".""Id""
FROM (
SELECT ""b"".""Id""
FROM ""Blogs"" AS ""b""
WHERE ""b"".""Id"" = {toSelect4}
LIMIT 1
) AS ""b1""
INNER JOIN (
SELECT ""s"".""BlogId"", ""s"".""ReviewerId"", ""s"".""Id"", ""s"".""Name""
FROM (
SELECT ""b0"".""BlogId"", ""b0"".""ReviewerId"", ""r"".""Id"", ""r"".""Name"", ROW_NUMBER() OVER(PARTITION BY ""b0"".""BlogId"" ORDER BY ""r"".""Name"", ""r"".""Id"") AS ""row""
FROM ""BlogReviewer"" AS ""b0""
INNER JOIN ""Reviewers"" AS ""r"" ON ""b0"".""ReviewerId"" = ""r"".""Id""
) AS ""s""
WHERE ""s"".""row"" <= 5 and ""s"".""BlogId"" = {toSelect4}
) AS ""s0"" ON ""b1"".""Id"" = ""s0"".""BlogId""
ORDER BY ""b1"".""Id"", ""s0"".""BlogId"", ""s0"".""Name"", ""s0"".""Id""
");

public class ReproContext(string ConnectionString) : DbContext
{
public DbSet Blogs { get; set; }

public DbSet Reviewers { get; set; }

protected override void OnModelCreating(ModelBuilder modelBuilder)
{
modelBuilder.Entity()
.HasMany(b => b.Reviewers)
.WithMany(r => r.Blogs)
.UsingEntity();
}

protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
=> optionsBuilder.UseSqlite(ConnectionString).LogTo(Console.WriteLine, Microsoft.Extensions.Logging.LogLevel.Information, Microsoft.EntityFrameworkCore.Diagnostics.DbContextLoggerOptions.None);
//=> optionsBuilder.UseNpgsql(ConnectionString).LogTo(Console.WriteLine, Microsoft.Extensions.Logging.LogLevel.Information, Microsoft.EntityFrameworkCore.Diagnostics.DbContextLoggerOptions.None);
}

public class Blog
{
public int Id { get; set; }
public required string Content { get; set; }
public List Reviewers { get; set; } = [];
public List BlogReviewers { get; set; } = [];
}

public class Reviewer
{
public int Id { get; set; }
public required string Name { get; set; }
public List Blogs { get; set; } = [];
public List BlogReviewers { get; set; } = [];
}

public class BlogReviewer
{
public int BlogId { get; set; }
public int ReviewerId { get; set; }
}
```

```csproj

Exe
net8.0
enable
enable

```

### Output with SQLite
```log
-- Slow FirstOrDefault --

FirstOrDefault:
Executed DbCommand (0ms) [Parameters=[@__toSelect_0='?' (DbType = Int32)], CommandType='Text', CommandTimeout='30']
SELECT "b"."Id", "b"."Content"
FROM "Blogs" AS "b"
WHERE "b"."Id" = @__toSelect_0
ORDER BY "b"."Id"
LIMIT 1
Executed DbCommand (333ms) [Parameters=[@__toSelect_0='?' (DbType = Int32)], CommandType='Text', CommandTimeout='30']
SELECT "s0"."BlogId", "s0"."ReviewerId", "s0"."Id", "s0"."Name", "b1"."Id"
FROM (
SELECT "b"."Id"
FROM "Blogs" AS "b"
WHERE "b"."Id" = @__toSelect_0
LIMIT 1
) AS "b1"
INNER JOIN (
SELECT "s"."BlogId", "s"."ReviewerId", "s"."Id", "s"."Name"
FROM (
SELECT "b0"."BlogId", "b0"."ReviewerId", "r"."Id", "r"."Name", ROW_NUMBER() OVER(PARTITION BY "b0"."BlogId" ORDER BY "r"."Name", "r"."Id") AS "row"
FROM "BlogReviewer" AS "b0"
INNER JOIN "Reviewers" AS "r" ON "b0"."ReviewerId" = "r"."Id"
) AS "s"
WHERE "s"."row" <= 5
) AS "s0" ON "b1"."Id" = "s0"."BlogId"
ORDER BY "b1"."Id", "s0"."BlogId", "s0"."Name", "s0"."Id"

ToList:
Executed DbCommand (0ms) [Parameters=[@__toSelect_0='?' (DbType = Int32)], CommandType='Text', CommandTimeout='30']
SELECT "b"."Id", "b"."Content"
FROM "Blogs" AS "b"
WHERE "b"."Id" = @__toSelect_0
ORDER BY "b"."Id"
Executed DbCommand (0ms) [Parameters=[@__toSelect_0='?' (DbType = Int32)], CommandType='Text', CommandTimeout='30']
SELECT "s0"."BlogId", "s0"."ReviewerId", "s0"."Id", "s0"."Name", "b"."Id"
FROM "Blogs" AS "b"
INNER JOIN (
SELECT "s"."BlogId", "s"."ReviewerId", "s"."Id", "s"."Name"
FROM (
SELECT "b0"."BlogId", "b0"."ReviewerId", "r"."Id", "r"."Name", ROW_NUMBER() OVER(PARTITION BY "b0"."BlogId" ORDER BY "r"."Name", "r"."Id") AS "row"
FROM "BlogReviewer" AS "b0"
INNER JOIN "Reviewers" AS "r" ON "b0"."ReviewerId" = "r"."Id"
) AS "s"
WHERE "s"."row" <= 5
) AS "s0" ON "b"."Id" = "s0"."BlogId"
WHERE "b"."Id" = @__toSelect_0
ORDER BY "b"."Id", "s0"."BlogId", "s0"."Name", "s0"."Id"

FirstOrDefault 2nd:
Executed DbCommand (0ms) [Parameters=[@__toSelect_0='?' (DbType = Int32)], CommandType='Text', CommandTimeout='30']
SELECT "b"."Id", "b"."Content"
FROM "Blogs" AS "b"
WHERE "b"."Id" = @__toSelect_0
ORDER BY "b"."Id"
LIMIT 1
Executed DbCommand (301ms) [Parameters=[@__toSelect_0='?' (DbType = Int32)], CommandType='Text', CommandTimeout='30']
SELECT "s0"."BlogId", "s0"."ReviewerId", "s0"."Id", "s0"."Name", "b1"."Id"
FROM (
SELECT "b"."Id"
FROM "Blogs" AS "b"
WHERE "b"."Id" = @__toSelect_0
LIMIT 1
) AS "b1"
INNER JOIN (
SELECT "s"."BlogId", "s"."ReviewerId", "s"."Id", "s"."Name"
FROM (
SELECT "b0"."BlogId", "b0"."ReviewerId", "r"."Id", "r"."Name", ROW_NUMBER() OVER(PARTITION BY "b0"."BlogId" ORDER BY "r"."Name", "r"."Id") AS "row"
FROM "BlogReviewer" AS "b0"
INNER JOIN "Reviewers" AS "r" ON "b0"."ReviewerId" = "r"."Id"
) AS "s"
WHERE "s"."row" <= 5
) AS "s0" ON "b1"."Id" = "s0"."BlogId"
ORDER BY "b1"."Id", "s0"."BlogId", "s0"."Name", "s0"."Id"
-- Fast FirstOrDefault --

FirstOrDefault:
Executed DbCommand (0ms) [Parameters=[@__toSelect2_0='?' (DbType = Int32)], CommandType='Text', CommandTimeout='30']
SELECT "b"."Id", "b"."Content"
FROM "Blogs" AS "b"
WHERE "b"."Id" = @__toSelect2_0
ORDER BY "b"."Id"
LIMIT 1
Executed DbCommand (0ms) [Parameters=[@__toSelect2_0='?' (DbType = Int32)], CommandType='Text', CommandTimeout='30']
SELECT "s"."BlogId", "s"."ReviewerId", "s"."Id", "s"."Name", "b1"."Id"
FROM (
SELECT "b"."Id"
FROM "Blogs" AS "b"
WHERE "b"."Id" = @__toSelect2_0
LIMIT 1
) AS "b1"
INNER JOIN (
SELECT "b0"."BlogId", "b0"."ReviewerId", "r"."Id", "r"."Name"
FROM "BlogReviewer" AS "b0"
INNER JOIN "Reviewers" AS "r" ON "b0"."ReviewerId" = "r"."Id"
) AS "s" ON "b1"."Id" = "s"."BlogId"
ORDER BY "b1"."Id", "s"."Name", "s"."Id"

ToList:
Executed DbCommand (0ms) [Parameters=[@__toSelect2_0='?' (DbType = Int32)], CommandType='Text', CommandTimeout='30']
SELECT "b"."Id", "b"."Content"
FROM "Blogs" AS "b"
WHERE "b"."Id" = @__toSelect2_0
ORDER BY "b"."Id"
Executed DbCommand (0ms) [Parameters=[@__toSelect2_0='?' (DbType = Int32)], CommandType='Text', CommandTimeout='30']
SELECT "s"."BlogId", "s"."ReviewerId", "s"."Id", "s"."Name", "b"."Id"
FROM "Blogs" AS "b"
INNER JOIN (
SELECT "b0"."BlogId", "b0"."ReviewerId", "r"."Id", "r"."Name"
FROM "BlogReviewer" AS "b0"
INNER JOIN "Reviewers" AS "r" ON "b0"."ReviewerId" = "r"."Id"
) AS "s" ON "b"."Id" = "s"."BlogId"
WHERE "b"."Id" = @__toSelect2_0
ORDER BY "b"."Id", "s"."Name", "s"."Id"

FirstOrDefault 2nd:
Executed DbCommand (0ms) [Parameters=[@__toSelect2_0='?' (DbType = Int32)], CommandType='Text', CommandTimeout='30']
SELECT "b"."Id", "b"."Content"
FROM "Blogs" AS "b"
WHERE "b"."Id" = @__toSelect2_0
ORDER BY "b"."Id"
LIMIT 1
Executed DbCommand (0ms) [Parameters=[@__toSelect2_0='?' (DbType = Int32)], CommandType='Text', CommandTimeout='30']
SELECT "s"."BlogId", "s"."ReviewerId", "s"."Id", "s"."Name", "b1"."Id"
FROM (
SELECT "b"."Id"
FROM "Blogs" AS "b"
WHERE "b"."Id" = @__toSelect2_0
LIMIT 1
) AS "b1"
INNER JOIN (
SELECT "b0"."BlogId", "b0"."ReviewerId", "r"."Id", "r"."Name"
FROM "BlogReviewer" AS "b0"
INNER JOIN "Reviewers" AS "r" ON "b0"."ReviewerId" = "r"."Id"
) AS "s" ON "b1"."Id" = "s"."BlogId"
ORDER BY "b1"."Id", "s"."Name", "s"."Id"
-- Workaround Fast FirstOrDefault --

FirstOrDefault:
Executed DbCommand (0ms) [Parameters=[@__toSelect3_0='?' (DbType = Int32)], CommandType='Text', CommandTimeout='30']
SELECT "b"."Id", "b"."Content"
FROM "Blogs" AS "b"
WHERE "b"."Id" = @__toSelect3_0
ORDER BY "b"."Id"
LIMIT 1
Executed DbCommand (92ms) [Parameters=[@__toSelect3_0='?' (DbType = Int32)], CommandType='Text', CommandTimeout='30']
SELECT "s0"."BlogId", "s0"."ReviewerId", "s0"."Id", "s0"."Name", "b2"."Id"
FROM (
SELECT "b"."Id"
FROM "Blogs" AS "b"
WHERE "b"."Id" = @__toSelect3_0
LIMIT 1
) AS "b2"
INNER JOIN (
SELECT "s"."BlogId", "s"."ReviewerId", "s"."Id", "s"."Name"
FROM (
SELECT "b0"."BlogId", "b0"."ReviewerId", "r"."Id", "r"."Name", ROW_NUMBER() OVER(PARTITION BY "b0"."BlogId" ORDER BY "r"."Name", "r"."Id") AS "row"
FROM "BlogReviewer" AS "b0"
INNER JOIN "Reviewers" AS "r" ON "b0"."ReviewerId" = "r"."Id"
WHERE EXISTS (
SELECT 1
FROM "BlogReviewer" AS "b1"
WHERE "r"."Id" = "b1"."ReviewerId" AND "b1"."BlogId" = @__toSelect3_0)
) AS "s"
WHERE "s"."row" <= 5
) AS "s0" ON "b2"."Id" = "s0"."BlogId"
ORDER BY "b2"."Id", "s0"."BlogId", "s0"."Name", "s0"."Id"

ToList:
Executed DbCommand (0ms) [Parameters=[@__toSelect3_0='?' (DbType = Int32)], CommandType='Text', CommandTimeout='30']
SELECT "b"."Id", "b"."Content"
FROM "Blogs" AS "b"
WHERE "b"."Id" = @__toSelect3_0
ORDER BY "b"."Id"
Executed DbCommand (0ms) [Parameters=[@__toSelect3_0='?' (DbType = Int32)], CommandType='Text', CommandTimeout='30']
SELECT "s0"."BlogId", "s0"."ReviewerId", "s0"."Id", "s0"."Name", "b"."Id"
FROM "Blogs" AS "b"
INNER JOIN (
SELECT "s"."BlogId", "s"."ReviewerId", "s"."Id", "s"."Name"
FROM (
SELECT "b0"."BlogId", "b0"."ReviewerId", "r"."Id", "r"."Name", ROW_NUMBER() OVER(PARTITION BY "b0"."BlogId" ORDER BY "r"."Name", "r"."Id") AS "row"
FROM "BlogReviewer" AS "b0"
INNER JOIN "Reviewers" AS "r" ON "b0"."ReviewerId" = "r"."Id"
WHERE EXISTS (
SELECT 1
FROM "BlogReviewer" AS "b1"
WHERE "r"."Id" = "b1"."ReviewerId" AND "b1"."BlogId" = @__toSelect3_0)
) AS "s"
WHERE "s"."row" <= 5
) AS "s0" ON "b"."Id" = "s0"."BlogId"
WHERE "b"."Id" = @__toSelect3_0
ORDER BY "b"."Id", "s0"."BlogId", "s0"."Name", "s0"."Id"

FirstOrDefault 2nd:
Executed DbCommand (0ms) [Parameters=[@__toSelect3_0='?' (DbType = Int32)], CommandType='Text', CommandTimeout='30']
SELECT "b"."Id", "b"."Content"
FROM "Blogs" AS "b"
WHERE "b"."Id" = @__toSelect3_0
ORDER BY "b"."Id"
LIMIT 1
Executed DbCommand (90ms) [Parameters=[@__toSelect3_0='?' (DbType = Int32)], CommandType='Text', CommandTimeout='30']
SELECT "s0"."BlogId", "s0"."ReviewerId", "s0"."Id", "s0"."Name", "b2"."Id"
FROM (
SELECT "b"."Id"
FROM "Blogs" AS "b"
WHERE "b"."Id" = @__toSelect3_0
LIMIT 1
) AS "b2"
INNER JOIN (
SELECT "s"."BlogId", "s"."ReviewerId", "s"."Id", "s"."Name"
FROM (
SELECT "b0"."BlogId", "b0"."ReviewerId", "r"."Id", "r"."Name", ROW_NUMBER() OVER(PARTITION BY "b0"."BlogId" ORDER BY "r"."Name", "r"."Id") AS "row"
FROM "BlogReviewer" AS "b0"
INNER JOIN "Reviewers" AS "r" ON "b0"."ReviewerId" = "r"."Id"
WHERE EXISTS (
SELECT 1
FROM "BlogReviewer" AS "b1"
WHERE "r"."Id" = "b1"."ReviewerId" AND "b1"."BlogId" = @__toSelect3_0)
) AS "s"
WHERE "s"."row" <= 5
) AS "s0" ON "b2"."Id" = "s0"."BlogId"
ORDER BY "b2"."Id", "s0"."BlogId", "s0"."Name", "s0"."Id"
-- Raw Sql --
Executed DbCommand (0ms) [Parameters=[@p0='?' (DbType = Int32), @p1='?' (DbType = Int32)], CommandType='Text', CommandTimeout='30']
SELECT "s0"."BlogId", "s0"."ReviewerId", "s0"."Id", "s0"."Name", "b1"."Id"
FROM (
SELECT "b"."Id"
FROM "Blogs" AS "b"
WHERE "b"."Id" = @p0
LIMIT 1
) AS "b1"
INNER JOIN (
SELECT "s"."BlogId", "s"."ReviewerId", "s"."Id", "s"."Name"
FROM (
SELECT "b0"."BlogId", "b0"."ReviewerId", "r"."Id", "r"."Name", ROW_NUMBER() OVER(PARTITION BY "b0"."BlogId" ORDER BY "r"."Name", "r"."Id") AS "row"
FROM "BlogReviewer" AS "b0"
INNER JOIN "Reviewers" AS "r" ON "b0"."ReviewerId" = "r"."Id"
) AS "s"
WHERE "s"."row" <= 5 and "s"."BlogId" = @p1
) AS "s0" ON "b1"."Id" = "s0"."BlogId"
ORDER BY "b1"."Id", "s0"."BlogId", "s0"."Name", "s0"."Id"
```

### Possible optimization

It seems to me that the query planner of SQLite and Postgres alike is not able to use an index on BlogReviewers.BlogId effectively for queries like this. If we add a predicate to the subquery manually that filters for the same id as is already done in the main query, the index will be used and the query executes much faster. Is this something general enough so that it can be done in EF or is this an optimization that is to specific to my circumstances?

### Workaround

Doing e.g. `(await q.ToListAsync()).FirstOrDefault()` seems counter intuitive first but results in a better query plan and probably the same amount of data on the wire as with EF-side `FirstOrDefaultAsync()`.