title: ASP.NET Core EF Query ignoring OrderBy when using Skip/Take with Subquery
body: <p>I've been running into problems with my application using asp.net core 1.1 and mvc. As best as I can tell, I'm getting incorrect results when performing a query, when combining orderby, skip/take, and a query on an included table.</p>
  
  <p>The schema is pretty straight forward.</p>
  
  <pre><code>public Class Member
  {
      public int Id {get; set;}
      public int Family {get; set;}
      public bool IsMember {get; set;}
      public DateTime DeceasedDate {get; set;}
      ...
      public int LivesWithId {get; set;}
      public Member LivesWith {get; set;}
      public ICollection&lt;Member&gt; LivingWith {get; set;}
  }
  </code></pre>
  
  <p>The short of it is, is you have one person with a family, and other people can be set to live with this person. A standard one to many.</p>
  
  <p>The c# code I'm running looks relatively simple... it finds anyone that is either a member or has someone living with them that isn't deceased. It's not a very good query, and it's kind of slow, but for my purposes it should be good enough. And it does work, until I toss in skip/take.</p>
  
  <pre><code>var q = _context.Members.Include(m =&gt; m.LivingWith).AsQueryable();
  
  q = q.Where(m =&gt; m.IsMember || m.LivingWith.Count(s =&gt; s.DeceasedDate == null) &gt; 0);
  q = q.OrderBy(m =&gt; m.Family);
  q = q.Skip(100);
  q = q.Take(100);
  
  var result = await q.ToListAsync();
  </code></pre>
  
  <p>Entity Framework takes this and spits out two queries. I'm not entirely sure why this is, the first query I think has everything that is needed... but the important thing is that entity framework appears to abandon the ordering, and I can't figure out why. Here's the SQL that is generated by this:</p>
  
  <pre><code>SELECT [t].[Id], [t].[Family], (...some fields omitted...)
  FROM (
      SELECT [m].[Id], [m].[Family], (...some fields omitted...) ROW_NUMBER() OVER(ORDER BY [m].[Family]) AS [__RowNumber__]
      FROM [Members] AS [m]
      WHERE  (([m].[IsMember] = 1) OR ((
          SELECT COUNT(*)
          FROM [Members] AS [s]
          WHERE [s].[DeceasedDate] IS NULL AND ([m].[Id] = [s].[LivesWithId])
      ) &gt; 0))
  ) AS [t]
  WHERE ([t].[__RowNumber__] &gt; @__p_0) AND ([t].[__RowNumber__] &lt;= (@__p_0 + @__p_1))
  ORDER BY [t].[Id]
  
  SELECT [m0].[Id], [m0].[Family], (...some fields omitted...)
  FROM [Members] AS [m0]
  WHERE EXISTS (
      SELECT 1
      FROM (
          SELECT [m].[Id], [m].[Family], (...some fields omitted...), ROW_NUMBER() OVER(ORDER BY [m].[Family]) AS [__RowNumber__]
          FROM [Members] AS [m]
          WHERE ([m].[IsMember] = 1) OR ((
              SELECT COUNT(*)
              FROM [Members] AS [s]
              WHERE [s].[DeceasedDate] IS NULL AND ([m].[Id] = [s].[LivesWithId])
          ) &gt; 0)
      ) AS [t]
      WHERE (([t].[__RowNumber__] &gt; @__p_0) AND ([t].[__RowNumber__] &lt;= (@__p_0 + @__p_1))) AND ([m0].[LivesWithId] = [t].[Id]))
  ORDER BY [m0].[LivesWithId]
  </code></pre>
  
  <p>It may not be obvious, but entity framework is doing some funny business that breaks the order specified. It's splitting groups correctly with skip/take, but it's not sorting results within that group because it's tossing in the orderby id at the end. I'm not entirely sure if entity framework is doing something wrong, of if I am. I imagine my query might not be very good, but I had difficulty making a query where it wouldn't execute a separate query for each resulting row.</p>
  
  <p><strong>Edit</strong>: In the comments, it appears to be caused by a EF bug. For now, I can work around the issue by putting a second order by after the skip/take</p>