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

Question

In the grid, I need to write a record by its identifier. This is why I need to find its index in a user filter and a user sorted set.

I work with LINQ to Entities . The request is created dynamically based on user input.

The table contains too many entries (over 10 ^ 5) for the following Offer :

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

Since LINQ to Entities does not support Select((entity, index) => ...) , this will require downloading 250,000 records from the SQL server so that I can decide to display the 25,000 page.

Currently, my most promising idea is to convert each sort criteria into a filter. Thus, the search for the index of a person sorted by increasing height will be a count of shorter faces (sorting criterion "height of increase" => filter "height is less than" + count).

How do I approach this? Has this problem been resolved yet? Is there a library for .NET that takes me even halfway?

Answer 1

Here is a recursive function that you can call to find out the line number. If your entries in the database change frequently, this probably won't work, as it calls up a database that narrows the search in half several times 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 this:

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