Thursday 31 December 2015

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).
Share this article on LinkedIn.

1 comment:

  1. It is true Power Automate services provides connectors for popular databases like Microsoft SQL Server, MySQL, and PostgreSQL. Developers can leverage these connectors to create workflows that integrate with databases, enabling seamless data transfer and synchronization between different systems and databases

    ReplyDelete