Entity Framework/Linq to SQL: Skip & Take

As long as you don't do it like queryable.ToList().Skip(5).Take(10), it won't return the whole recordset.

Take

Doing only Take(10).ToList(), does a SELECT TOP 10 * FROM.

Skip

Skip works a bit different because there is no 'LIMIT' function in TSQL. However it creates an SQL query that is based on the work described in this ScottGu blog post.

If you see the whole recordset returned, it probably is because you are doing a ToList() somewhere too early.

The following works and accomplishes the simplicity I was looking for:

public IEnumerable<Store> ListStores(Expression<Func<Store, string>> sort, bool desc, int page, int pageSize, out int totalRecords)
{
    List<Store> stores = new List<Store>();
    using (var context = new TectonicEntities())
    {
        totalRecords = context.Stores.Count();
        int skipRows = (page - 1) * pageSize;
        if (desc)
            stores = context.Stores.OrderByDescending(sort).Skip(skipRows).Take(pageSize).ToList();
        else
            stores = context.Stores.OrderBy(sort).Skip(skipRows).Take(pageSize).ToList();
    }
    return stores;
}

The main thing that fixed it for me was changing the Func sort parameter to:

Expression<Func<Store, string>> sort

I created simple extension:

public static IEnumerable<T> SelectPage<T, T2>(this IEnumerable<T> list, Func<T, T2> sortFunc, bool isDescending, int index, int length)
{
    List<T> result = null;
    if (isDescending)
        result = list.OrderByDescending(sortFunc).Skip(index).Take(length).ToList();
    else
        result = list.OrderBy(sortFunc).Skip(index).Take(length).ToList();
    return result;
}

Simple use:

using (var context = new TransportContext())
{
    var drivers = (from x in context.Drivers where x.TransportId == trasnportId select x).SelectPage(x => x.Id, false, index, length).ToList();
}

Entity Framework 6 solution here...

http://anthonychu.ca/post/entity-framework-parameterize-skip-take-queries-sql/

e.g.

using System.Data.Entity;
....

int skip = 5;
int take = 10;

myQuery.Skip(() => skip).Take(() => take);