Get record's row number using Linq

Question

I'd like to get a record's row number inside a huge list of records from a table, using Entity Framework 6.

I tried this code:

//var currentUser = my record
var orderedUsers = dbContext.User.OrderByDescending(u => u.Age).ToList();

var userIndex = orderedUsers.IndexOf(currentUser);

The ToList() method crashes with a timeout, because I assume that this methods loads the whole list in memory.

How can I get this row number using a simpler method with Linq (so, without ToList or mounting everything in memory) ?

For information, my goal is to get a range of record from a start index to another index. Here's the code I wrote to do this:

var result = orderedUsers.Skip(userIndex).Take(30).ToList();

Thanks

could you store the page number in an int? then just do `var result = orderedUsers.Skip(30 * pagenumber).Take(30).ToList();` — user1666620, Oct 05 '15 at 16:07

score 7 · Accepted Answer · edited Dec 19 '17 at 02:53

7

Use the form of Select that takes a lambda using an index parameter as well as the entity:

int index = dbContext
  .User
  .OrderByDescending(u => u.Age)
  .Select((user, index) => new {user, index})
  .First(x => x.user == currentUser)
  .index;

my goal is to get a range of record from a start index to another index

So if you want to skip while you haven't reached a particular record, why not just do exactly that:

dbContext
  .User
  .SkipWhile(x => x != currentUser)
  .Take(30);

edited Dec 19 '17 at 02:53

Scott Gartner

862
14
22

answered Oct 05 '15 at 16:09

Jon Hanna

110,372
10
146
251

Thanks. Your second piece of code would be perfect but I need a range containing the user at its middle position. 30 users before and 30 after. So I think I cannot use SkipWhile. The first piece of code didn't compile because of the second Select clause. I replaced it with First(...).i; but I ran into this error: 'IQueryable`1[<>f__AnonymousType5`2[User,System.Int32]] Select[User,<>f__AnonymousType5`2](IQueryable`1[User], Expressions.Expression`1[Func`3[User,System.Int32,<>f__AnonymousType5`2[User,System.Int32]]])' method, and this method cannot be translated into a store expression. – Rotan Oct 06 '15 at 07:36
Do you have an ID on the user to allow you to use `First(x => x.e.Id == currentuser.Id)`? – Jon Hanna Oct 06 '15 at 12:19
I have an ID on the user, but when I try to modify the First method according to your answer it still doesn't compile – Rotan Oct 06 '15 at 15:56
A compile rather than runtime error? Hmm. If it was a runtime failure to compile to SQL I'd consider `.AsEnumerable()` after `OrderByDescending` to do it in memory, though I'd experiment to see if I could get around that as it would obviously be nicer done in the database. I'm surprised it doesn't compile at all though. – Jon Hanna Oct 06 '15 at 20:56
Yes, at compile I got: Error CS1061 '' does not contain a definition for 'Select' and no extension method 'Select' accepting a first argument of type '' could be found ... I just copy past your code above and remove the doubles point after the first select – Rotan Oct 07 '15 at 11:00
Thanks, no more compil errors but i still get my runtime error: LINQ to Entities does not recognize the method 'System.Linq.IQueryable`1[<>f__AnonymousType5`2[User,System.Int32]] Select[User,<>f__AnonymousType5`2](System.Linq.IQueryable`1[User], System.Linq.Expressions.Expression`1[System.Func`3[User,System.Int32,<>f__AnonymousType5`2[User,System.Int32]]])' method, and this method cannot be translated into a store expression. I tried also like you said to put my Id like First(x => x.e.Id == currentuser.Id) but it does the same. – Rotan Oct 08 '15 at 09:01
Adding AsEnumerable() after OrderByDescending works but I assume it will load all my users in memory... – Rotan Oct 08 '15 at 09:09
Yes, that last error is a limitation of EF (which can't handle all valid linq expressions). `AsEnumerable()` gets around that limitation, but as you say does far more pulling into memory. `…OrderBy(…).TakeWhile(x => x != currentUser).Count()` might work through EF and is another way to get the same answer. – Jon Hanna Oct 08 '15 at 09:21
OrderByDescending ... TakeWhile ... Count() throws the same error, cannot be translated into a store expression. – Rotan Oct 08 '15 at 09:28
I'm afraid I can't think of anything other than using `AsEnumerable()` then. You could at least do `Select(x => x.Id)`.AsEnumerable()` so you were only pulling the IDs into memory, since that's all you need to find the match. – Jon Hanna Oct 08 '15 at 10:48

user1666620 · Answer 2 · 2015-10-05T16:19:31.723

A simple way would probably be to just store the page number in an integer, and do a multiplication.

public List<User> GetUsers(int pageNumber = 0) //pageNumber is the page you are on
{
    int itemsPerPage = 30;
    var orderedUsers = dbContext.User.OrderByDescending(u => u.Age)
                      .Skip(itemsPerPage * pageNumber)
                      .Take(itemsPerPage).ToList();
    return orderedUsers;
}

Get record's row number using Linq

2 Answers2

Linked