Query: Remove additional orderings for split collection include #21273
Description
There are basically 2 cases here.
ss.Set<Level1>().Include(l1 => l1.OneToMany_Optional1.OrderBy(x => x.Name).Take(3)).AsSplitQuery()
Above query generates a split query with 2nd query containing SelectMany which gets translated to INNER JOIN using RowNumberExpression for window function. This erases ordering from the 2nd query which we need to preserve so that results are also sorted in same order in ordered include. Hence we lift ordering from the RowNumberExpression which takes us to 2 cases where we could potentially optimize. (posting only 2nd query generated here)
Case 1:
SELECT [t0].[Id], [t0].[Date], [t0].[Level1_Optional_Id], [t0].[Level1_Required_Id], [t0].[Name], [t0].[OneToMany_Optional_Inverse2Id], [t0].[OneToMany_Optional_Self_Inverse2Id], [t0].[OneToMany_Required_Inverse2Id], [t0].[OneToMany_Required_Self_Inverse2Id], [t0].[OneToOne_Optional_PK_Inverse2Id], [t0].[OneToOne_Optional_Self2Id], [l].[Id]
FROM [LevelOne] AS [l]
INNER JOIN (
SELECT [t].[Id], [t].[Date], [t].[Level1_Optional_Id], [t].[Level1_Required_Id], [t].[Name], [t].[OneToMany_Optional_Inverse2Id], [t].[OneToMany_Optional_Self_Inverse2Id], [t].[OneToMany_Required_Inverse2Id], [t].[OneToMany_Required_Self_Inverse2Id], [t].[OneToOne_Optional_PK_Inverse2Id], [t].[OneToOne_Optional_Self2Id]
FROM (
SELECT [l0].[Id], [l0].[Date], [l0].[Level1_Optional_Id], [l0].[Level1_Required_Id], [l0].[Name], [l0].[OneToMany_Optional_Inverse2Id], [l0].[OneToMany_Optional_Self_Inverse2Id], [l0].[OneToMany_Required_Inverse2Id], [l0].[OneToMany_Required_Self_Inverse2Id], [l0].[OneToOne_Optional_PK_Inverse2Id], [l0].[OneToOne_Optional_Self2Id], ROW_NUMBER() OVER(PARTITION BY [l0].[OneToMany_Optional_Inverse2Id] ORDER BY [l0].[Name]) AS [row]
FROM [LevelTwo] AS [l0]
) AS [t]
WHERE [t].[row] <= 3
) AS [t0] ON [l].[Id] = [t0].[OneToMany_Optional_Inverse2Id]
ORDER BY [l].[Id], [t0].[OneToMany_Optional_Inverse2Id], [t0].[Name]Since join condition already defines [l].[Id] = [t0].[OneToMany_Optional_Inverse2Id], 1st & 2nd ordering are essentially same. We can remove the 2nd ordering if we inspect join condition. We cannot remove it always if the collection navigation is targeting Alternate Key. In that case the first ordering would be PK of outer and 2nd ordering would be AK/FK value for the relationship.