Windows function inside of query that will be joined as a table leads to invalid SQL

I was going to use a CTE for this, but linq2db can't figure out how to handle SQL Server temporal tables.

The query below translates to invalid SQL om efcore-extensions:

from change0 in changeSet.TemporalAll()
join change1 in
	(
		from change in changeSet.TemporalAll()
		where change.Id == key
		select new
		{
			change.Id,
			PeriodStart = EF.Property<DateTime>(change, "PeriodStart"),
			RowNumber = EF.Functions.RowNumber(
				EF.Functions.Over().OrderBy(EF.Property<DateTime>(change, "PeriodStart")).PartitionBy(change.Id)
			)
		}
	) on new { change0.Id, PeriodStart = EF.Property<DateTime>(change0, "PeriodStart") } equals new { change1.Id, change1.PeriodStart }
where change1.RowNumber <= 2
select change0.Id

Produces this...

SELECT [c].[Id]
FROM
    [Ephron.Metadata.DotNet].[Class] FOR SYSTEM_TIME ALL AS [c]
    INNER JOIN
    (
        SELECT [c0].[Id]
              ,[c0].[PeriodStart]
        FROM
            [Ephron.Metadata.DotNet].[Class] FOR SYSTEM_TIME ALL AS [c0]
        WHERE
            [c0].[Id] = @__key_0
    )                                                    AS [t]
        ON [c].[Id] = [t].[Id]
           AND [c].[PeriodStart] = [t].[PeriodStart]
WHERE
    ROW_NUMBER() OVER (PARTITION BY [t].[Id] ORDER BY [t].[PeriodStart]) <= 2;

Should produce...

SELECT [c].[Id]
FROM
    [Ephron.Metadata.DotNet].[Class] FOR SYSTEM_TIME ALL AS [c]
    INNER JOIN
    (
        SELECT [c0].[Id]
              ,[c0].[PeriodStart]
              ,ROW_NUMBER() OVER (PARTITION BY [t].[Id] ORDER BY [t].[PeriodStart])  [RowNumber]
        FROM
            [Ephron.Metadata.DotNet].[Class] FOR SYSTEM_TIME ALL AS [c0]
        WHERE
            [c0].[Id] = @__key_0
    )                                                    AS [t]
        ON [c].[Id] = [t].[Id]
           AND [c].[PeriodStart] = [t].[PeriodStart]
WHERE
    [t].[RowNumber] <= 2;

Below is the expression tree of the LINQ query, which is a little overcomplicated by calling a number of EF Core methods inline.

// using static System.Linq.Expressions.Expression

var key = Parameter(
    typeof(int),
    "key"
);
var change = Parameter(
    typeof(Class),
    "change"
);
var change = Parameter(
    typeof(Class),
    "change"
);
var change0 = Parameter(
    typeof(Class),
    "change0"
);
var change1 = Parameter(
    typeof(<anonymous({ int Id, DateTime PeriodStart, long RowNumber })>),
    "change1"
);
var change0 = Parameter(
    typeof(Class),
    "change0"
);
var change1 = Parameter(
    typeof(<anonymous({ int Id, DateTime PeriodStart, long RowNumber })>),
    "change1"
);
var <>h__TransparentIdentifier0 = Parameter(
    typeof(<anonymous({ Class change0, { int Id, DateTime PeriodStart, long RowNumber } change1 })>),
    "<>h__TransparentIdentifier0"
);
var <>h__TransparentIdentifier0 = Parameter(
    typeof(<anonymous({ Class change0, { int Id, DateTime PeriodStart, long RowNumber } change1 })>),
    "<>h__TransparentIdentifier0"
);

Call(
    typeof(Queryable).GetMethod("Select", new[] { typeof(IQueryable<{ Class change0, { int Id, DateTime PeriodStart, long RowNumber } change1 }>), typeof(Expression<Func<{ Class change0, { int Id, DateTime PeriodStart, long RowNumber } change1 }, int>>) }),
    Call(
        typeof(Queryable).GetMethod("Where", new[] { typeof(IQueryable<{ Class change0, { int Id, DateTime PeriodStart, long RowNumber } change1 }>), typeof(Expression<Func<{ Class change0, { int Id, DateTime PeriodStart, long RowNumber } change1 }, bool>>) }),
        Call(
            typeof(Queryable).GetMethod("Join", new[] { typeof(IQueryable<Class>), typeof(IEnumerable<{ int Id, DateTime PeriodStart, long RowNumber }>), typeof(Expression<Func<Class, { int Id, DateTime PeriodStart }>>), typeof(Expression<Func<{ int Id, DateTime PeriodStart, long RowNumber }, { int Id, DateTime PeriodStart }>>), typeof(Expression<Func<Class, { int Id, DateTime PeriodStart, long RowNumber }, { Class change0, { int Id, DateTime PeriodStart, long RowNumber } change1 }>>) }),
            Call(
                typeof(EntityFrameworkQueryableExtensions).GetMethod("AsNoTracking"),
                --
-- Not implemented - NodeType: Extension not implemented.
--
            ),
            Call(
                typeof(Queryable).GetMethod("Select", new[] { typeof(IQueryable<Class>), typeof(Expression<Func<Class, { int Id, DateTime PeriodStart, long RowNumber }>>) }),
                Call(
                    typeof(Queryable).GetMethod("Where", new[] { typeof(IQueryable<Class>), typeof(Expression<Func<Class, bool>>) }),
                    Call(
                        typeof(EntityFrameworkQueryableExtensions).GetMethod("AsNoTracking"),
                        --
-- Not implemented - NodeType: Extension not implemented.
--
                    ),
                    Quote(
                        Lambda(
                            Equal(
                                MakeMemberAccess(change,
                                    typeof(ITrackedEntity).GetProperty("Id")
                                ),
                                key
                            ),
                            change
                        )
                    )
                ),
                Quote(
                    Lambda(
                        New(
                            typeof(<anonymous({ int Id, DateTime PeriodStart, long RowNumber })>).GetConstructor(new[] { typeof(int), typeof(DateTime), typeof(long) }),
                            MakeMemberAccess(change,
                                typeof(ITrackedEntity).GetProperty("Id")
                            ),
                            Call(
                                typeof(EF).GetMethod("Property"),
                                change,
                                Constant("PeriodStart")
                            ),
                            Call(
                                typeof(DbFunctionsExtensions).GetMethod("RowNumber"),
                                MakeMemberAccess(null,
                                    typeof(EF).GetProperty("Functions")
                                ),
                                Call(
                                    typeof(DbFunctionsExtensions).GetMethod("PartitionBy"),
                                    Call(
                                        typeof(DbFunctionsExtensions).GetMethod("OrderBy"),
                                        Call(
                                            typeof(DbFunctionsExtensions).GetMethod("Over"),
                                            MakeMemberAccess(null,
                                                typeof(EF).GetProperty("Functions")
                                            )
                                        ),
                                        Call(
                                            typeof(EF).GetMethod("Property"),
                                            change,
                                            Constant("PeriodStart")
                                        )
                                    ),
                                    MakeMemberAccess(change,
                                        typeof(ITrackedEntity).GetProperty("Id")
                                    )
                                )
                            )
                        ),
                        change
                    )
                )
            ),
            Quote(
                Lambda(
                    New(
                        typeof(<anonymous({ int Id, DateTime PeriodStart })>).GetConstructor(new[] { typeof(int), typeof(DateTime) }),
                        MakeMemberAccess(change0,
                            typeof(ITrackedEntity).GetProperty("Id")
                        ),
                        Call(
                            typeof(EF).GetMethod("Property"),
                            change0,
                            Constant("PeriodStart")
                        )
                    ),
                    change0
                )
            ),
            Quote(
                Lambda(
                    New(
                        typeof(<anonymous({ int Id, DateTime PeriodStart })>).GetConstructor(new[] { typeof(int), typeof(DateTime) }),
                        MakeMemberAccess(change1,
                            typeof(<anonymous({ int Id, DateTime PeriodStart, long RowNumber })>).GetProperty("Id")
                        ),
                        MakeMemberAccess(change1,
                            typeof(<anonymous({ int Id, DateTime PeriodStart, long RowNumber })>).GetProperty("PeriodStart")
                        )
                    ),
                    change1
                )
            ),
            Quote(
                Lambda(
                    New(
                        typeof(<anonymous({ Class change0, { int Id, DateTime PeriodStart, long RowNumber } change1 })>).GetConstructor(new[] { typeof(Class), typeof(<anonymous({ int Id, DateTime PeriodStart, long RowNumber })>) }),
                        change0, change1
                    ),
                    change0, change1
                )
            )
        ),
        Quote(
            Lambda(
                LessThanOrEqual(
                    MakeMemberAccess(
                        MakeMemberAccess(<>h__TransparentIdentifier0,
                            typeof(<anonymous({ Class change0, { int Id, DateTime PeriodStart, long RowNumber } change1 })>).GetProperty("change1")
                        ),
                        typeof(<anonymous({ int Id, DateTime PeriodStart, long RowNumber })>).GetProperty("RowNumber")
                    ),
                    Constant(2)
                ),
                <>h__TransparentIdentifier0
            )
        )
    ),
    Quote(
        Lambda(
            MakeMemberAccess(
                MakeMemberAccess(<>h__TransparentIdentifier0,
                    typeof(<anonymous({ Class change0, { int Id, DateTime PeriodStart, long RowNumber } change1 })>).GetProperty("change0")
                ),
                typeof(ITrackedEntity).GetProperty("Id")
            ),
            <>h__TransparentIdentifier0
        )
    )
)

Start by reproducing the LINQ query and comparing its generated SQL with the expected SQL shown in the issue, using the SQL Server temporal-table and window-function paths. Trace the translation of RowNumber when the query is joined as a table; done means the generated SQL places the window expression and alias in the joined subquery and uses that alias in the outer filter.