Count how many items appear before an item with a specific ID

Question

I have this project, and one of the tasks I need to do is find what page a specific object appears on. The object has a predefined ID, and the ID's appear in order from 0 to N, but they could potentially skip values.

Which means, obviously, that using the ID of the element I'm looking for won't work, as if it's 25, and there are supposed to be 10 items per page, but 8 of those items are missing, it would try to fit on page 3, but could only fit on page 2.

Here's what I have now, I was wondering if there was a better way:

int itemsPerPage = Convert.ToInt32(masterDbContext.Settings.First(x => x.Name == "ItemsPerPage").Value);
int itemCount = masterDbContext.Items.OrderBy(x => x.Id).TakeWhile(x => x.Id < currentItemId).Count();
int pageNumber = (int)Math.Ceiling((double)itemCount / itemsPerPage);

Response.Redirect("~/View/" + rootItem.Id + (pageNumber > 1 ? "/Page/" + pageNumber : ""));

If there's no better way to do this, that's fine. I just hope that there is, because I'm sure the TakeWhile can take, well, a while when I have thousands of Items in the database.

Do note: this method currently works it seems.

Answer 1

You almost have it. Since you are ordering items by ID and counting how many of them with ID less than a number, you can just use that condition in Count:

 var itemsPerPage = Convert.ToInt32(masterDbContext.Settings.First(x => x.Name == "ItemsPerPage").Value); 
 var itemCount = masterDbContext.Items.Count(x => x.Id < currentItemId);

Also there is no direct SQL match for TakeWhile LINQ extension method. So you better of not using it, when you want to have your LINQ query translated to SQL

Answer 2

Assuming this uses some form of SQL provider, all you have to do is analogous to this in SQL:

SELECT COUNT([Id])
FROM [Items]
WHERE [Id] < @currentItemId

C# LINQ:

masterDbContext.Items.Count(item => item.Id < currentItemId);