Orderings when Offset is used

[cincuranet]

When the query contains Offset, this code puts ORDER BY (SELECT 1) into the SQL. For the future it would make sense to have this part overridable instead of whole GenerateOrderings.

[cincuranet]

This could probably be handled using the #18999.

[takacsalbert]

When using with Distinct(), this leads to an invalid query.
This code:

context.Vocabularies.Where(x => x.Expression.StartsWith(startChar)).OrderBy(x => x.Expression).Select(x => x.Expression).Distinct().Skip(offset).Take(pageSize);`

Translates to this query:

SELECT DISTINCT [v].[Expression]
      FROM [Vocabulary] AS [v]
      WHERE (@__startChar_0 = N'') OR ([v].[Expression] IS NOT NULL AND (LEFT([v].[Expression], LEN(@__startChar_0)) = @__startChar_0))
      ORDER BY (SELECT 1)
      OFFSET @__p_1 ROWS FETCH NEXT @__p_2 ROWS ONLY

And the following SQL error occures

ORDER BY items must appear in the select list if SELECT DISTINCT is specified.

The problem is with ORDER BY (SELECT 1), it should be either ORDER BY 1 ORDER BY [v].[Expression]

I know, it's a strange combination, but sh!t happens. :)

[smitpatel]

Distinct gives you unordered result set, doing Skip/Take on that without order by does not make sense. If we did fix that invalid SQL, it would likely be an error message asking to add OrderBy before Skip/Take.

[takacsalbert]

Distinct gives you unordered result set, doing Skip/Take on that without order by does not make sense. If we did fix that invalid SQL, it would likely be an error message asking to add OrderBy before Skip/Take.

Thanks for the reply.
Replacing the 'ORDER BY (SELECT 1)' with 'ORDER BY 1' making the query work. That's the only issue. Tried in SQL Management Studio.

[smitpatel]

Order by 1 is ordering by first column in the result. It may work in this query, same may not be true for all the queries.