QueryRewrite: handle order by lifting (or warn about lack thereof)

[maumar]

In some queries, users can have reasonable expectation about having their results ordered, but due to Joins that order could be lost. E.g.

Example:

l3s.Select(l3 => l3.OneToOne_Required_FK_Inverse3)
   .OrderBy(l2 => l2.Id)
   .Take(10)
   .Select(l2 => l2.OneToOne_Required_FK_Inverse2.Name)

previous translation (order on l3s is preserved)

@__p_0='10'

SELECT TOP(@__p_0) [l3.OneToOne_Required_FK_Inverse3.OneToOne_Required_FK_Inverse2].[Name]
FROM [LevelThree] AS [l3]
INNER JOIN [LevelTwo] AS [l3.OneToOne_Required_FK_Inverse3] ON [l3].[Level2_Required_Id] = [l3.OneToOne_Required_FK_Inverse3].[Id]
INNER JOIN [LevelOne] AS [l3.OneToOne_Required_FK_Inverse3.OneToOne_Required_FK_Inverse2] ON [l3.OneToOne_Required_FK_Inverse3].[Level1_Required_Id] = [l3.OneToOne_Required_FK_Inverse3.OneToOne_Required_FK_Inverse2].[Id]
ORDER BY [l3].[Level2_Required_Id]

(naïve) new translation

@__p_0='10'

SELECT [l2.OneToOne_Required_FK_Inverse2].[Name]
FROM (
    SELECT TOP(@__p_0) [l3.OneToOne_Required_FK_Inverse3].*
    FROM [LevelThree] AS [l3]
    INNER JOIN [LevelTwo] AS [l3.OneToOne_Required_FK_Inverse3] ON [l3].[Level2_Required_Id] = [l3.OneToOne_Required_FK_Inverse3].[Id]
    ORDER BY [l3.OneToOne_Required_FK_Inverse3].[Id]
) AS [t]
INNER JOIN [LevelOne] AS [l2.OneToOne_Required_FK_Inverse2] ON [t].[Level1_Required_Id] = [l2.OneToOne_Required_FK_Inverse2].[Id]

Depending on the join implementation on the database, the second case may not preserve the order. We can fix this by order by lifting, warn about the potential loss of ordering, or keep as is and wait for feedback

[smitpatel]

We lift order by already in 3.1