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.

Paged result with EntityFramework ObjectContext

Paged results are possible using Entity Framework. Sometimes it is desirable to create a utility method to support paged results in a simple way as an extension method. Let's look at how to achieve this. First we download the AdventureWorks 2008 R2 Sales database from: Download AdventureWorks 2008 R2 Sales After downloading the MDF file, restoring it should be easy. Open up SQL Management Studio and create a new query window. Next up, insert the following SQL:

CREATE DATABASE AdventureWorks2008R2  ON (FILENAME = '{drive}:{Folder}') 
FOR ATTACH_REBUILD_LOG

Note that {Drive} here can be c:\ and {Folder} here can be:

 
c:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\DATA

We should have now our data and we can create a new Console Project in Visual Studio and choose adding an ADO Entity Data Object Model to our project, located under Data. Choose to generate from database and choose the Production.Product table in AdventureWorks2008 database. We can then add an EntityFramework 6.x EntityObject Generator. If you cannot locate this kind of item, download the Extension for Visual Studio from here: EF 6.x EntityObject Generator for C# This is necessary as Microsoft has defaulted to DbContext in EntityFramework 6. To support ObjectContext, we choose to use this extension. Finally we can code our Paged result extension method! Let's define the extension method next:

   public static List<TEntity> PagedResult<TEntity, TKey>(
            this ObjectContext ctx,
            Func<TEntity, TKey> sortingfunc,
            int pageIndex,
            int pageSize = 20)
            where TEntity : EntityObject
        {
            var result = ctx.CreateObjectSet<TEntity>().OrderBy(sortingfunc).Skip(Math.Max(pageIndex - 1, 0) * pageSize).Take(pageSize);
            return result.ToList();
        }

Note that our method did not require that much code. We allow to pass in our ordering member or column of our entity and we choose a default page size of 20 which can be adjusted. We specify the entity type (table) and we specify the page index. You can choose a page index of one to this method and that means the first page. This in fact is technically page index zero and we use the Max method here to protect inputting negative indexes. Let's test this extension method out!

 static void Main(string[] args)
        {

            using (var ctx = new AdventureWorks2008R2Entities())
            {
                var prods = ctx.PagedResult<Product, string>(x => x.Name, 2);


                foreach (var p in prods)
                    Console.WriteLine(p.Name);
            }

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

And we get the desired result:

This may be readily inspected using the AdventureWorks2008 Sales database and select the content from the Production.Product table. So there we are, a reusable method to choose paged contents from a database using Entity Framework and ObjectContext. I prefer ObjectContext instead of DbContext because it provides more lowlevel functionality compared to DbContext. (Although DbContext is more convenient to use).

Create your very own generic GetById method using LINQ Expression Trees

Working with LINQ Expression Trees is sometimes somewhat a tough challenge for C#-programmers, since they usually do not work with Expression trees directly. But LINQ Expression Trees makes it possible to create highly dynamic functions and supports generics quite nicely. Let's create our very own generic GetById method using Linq Expression trees! First off, let's define a simple POCO and use the System.ComponentModel.DataAnnotations.KeyAttribute:

using System.ComponentModel.DataAnnotations;

namespace GetByIdLinqExpression
{
   
    public class ChoccieBar
    {

        [KeyAttribute]
        public int ItemStockId { get; set; }

        public string Title { get; set; }

        public int Qty { get; set; }

        public decimal ItemPrice { get; set; }


    }
}

Okay, let's then define our generic GetById method that will support LINQ expression trees:

using System.ComponentModel.DataAnnotations;
using System.Linq;
using System.Linq.Expressions;
using System;
using System.Collections.Generic;
using System.Linq; 

namespace GetByIdLinqExpression
{
   
    public static class LinqExtensions
    {

        public static TEntity GetById<TEntity>(this IEnumerable<TEntity> source, object primaryKey)
        {
            var itemFound = source.AsQueryable().SingleOrDefault<TEntity>(GetByKeyExpression<TEntity>(primaryKey));
            return itemFound;
        }

        private static Expression<Func<TEntity, bool>> GetByKeyExpression<TEntity>(object primaryKey)
        {
            var primaryKeyProperty = typeof(TEntity).GetProperties()
                .First(p => p.GetCustomAttributes(typeof(KeyAttribute), false).Count() > 0); 

            //Create entity => portion of lambda expression 
            ParameterExpression parameter = Expression.Parameter(typeof(TEntity), "element");

            //Create entity.Id portion of lambda expression 
            MemberExpression property = Expression.Property(parameter, primaryKeyProperty.Name);

            //Create 'id' portion of lambda expression 
            var equalsTo = Expression.Constant(primaryKey);

            //Create entity.Id == 'id' portion of lambda expression 
            var equality = Expression.Equal(property, equalsTo);

            //finally create the entire expression: entity => entity.Id = 'id' 
            Expression<Func<TEntity, bool>> retVal = Expression.Lambda<Func<TEntity, bool>>(equality,
                new[] { parameter });

            return retVal;
        }

    }

}

Note that in our customized extension method we will first take the IEnumerable collection and use the AsQueryable method, since we will use Linq Expression trees. Next we have our own private method that builds up the necessary lambda expression using parameterexpression, memberexpression, constantexpression and equal or equality expression before finally handing it over to a lambdaexpression. If you find it hard to follow, note that you will get nice tooltips as you debug the method. Learning how to assemble for example lambda expressions in Linq expression trees is a must. And yes, the code can quickly grow. The key point is that once you first got it right, you can unlock the complexity using simple calls such as GetById shown in the unit tests next. And of course other, more complex queries can be built up by simpler ones such as this. And let's then create some unit tests to test out this little baby:

    [TestClass]
    public class UnitTest1
    {
       
        [TestMethod]
        public void GetByIdDoesNotThrow()
        {
            var choccieBars = new List
            {
                new ChoccieBar { ItemStockId = 1, Qty = 5200, Title = "Marsbar", ItemPrice = 2.0M },
                new ChoccieBar { ItemStockId = 2, Qty = 5200, Title = "Milky Way", ItemPrice = 1.75M },
                new ChoccieBar { ItemStockId = 3, Qty = 5200, Title = "Bounty", ItemPrice = 2.25M },
                new ChoccieBar { ItemStockId = 4, Qty = 5200, Title = "Lion", ItemPrice = 1.75M }
            };

            var choccieFound = choccieBars.GetById(4);
            Assert.IsNotNull(choccieFound); 

        }

    }

And the test passes. Happy LINQ Expression coding! Remember that using Linq Expression trees there is a lot of things you can achieve in C#! Only creativity can stop you from what extension methods using linq expression trees can be made! And Linq Expression trees might seem difficult, but get started coding them and you soon will find out that they are fun to work with!