title: LINQ to Entities - Skip and Take very slow
body: <p>I am writing a replacement system which deals with a large number of images. I've migrated what are deemed to be "live" images to a new database, but there is a requirement to search the old database through the UI to allow users to pull different images across.</p>
  
  <p>I've put a view in my database which queries the old database so that I can expose it through Entity Framework. The view works well, and despite having several million records I get results pretty snappily.</p>
  
  <p>My problem is when I try to run a query using LINQ to Entities. My search is paged, and limits results to 100 per page.</p>
  
  <p>I have an IQueryable which I use to filter results, but for Skip/Take I have to add an order by. Example below shows getting page 2 of results (Skip 100, Take 100).</p>
  
  <pre><code>        if (firstId.HasValue)
              query = query.Where(x =&gt; x.Id &gt;= firstId.Value);
  
          if (!string.IsNullOrEmpty(groupCode))
              query = query.Where(x =&gt; x.GroupCode == groupCode);
  
          var daResults = query
              .OrderBy(x =&gt; x.Id)
              .Skip(100)
              .Take(100)
              .ToList();
  </code></pre>
  
  <p>The SQL this generates is:</p>
  
  <pre><code>SELECT 
      [Project1].[Id] AS [Id], 
      [Project1].[FolderPath] AS [FolderPath], 
      [Project1].[Filename] AS [Filename], 
      [Project1].[IsFlagged] AS [IsFlagged], 
      [Project1].[IsHidden] AS [IsHidden], 
      [Project1].[TextField1] AS [TextField1], 
      [Project1].[TextField2] AS [TextField2], 
      [Project1].[TextField3] AS [TextField3], 
      [Project1].[TextField4] AS [TextField4], 
      [Project1].[GroupCode] AS [GroupCode], 
      [Project1].[Deleted] AS [Deleted], 
      [Project1].[Created] AS [Created], 
      [Project1].[CreatedBy] AS [CreatedBy], 
      [Project1].[Updated] AS [Updated], 
      [Project1].[UpdatedBy] AS [UpdatedBy]
      FROM ( SELECT 
          [Extent1].[Id] AS [Id], 
          [Extent1].[FolderPath] AS [FolderPath], 
          [Extent1].[Filename] AS [Filename], 
          [Extent1].[IsFlagged] AS [IsFlagged], 
          [Extent1].[IsHidden] AS [IsHidden], 
          [Extent1].[TextField1] AS [TextField1], 
          [Extent1].[TextField2] AS [TextField2], 
          [Extent1].[TextField3] AS [TextField3], 
          [Extent1].[TextField4] AS [TextField4], 
          [Extent1].[GroupCode] AS [GroupCode], 
          [Extent1].[Deleted] AS [Deleted], 
          [Extent1].[Created] AS [Created], 
          [Extent1].[CreatedBy] AS [CreatedBy], 
          [Extent1].[Updated] AS [Updated], 
          [Extent1].[UpdatedBy] AS [UpdatedBy]
          FROM [dbo].[view_ProEditBulkImageInfo] AS [Extent1]
          WHERE
              ([Extent1].[Deleted] IS NULL) 
          AND ([Extent1].[Id] &gt;= 300000)
      )  AS [Project1]
      ORDER BY row_number() OVER (ORDER BY [Project1].[Id] ASC)
      OFFSET 100 ROWS FETCH NEXT 100 ROWS ONLY
  </code></pre>
  
  <p>The issue seems to be that the first projection is fully evaluated (around 10 million records) which are then sorted before the top 100 rows are returned. The query execution plan shows the SORT cost as 92% of the batch.</p>
  
  <p>This query takes around 30 seconds, just on the cusp of the timeout, so it's hit and miss as to whether it even returns.</p>
  
  <p>I'm looking for some hints on how to speed this thing up, queries against the view are super quick (&lt; 1 second).</p>