Index was out of range exception in query

Question

each Route contains Locations in specific order.
For example: NY -> LA is different from LA -> NY.
I would like to write a method that gets locations array and return true or false whether route with the same locations and order exists.
I need to do it using linq to entities and entity framework (Route and Location are entities). Here is what I wrote:

    public bool IsRouteExists(IList<LocationInRoute> locationsInRoute)
    {
        Route route = null;
        if (locationsInRoute.Count > 0)
        {
            var query = GetRoutesQuery().
                Where(x => x.Locations.Count() == locationsInRoute.Count);

            for (int i = 0; i < locationsInRoute.Count; i++)
            {
                long locationId = locationsInRoute[i].LocationId;
                query = query.Where(x => 
    x.Locations.ElementAt(i).LocationId == locationId); //THROWS EXCEPTION
            }
            route = query.SingleOrDefault();
        }
        return route!=null;
    }

I get the following exception in the marked line:

Index was out of range. Must be non-negative and less than the size of the collection.
Parameter name: index

What is the reason for this exception?

EDIT
The exception accurs when executing route = query.SingleOrDefault(); and the exception complains about Where(x => x.Locations.ElementAt(i).LocationId == locationId);.

Answer 1

I believe this query is completely wrong. First of all it is not linq-to-entities query and it will never be because linq to entities is not able to work with indexes. I think comparing ordered sequences will have to be executed in memory = linq-to-objects.

Another problem is this:

for (int i = 0; i < locationsInRoute.Count; i++)
{
    long locationId = locationsInRoute[i].LocationId;
    query = query.Where(x => x.Locations.ElementAt(i).LocationId == locationId);
}
route = query.SingleOrDefault();

I think this is known gotcha when using Linq, query built in loop and deferred execution - I believe this always compares locationId with the last element.

In my opinion the most efficient way to do this is stored procedure with table valued parameter passing your expected sequence and using SQL cursor to compare sequences in the stored procedure.

Answer 2

Looks like perhaps your x.Locations.Count() might be less than your locationsInRoute.Count. are you sure it's not? I'm saying that b/c you're calling x.Locations.ElementAt(i), which will throw if i > Count().

As a sidenote, a better solution to what you're doing is to override equality or implement an IComparer on your class that you want unique and then you can use things like Any() and Contains() for your test.

Answer 3

If you getting an index out of range exception it must mean that number of elements in the locationsRoute collection exceeds the number of elements in IQueryable. If you attempting to test that each location in the provided list is contained in route you should be able to do something like:

var locationIds = locationsInRoute.Select(l => l.LocationId);
query = query.Where(r => r.Locations.All(l => locationIds.Contains(l.LocationId)))

Answer 4

I'm guessing it has to do with your use of ElementAt, which can't be translated to SQL (see the Operators with No Translation section), to operate on your IQueryable. This materializes the IQueryable's result set on the first iteration and so subsequent iterations Route items will be unable to access their related Locations sets. This should probably only happen on the second iteration, but the myriad implications of the deferred execution nature of LINQ is not entirely clear to me in ever case ;-) HTH

You could put a breakpoint at SingleOrDefault and inspect the SQL statement it is executing there, to see why there is no record returned for the SingleOrDefault to find. Though the SQL may be pretty ugly depending on how many routes you have.

Answer 5

Thanks to @Ladislav Mrnka's advice, here is the solution:

public class LocationSequenceEqual : IEqualityComparer<Location>
    {
        public bool Equals(Location x, Location y)
        {
            return x.Id == y.Id;
        }

        public int GetHashCode(Location obj)
        {
            return obj.Id.GetHashCode();
        }
    }

    public bool IsRouteExists(IList<LocationInRoute> locationsInRoute)
    {
        Route route = null;
        if (locationsInRoute.Count > 0)
        {
            var query = GetRoutesQuery().
                Where(x => x.Locations.Count() == locationsInRoute.Count);

            query = query.Where(x => x.Locations.OrderBy(l => l.Order).
                    Select(l => l.Location).SequenceEqual(locations, new LocationSequenceEqual()));
            route = query.FirstOrDefault();
        }
        return route!=null;
    }

Answer 6

If Location has Order as you indicate above, this can be done entirely in (Linq to) SQL:

public bool IsRouteExists(IList<LocationInRoute> locationsInRoute)
{
    Route route = null;
    if (locationsInRoute.Count == 0)
        return;

    var possibleRoutes = GetRoutesQuery().
        Where(x => x.Locations.Count() == locationsInRoute.Count);

    var db = GetDataContext(); //get a ref to the DataContext or pass it in to this function
    for (var i = 0; i < locationsInRoute.Length; i++)
    {
        var lcoationInRoute = locationsInRoute[i];
        possibleRoutes = possibleRoutes.Where(x => x.Locations.Any(l => l.Id == locationInRoute.Id && l.Order == locationInRoute.Order));
    }
    route = possibleRoutes.FirstOrDefault();

    return route!=null;
}