Tuesday 26 January 2016

Paged IQueryable ObjectContext EntityFramework

The following article displays how we can achieve retrieving data from EntityFramework using paged results with ObjectContext and sticking inside IQueryable<T> Let's review the extension mehod first:

 public static class EntityExtensions
    {

        public static IQueryable<TEntity> PagedResult<TEntity, TKey>(
            this IQueryable<TEntity> query, 
            Func<TEntity, TKey> sortingFunc, 
            int pageIndex = 1,
            int pageSize = 20)
        {
            var pagedResult = query.OrderBy(sortingFunc)
                .Skip(Math.Max(pageIndex - 0, 0) * pageSize)
                .Take(pageSize);
            return pagedResult.AsQueryable(); 
        }

}

And using AdventureWorks2008R2 database, here is some sample query that shows how we can use this query extension in Linq to Entities:

   using (var ctx = new AdventureWorks2008R2Entities())
   {
                var mountainStuff = from product in ctx.Products
                               where product.Name.Contains("Mountain")
                               select product;
                var firstMountainStuffPage = mountainStuff.PagedResult(p => p.Name, 1, 20);

                foreach (var item in firstMountainStuffPage)
                    Console.WriteLine(item.Name);
   }

            Console.WriteLine("Press any key to continue ..");
            Console.ReadKey();

Output

LL Mountain Frame - Black, 42
LL Mountain Frame - Black, 44
LL Mountain Frame - Black, 48
LL Mountain Frame - Black, 52
LL Mountain Frame - Silver, 40
LL Mountain Frame - Silver, 42
LL Mountain Frame - Silver, 44
LL Mountain Frame - Silver, 48
LL Mountain Frame - Silver, 52
LL Mountain Front Wheel
LL Mountain Handlebars
LL Mountain Pedal
LL Mountain Rear Wheel
LL Mountain Rim
LL Mountain Seat Assembly
LL Mountain Seat/Saddle
LL Mountain Tire
ML Mountain Frame - Black, 38
ML Mountain Frame - Black, 40
ML Mountain Frame - Black, 44
Press any key to continue ..

Conclusion

So there we are, we now have a query that we can reuse to get our paged result and we can pass in our sorting key. So now we can retrieve paged data from for example queries returning large result sets and only display a single page at a time, supporting quicker fetches from the server for clients, retrieving less data and support mobile clients better by getting data pagewise.