How to get row index by entity key in a dynamically built query using Entity Framework

Question

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.

I'm working with LINQ to Entities. The query is built dynamically, based on user input.

The table contains too many (more than 10^5) records, for the following Stack Overflow suggestion to be any good:

Recs = Recs.Where( /* Filters */ );
Recs = Recs.OrderBy( /* Sort criteria */ );
Recs.AsEnumerable()
        .Select((x,index) => new {RowNumber = index, Record = x})
        .Where(x=>x.Record.ID = 35);

Because LINQ to Entities doesn't support Select((entity, index) => ...), it would require downloading 250,000 records from the SQL server just so I could decide to show page 25,000.

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).

How should I approach this? Is this problem already solved? Is there any library for .NET that takes me even half way there?

Answer 1

Here is a recursive function you can call to figure out the row number. If your database records are changing frequently it probably won't work, since this is calling the database multiple times narrowing down the search in half each time.

public static int FindRowNumber<T>(IQueryable<T> query, Expression<Func<T, bool>> search, int skip, int take)
{
  if(take < 1) return -1;
  if(take == 1) return query.Skip(skip).Take(take).Any(search) ? skip : -1;      


  int bottomSkip = skip;
  int bottomTake = take / 2;
  int topSkip = bottomTake + bottomSkip;
  int topTake = take - bottomTake;

  if(query.Skip(bottomSkip).Take(bottomTake).Any(search))
  {        
    return FindRowNumber(query, search, bottomSkip, bottomTake);
  }
  if(query.Skip(topSkip).Take(topTake).Any(search))
  {
    return FindRowNumber(query, search, topSkip, topTake);
  }

  return -1;
}

You call it like so:

var query = ... //your query with ordering and filtering
int rownumber = FindRowNumber(query, x => x.Record.ID == 35, 0, query.Count());