title: Get page containing an item with an Id
body: <p>Using Entity Framework if have a MyDbContext with a DbSet Customers.</p>
  
  <p>I want to display the customers page by page, so I have created a function (simplified):</p>
  
  <pre><code>public List&lt;Customer&gt; GetCustomerPage&lt;TKey&gt;
      (int pageNr, int pageSize, Func&lt;Customer, TKey&gt; keySelector);
  </code></pre>
  
  <p>This function will order myDbContext.Customers by keySelector, and use Skip and Take to return a page:</p>
  
  <pre><code>using (MyDbContext myDbContext = ...)
  {
      return myDbContext.Customers
          .Orderby(keySelector)
          .Skip(pageNr * pageSize)
          .Take(pageSize)
          .ToList();
  }
  </code></pre>
  
  <p>This works fine and efficient. Now suppose I have a customer with a customerId and I want the page on which this customer is.</p>
  
  <p>If everything would be in a local List, then this would be easy.
  Find the index of the customer with Id, and calculate the page as follows:</p>
  
  <pre><code>int customerIndex = customerList.Single(cust =&gt; cust.Id == customerId);
  int pageToGet = (int)Math.Floor(customerIndex / pageSize);
  </code></pre>
  
  <p>If you have an IEnumerable instead of a List you could do something like</p>
  
  <pre><code>int customerIndex = Customers.Select( (cust, index) =&gt; new
      {
          Index = index,
          Customer = cust,
      })
      .Single(cust =&gt; cust.Id == customerId)
      .Index;
  </code></pre>
  
  <p>Alas, this works only AsEnumerable, not AsQueryable.</p>
  
  <p>SQL has the concept of ROW_NUMBER, and it would enough to get the ROW_NUMBER of the customer with customerId.</p>
  
  <p>Several questions on StackOverFlow answer how to get the ROW_NUMBER, for instance <a href="https://stackoverflow.com/questions/17788400/how-do-i-translate-a-query-that-uses-row-number-into-linq">How do I translate a query that uses ROW_NUMBER() into linq?</a>. </p>
  
  <p>Alas, all solutions I find have AsEnumerable somewhere in the LINQ statement, which causes an SQL select of the complete customer collection. This is confirmed by the SQL Server Profiler.</p>
  
  <p>So question: <strong>how to get the ordered index number of the Customer with customerId</strong></p>
  
  <p>By the way: I need this for most of my tables. A stored procedure for every table might not be a good solution.</p>