title: How to get row index by entity key in a dynamically built query using Entity Framework
body: <p>In a grid, I need to page to an record by its ID. That is why I need to find its index in the user-filtered and user-sorted set.</p>
  
  <p>I'm working with <a href="http://en.wikipedia.org/wiki/ADO.NET_Entity_Framework#LINQ_to_Entities" rel="nofollow noreferrer">LINQ&nbsp;to&nbsp;Entities</a>. The query is built dynamically, based on user input.</p>
  
  <p>The table contains too many (more than 10^5) records, for the following <a href="https://stackoverflow.com/questions/7892675/how-to-get-sequence-number-of-row-using-entity-framework#answer-7893229">Stack Overflow suggestion</a> to be any good:</p>
  
  <pre><code>Recs = Recs.Where( /* Filters */ );
  Recs = Recs.OrderBy( /* Sort criteria */ );
  Recs.AsEnumerable()
          .Select((x,index) =&gt; new {RowNumber = index, Record = x})
          .Where(x=&gt;x.Record.ID = 35);
  </code></pre>
  
  <p>Because LINQ&nbsp;to&nbsp;Entities doesn't support <code>Select((entity, index) =&gt; ...)</code>, it would require downloading 250,000 records from the SQL server just so I could decide to show page 25,000.</p>
  
  <p>Currently, my most promising idea is to transform each sort criterion into a filter. So finding the index of a person sorted by ascending height would become counting the shorter persons (sort criterion 'height ascending' => filter 'height less than' + count). </p>
  
  <p>How should I approach this? Is this problem already solved? Is there any library for <a href="http://en.wikipedia.org/wiki/.NET_Framework" rel="nofollow noreferrer">.NET</a> that takes me even half way there?</p>