Linq to Entities (or Entity Framework) - Find Index of Row Matching Condition in Entity Set (DbSet/ ObjectSet)

This article aims to explain how to find the index of a row matching a particular condition in DbSet/ObjectSet using Linq to Entities.

While working with Entity Framework (at least till Entity Framework 5), and Linq-to-entities, I realized it doesn't have support to find index of row within the DbSet for obvious reasons. And so, I thought of writing a method manually. There are many approaches for the same, and different developers might have implemented different approaches. I found the one I implemented below as more performance optimized.

Say, for example, you have an entity set "Cities". There exists thousands of rows in this table. You need to sort the cities by their name, and want to find index of a particular city within that ordered collection.

One instance of such need is to have paging implemented in your site, and you want your user to jump to the page containing record of a particular city by keeping paging intact.

The approach is to - First check if that particular city exists in the "Cities" collection or not. If it exists, call the below FindIndex method:

Also, take a note of loopCapacity variable in the below code. In the below example, it means, the final "for loop" to find and calculate the index will be started only after a bunch of 100 records are found. Here, these 100 records contain that particular record (i.e. city) which we are looking for.

public int FindIndex(String CityName)
        {
            int index = 0;
            int totalRecords = db.Cities.Count();
            int halfCount = Convert.ToInt32(Math.Ceiling(Convert.ToDecimal(totalRecords / 2))) + 1;
            int loopCapacity = 100;
            int recordsToSkip = 0;
            if (totalRecords > 0)
            {
                bool nextIteration = true;
                while (nextIteration)
                {
                    for (int counter = 0; counter < 2; counter++)
                    {
                        recordsToSkip = recordsToSkip + (counter * halfCount);
                        if (db.Cities.OrderBy(c => c.CityName).Skip(recordsToSkip).Take
                        (halfCount).Where(c => c.CityName == CityName).Count() > 0)
                        {
                            if (halfCount > loopCapacity)
                            {
                                totalRecords = totalRecords - (halfCount * 1);
                                halfCount = Convert.ToInt32
                                (Math.Ceiling(Convert.ToDecimal(totalRecords / 2))) + 1;
                                nextIteration = true;
                                break;
                            }
                            else
                            {
                                foreach (Cities city in db.Cities.OrderBy
                                (c => c.CityName).Skip(recordsToSkip).Take(halfCount))
                                {
                                    if (city.CityName == CityName)
                                    {
                                        index = index + 1;
                                        index = recordsToSkip + index;
                                        break;
                                    }
                                    else
                                    {
                                        index = index + 1;
                                    }
                                }
                                nextIteration = false;
                                break;
                            }
                        }
                        else
                        {
                            nextIteration = true;
                        }
                    }
                }
            }
            return index;
        }

Hope this helps!

Do let me know if anything is unclear, or if you need explanation for any part of the code.

Thanks!

CodeProject