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?

I don't quite understand what you're asking, are you asking how to find the key value for a particular selection on a particular page? Or are you trying to figure out the best way to retrieve a single page at a time? — CodingGorilla
– CodingGorilla, Commented Mar 22, 2012 at 14:52
Given a row id / entity key, I want to find the position of that particular row in a sorted and filtered query. Say the user decides to sort persons by name, the person set is [{id: 11, name:"Amy"}, {id: 1, name"Joe"}, {id: 2, name"Zack"}], given the id 11 I need to get index=1, id = 1 => index = 2, id = 2 => index = 3 — Călin Darie
– Călin Darie, Commented Mar 22, 2012 at 15:04
Ok, so you have the database Id, you need to find the corresponding row in the UI, right? What is the UI, ASP.NET, WinForms, WPF? — CodingGorilla
– CodingGorilla, Commented Mar 22, 2012 at 15:07
It's the Telerik extensions for ASP MVC, but I think it would take ages to move all the data into the UI, then try to find the index. I'm talking of millions of records. — Călin Darie
– Călin Darie, Commented Mar 22, 2012 at 15:11
what's wrong with your own suggestion? recs.OrderBy(r => r.someProperty).Count(r => r.index < someIndex)? — paul
– paul, Commented Mar 22, 2012 at 15:28

Aducci · Accepted Answer · 2012-03-22 16:38:25Z

3

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());

edited Mar 22, 2012 at 16:38

answered Mar 22, 2012 at 16:13

Aducci

26.8k8 gold badges67 silver badges69 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Călin Darie Over a year ago

It's an interesting approach, but not by far as fast as the one I suggested in the question, which I finally implemented by (ab)using the grid filtering system. On the other hand, it's visibly better than the approach in my snippet. Thanks for the effort!

Aducci Over a year ago

@CălinDarie - You should post your answer and accept it. I think people would benefit from it.

Collectives™ on Stack Overflow

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

1 Answer 1

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related