When using a paged sub-query in Linq, generates incorrect SQL

[ngbrown]

I have an top level query that I control paging on, then use it along with ToFuture() to pull various related lists, then combine at the client.

The top level query has a .Top() along with .OrderBy(). When I use it in a .Any() sub-query, the ORDER BY gets duplicated into the EXISTS/IN query and SQL fails.

Something like this:

	[TestFixture]
	public class WhereSubqueryTests : LinqTestCase
	{
		[Test]
		public void OrdersWithSubquery9()
		{
			var query = db.Orders
			              .Where(x => x.Employee.EmployeeId > 5)
			              .OrderBy(x => x.OrderId)
			              .Take(2);

			var listFuture = db.OrderLines
			                   .Where(x => query.Any(o => o == x.Order))
			                   .OrderBy(x => x.Id)
			                   .ToFuture();

			var results = query.ToFuture().ToList();
			var listResults = listFuture.ToList();

			Assert.That(results.Count, Is.EqualTo(2), nameof(results));
			Assert.That(listResults.Count, Is.EqualTo(6), nameof(listResults));
		}
	}

This results in the following error:

System.Data.SqlClient.SqlException : The ORDER BY clause is invalid in views, inline functions, derived tables, subqueries, and common table expressions, unless TOP, OFFSET or FOR XML is also specified.

The generated SQL looked like this:

select orderline0_.OrderLineId as orderlineid1_4_, orderline0_.OrderId as orderid2_4_, orderline0_.ProductId as productid3_4_, orderline0_.UnitPrice as unitprice4_4_, orderline0_.Quantity as quantity5_4_, orderline0_.Discount as discount6_4_
from OrderLines orderline0_
where exists (
    select order1_.OrderId 
    from Orders order1_ 
    where order1_.OrderId=orderline0_.OrderId and (order1_.OrderId in (
        select TOP (?) order2_.OrderId 
        from Orders order2_ where order2_.EmployeeId=? 
        order by order2_.OrderId asc)) 
    order by order1_.OrderId asc)
order by orderline0_.OrderLineId asc;

I think the issue is in PagingRewriter, because I can remove this if section and then all tests pass:

nhibernate-core/src/NHibernate/Linq/GroupBy/PagingRewriter.cs

Lines 56 to 61 in ac39173

	if (!queryModel.BodyClauses.OfType<OrderByClause>().Any())
	{
	var orderByClauses = subQueryModel.BodyClauses.OfType<OrderByClause>();
	foreach (var orderByClause in orderByClauses)
	queryModel.BodyClauses.Add(orderByClause);
	}