Incorrect ordering with Include, OrderBy and Skip/Take

[samcook]

When including a 1-n relationship, ordering is not always applied as expected, depending on where in the expression the ordering is specified.

Example 1 (OrderBy before Include) :x:

var malls = this.model.Malls
	.OrderBy(x => x.LongId)
	.Include(x => x.Shops)
	.ToList();

results in

SELECT ...
FROM "Mall" AS "T0"
 LEFT JOIN 
(
  (SELECT ...
  FROM "Shop" AS "T3")
  ) AS "T4"
  ON (("T0"."MallId") = ("T4"."MallId"))
ORDER BY "T0"."MallId", "T4"."ShopId", "T4"."AddressId", "T4"."AddressRegionId", "T4"."AddressRegionName", "T0"."LongId"

The ORDER BY clause has our LongId last, the results don't come out ordered by LongId.

Example 2 (OrderBy after Include) :heavy_check_mark:

var malls = this.model.Malls
	.Include(x => x.Shops)
	.OrderBy(x => x.LongId)
	.ToList();

results in

SELECT ...
FROM "Mall" AS "T0"
 LEFT JOIN 
(
  (SELECT ...
  FROM "Shop" AS "T2")
  ) AS "T3"
  ON (("T0"."MallId") = ("T3"."MallId"))
ORDER BY "T0"."LongId", "T0"."MallId", "T3"."ShopId", "T3"."AddressId", "T3"."AddressRegionId", "T3"."AddressRegionName"

The ORDER BY clause has our LongId first, the results come out in the expected order.

Example 3 (Skip/Take) :x:

var malls = this.model.Malls
	.Include(x => x.Shops)
	.OrderBy(x => x.LongId)
	.Skip(5)
	.Take(5)
	.ToList();

results in

SELECT ...
FROM 
(
  (SELECT ...
  FROM 
  (
    (SELECT ..., ROW_NUMBER() OVER (ORDER BY "T4"."LongId") AS "__$$ROW_NUMBER"
    FROM 
    (
      (SELECT ...
      FROM "Mall" AS "T0")
      ) AS "T4")
    ) AS "T4_ROW"
  WHERE (((("T4_ROW"."__$$ROW_NUMBER") > (5))) AND ((("T4_ROW"."__$$ROW_NUMBER") <= (((5) + (5)))))))
  ) AS "T4"
 LEFT JOIN 
(
  (SELECT ...
  FROM "Shop" AS "T5")
  ) AS "T6"
  ON (("T4"."MallId") = ("T6"."MallId"))
ORDER BY "T4"."MallId", "T6"."ShopId", "T6"."AddressId", "T6"."AddressRegionId", "T6"."AddressRegionName"

The LongId ordering is applied in the ROW_NUMBER() window function, but not the final ORDER BY clause, results don't come out ordered by LongId.

Example 4 (Skip/Take with ordering specified twice) :heavy_check_mark:

var malls = this.model.Malls
	.Include(x => x.Shops)
	.OrderBy(x => x.LongId)
	.Skip(5)
	.Take(5)
	.OrderBy(x => x.LongId)
	.ToList();

results in

SELECT ...
FROM 
(
  (SELECT ...
  FROM 
  (
    (SELECT ..., ROW_NUMBER() OVER (ORDER BY "T4"."LongId") AS "__$$ROW_NUMBER"
    FROM 
    (
      (SELECT ...
      FROM "Mall" AS "T0")
      ) AS "T4")
    ) AS "T4_ROW"
  WHERE (((("T4_ROW"."__$$ROW_NUMBER") > (5))) AND ((("T4_ROW"."__$$ROW_NUMBER") <= (((5) + (5)))))))
  ) AS "T4"
 LEFT JOIN 
(
  (SELECT ...
  FROM "Shop" AS "T5")
  ) AS "T6"
  ON (("T4"."MallId") = ("T6"."MallId"))
ORDER BY "T4"."LongId", "T4"."MallId", "T6"."ShopId", "T6"."AddressId", "T6"."AddressRegionId", "T6"."AddressRegionName"

The LongId ordering is applied to both the ROW_NUMBER() window function and the final ORDER BY clause, results come out in the order expected.