IQueryable against SQL 2000 Paging with Joins

Question

I have a query that contains about 10 joins. Using a List View and Linq Data Source it pages fine against SQL2008 but fails to produce the correct result when run against SQL2000. No exception is thrown but the results are clearly out of order and at times, the same page is returned. Reading through the LINQ Docs, paging against queries wiht joins are not supported on SQL2000. Enough said although I would have expected an exception.

I am unable to move the data off SQL2000 right now. The solution is to do a full query and then perform memory based paging.

protected void LinqDataSourceMain_Selecting(object sender,
    LinqDataSourceSelectEventArgs e)
{
    // var query = myquery with join...
    e.Result = query.ToList();
}

Now the question is: Is it more efficient to invoke ToList() or ToArray() ?

AsEnumerable() does not fix the issue. Looking at the extension method with Reflector, it simply returns the 'source' so this makes sense to me.

e.Result = query.AsEnumerable();
public static IEnumerable<TSource> AsEnumerable<TSource>(
    this IEnumerable<TSource> source)
{
    return source;
}

However, would the following be more efficient than either ToList() or ToArray()? It does work and it does defer the execution.

e.Result = query.ToEnumerable();

public static IEnumerable<TSource> ToEnumerable<TSource>(
    this IEnumerable<TSource> source)
{
    foreach (var item in source)
        yield return item;
}

Answer 1

ToList() will be more efficient than ToArray() because it doesn't need to do the final copy from an oversized buffer to a fixed-sized array.

If you're going to do in-memory paging, you don't want deferred execution (IMO) because you need to get the count of items, and random access. You don't want to have to re-execute the query each time.