Linq paging - how to solve performance problem?

Question

Edit: Entity Framework seems to be the issue, discussed further in question Entity Framework & Linq performance problem.

I am supporting a PagedList (using linq/generics) class written by someone long departed - and it has 2 lines that have very bad performance - on a dataset of just 2 thousand rows it takes up to one minute to run.

The two offending lines are:

TotalItemCount = source.Count();

I probably can fix that by passing in the count as a parameter. But the other line that is slow is the AddRange in this snippet:

IQueryable<T> a = source.Skip<T>((index) * pageSize).Take<T>(pageSize);
AddRange(a.AsEnumerable());

I don't understand why AddRange is so slow or what I can do to improve it?

The entire class source listing is

public class PagedList<T> : List<T>, IPagedList<T>
{
    public PagedList(IEnumerable<T> source, int index, int pageSize)
     : this(source, index, pageSize, null)
    {
    }

    public PagedList(IEnumerable<T> source, int index, int pageSize, int? totalCount)
    {
        Initialize(source.AsQueryable(), index, pageSize, totalCount);
    }

    public PagedList(IQueryable<T> source, int index, int pageSize)
     : this(source, index, pageSize, null)
    {
    }

    public PagedList(IQueryable<T> source, int index, int pageSize, int? totalCount)
    {
        Initialize(source, index, pageSize, totalCount);
    }

    #region IPagedList Members

    public int PageCount { get; private set; }
    public int TotalItemCount { get; private set; }
    public int PageIndex { get; private set; }
    public int PageNumber { get { return PageIndex + 1; } }
    public int PageSize { get; private set; }
    public bool HasPreviousPage { get; private set; }
    public bool HasNextPage { get; private set; }
    public bool IsFirstPage { get; private set; }
    public bool IsLastPage { get; private set; }

    #endregion

    protected void Initialize(IQueryable<T> source, int index, 
            int pageSize, int? totalCount)
    {
        //### argument checking
        if (index < 0)
        {
            throw new ArgumentOutOfRangeException("PageIndex cannot be below 0.");
        }
        if (pageSize < 1)
        {
            throw new ArgumentOutOfRangeException("PageSize cannot be less than 1.");
        }

        //### set source to blank list if source is null to prevent exceptions
        if (source == null)
        {
            source = new List<T>().AsQueryable();
        }

        //### set properties
        if (!totalCount.HasValue)
        {
            TotalItemCount = source.Count();
        }
        PageSize = pageSize;
        PageIndex = index;
        if (TotalItemCount > 0)
        {
            PageCount = (int)Math.Ceiling(TotalItemCount / (double)PageSize);
        }
        else
        {
            PageCount = 0;
        }
        HasPreviousPage = (PageIndex > 0);
        HasNextPage = (PageIndex < (PageCount - 1));
        IsFirstPage = (PageIndex <= 0);
        IsLastPage = (PageIndex >= (PageCount - 1));

        //### add items to internal list
        if (TotalItemCount > 0)
        {
            IQueryable<T> a = source.Skip<T>((index) * pageSize).Take<T>(pageSize);
            AddRange(a.AsEnumerable());
        }
    }
}

Answer 1

It's probably not AddRange that is slow, it's probably the query over the source. The call to AsEnumerable() will come back immediately but the query actually won't be executed until inside of the AddRange call when the sequence is actually enumerated.

You can prove this by changing this line:

AddRange(a.AsEnumerable());

to:

T[] aa = a.ToArray();
AddRange(aa);

You will probably see that the ToArray() call is what takes most of the time because this is when the query is actually executed. Now why that is slow is anyone's guess. If you're using LINQ to SQL or Entity Framework, you can try profiling the database to see where the bottleneck is. But it's probably not the PagedList class.

Answer 2

Why don't you just make a an IEnumerable? Then you won't have to call AsEnumerable() at all.

Although come to think of it, the query doesn't actually execute until AsEnumerable is called, so it probably won't make any difference here.