March 2011

Volume 26 Number 03

Data Points - Server-Side Paging with the Entity Framework and ASP.NET MVC 3

By Julie Lerman | March 2011

In my February Data Points column, I showed off the jQuery DataTables plug-in and its ability to seamlessly handle huge amounts of data on the client side. This works well with Web applications where you want to slice and dice large amounts of data. This month, I’ll focus on using queries that return smaller payloads to enable a different type of interaction with the data. This is especially important when you’re targeting mobile applications.

I’ll take advantage of features introduced in ASP.NET MVC 3 and demonstrate how to use these together with efficient server-side paging against the Entity Framework. There are two challenges with this task. The first is to provide an Entity Framework query with the correct paging parameters. The second is to mimic a feature of client-side paging by providing visual clues to indicate that there’s more data to retrieve, as well as links to trigger the retrieval.

ASP.NET MVC 3 has a slew of new features, such as the new Razor view engine, validation improvements and a ton more JavaScript features. The launch page for MVC is at asp.net/mvc, where you can download ASP.NET MVC 3 and find links to blog posts and training videos to help you get up to speed. One of the new features that I’ll use is the ViewBag. If you’ve used ASP.NET MVC previously, ViewBag is an enhancement to the ViewData class and lets you use dynamically created properties.

Another new element that ASP.NET MVC 3 brings to the table is the specialized System.Web.Helpers.WebGrid. Although one of the grid’s features is paging, I’ll use the new grid but not its paging in this example, because that paging is client-side—in other words, it pages through a set of data provided to it, similar to the DataTables plug-in. I’m going to be using server-side paging instead.

For this little app, you’ll need an Entity Data Model to work with. I’m using one created from the Microsoft AdventureWorksLT sample database, but I only need the Customer and SalesOrderHeaders brought into the model. I’ve moved the Customer rowguid, PasswordHash and PasswordSalt properties into a separate entity so that I don’t have to worry about them when editing. Other than this small change, I haven’t modified the model from its default.

I created a project using the default ASP.NET MVC 3 project template. This prepopulates a number of controllers and views, and I’ll let the default HomeController present the Customers.

I’ll use a simple DataAccess class to provide interaction with the model, context and, subsequently, the database. In this class, my GetPagedCustomers method provides server-side paging. If the goal of the ASP.NET MVC application was to allow the user to interact with all of the customers, that would be a lot of customers returned in a single query and managed in the browser. Instead, we’ll let the app present 10 rows at a time and the GetPagedCustomers will provide that filter. The query that I’ll eventually need to execute looks like this:

context.Customers.Where(c => 
c.SalesOrderHeaders.Any()).Skip(skip).Take(take).ToList()

The view will know which page to request and give that information to the controller. The controller will be in charge of knowing how many rows to supply per page. The controller will calculate the “skip” value using the page number and the rows per page. When the controller calls the GetPagedCustomers method, it will pass in the calculated skip value as well as the rows per page, which is the “take” value. So if we’re on page four and presenting 10 rows per page, skip will be 40 and take will be 10.

The paging query first creates a filter that requests only those customers who have any SalesOrders. Then, using LINQ Skip and Take methods, the resulting data will be a subset of those customers. The full query, including the paging, is executed in the database. The database returns only the number of rows specified by the Take method.

The query is composed of a few parts to enable some tricks I’ll add down the road. Here’s a first pass at the GetPagedCustomers method that will be called from the HomeController:

public static List<Customer> GetPagedCustomers(int skip, int take)
    {
      using (var context = new AdventureWorksLTEntities())
      {
        var query = context.Customers.Include("SalesOrderHeaders")
          .Where(c => c.SalesOrderHeaders.Any())
          .OrderBy(c => c.CompanyName + c.LastName + c.FirstName);
        return query.Skip(skip).Take(take).ToList();
      }
    }

The controller Index method that calls this method will determine the number of rows to return using a variable I’ll call pageSize, which becomes the value for Take. The Index method will also specify where to begin based on a page number that will be passed in as a parameter, as shown here:

public ActionResult Index(int? page)
    {
      const int pageSize = 10;
      var customers=DataAccess.GetPagedCustomers((page ?? 0)*pageSize, pageSize);
      return View(customers);
    }

This gets us a good part of the way. The server-side paging is completely in place. With a WebGrid in the Index view markup, we can display the customers returned from the GetPagedCustomers method. In the markup, you need to declare and instantiate the grid, passing in Model, which represents the List<Customer> that was provided when the controller created the view. Then, using the WebGrid GetHtml method, you can format the grid, specifying which columns to display. I’ll only show three of the Customer properties: CompanyName, FirstName and LastName. You’ll be happy to find full IntelliSense support as you type this markup whether you use syntax associated with ASPX views or with the new MVC 3 Razor view engine syntax (as with the following example). In the first column, I’ll provide an Edit ActionLink so that the user can edit any of the Customers that are displayed:

@{
  var grid = new WebGrid(Model); 
}
<div id="customergrid">
  @grid.GetHtml(columns: grid.Columns(
    grid.Column(format: (item) => Html.ActionLink
      ("Edit", "Edit", new { customerId = item.CustomerID })),
  grid.Column("CompanyName", "Company"), 
  grid.Column("FirstName", "First Name"),
  grid.Column("LastName", "Last Name")
   ))
</div>

The result is shown in Figure 1.

Figure 1 Providing Edit ActionLinks in the WebGrid

So far, so good. But this doesn’t provide a way for the user to navigate to another page of data. There are a number of ways to achieve this. One way is to specify the page number in the URI—for example, https://adventureworksmvc.com/Page/3. Surely you don’t want to ask your end users to do this. A more discoverable mechanism is to have paging controls, such as page number links “1 2 3 4 5 …” or links that indicate forward and backward, for example, “<<      >>.”

The current roadblock to enabling the paging links is that the Index view page has no knowledge that there are more Customers to be acquired. It knows only that the universe of customers is the 10 that it’s displaying. By adding some additional logic into the data-access layer and passing it down to the view by way of the controller, you can solve this problem. Let’s begin with the data-access logic.

In order to know if there are more records beyond the current set of customers, you’ll need to have a count of all of the possible customers that the query would return without paging in groups of 10. This is where composing the query in the GetPagedCustomers will pay off. Notice that the first query is returned into _customerQuery, a variable that’s declared at the class level, as shown here:

_customerQuery = context.Customers.Where(c => c.SalesOrderHeaders.Any());

You can append the Count method to the end of that query to get the count of all of the Customers that match the query before paging is applied. The Count method will force a relatively simple query to be executed immediately. Here’s the query executed in SQL Server, from which the response returns a single value:

SELECT 
[GroupBy1].[A1] AS [C1]
FROM ( SELECT 
       COUNT(1) AS [A1]
       FROM [SalesLT].[Customer] AS [Extent1]
       WHERE  EXISTS (SELECT 
              1 AS [C1]
              FROM [SalesLT].[SalesOrderHeader] AS [Extent2]
              WHERE [Extent1].[CustomerID] = [Extent2].[CustomerID]
       )
)  AS [GroupBy1]

Once you have the count, you can determine if the current page of customers is the first page, the last page or something in between. Then you can use that logic to decide which links to display. For example, if you’re beyond the first page of customers, then it’s logical to display a link to access earlier pages of customer data with a link for the previous page, for example, “<<.”

We can calculate values to represent this logic in the data-access class and then expose it in a wrapper class along with the customers. Here’s the new class I’ll be using:

public class PagedList<T>
  {
    public bool HasNext { get; set; }
    public bool HasPrevious { get; set; }
    public List<T> Entities { get; set; }
  }

GetPagedCustomers method will now return a PagedList class rather than a List. Figure 2 shows the new version of GetPagedCustomers.

Figure 2 The New Version of GetPagedCustomers

public static PagedList<Customer> GetPagedCustomers(int skip, int take)
    {
      using (var context = new AdventureWorksLTEntities())
      {
        var query = context.Customers.Include("SalesOrderHeaders")
          .Where(c => c.SalesOrderHeaders.Any())
          .OrderBy(c => c.CompanyName + c.LastName + c.FirstName);
        var customerCount = query.Count();
        var customers = query.Skip(skip).Take(take).ToList();
      
        return new PagedList<Customer>
        {
          Entities = customers,
          HasNext = (skip + 10 < customerCount),
          HasPrevious = (skip > 0)
        };
      }
    }

With the new variables populated, let’s take a look at how the Index method in the HomeController can push them back to the View. Here’s where you can use the new ViewBag. We’ll still return the results of the customers query in a View, but you can additionally stuff the values to help determine what the markup will look like for the next and previous links in the ViewBag. They will then be available to the View at run time:

public ActionResult Index(int? page)
    {
      const int pageSize = 10;
      var customers=DataAccess.GetPagedCustomers((page ?? 0)*pageSize, pageSize);
      ViewBag.HasPrevious = DataAccess.HasPreviousCustomers;
      ViewBag.HasMore = DataAccess.HasMoreCustomers;
      ViewBag.CurrentPage = (page ?? 0);
      return View(customers);
    }

It’s important to understand that the ViewBag is dynamic, not strongly typed. ViewBag doesn’t really come with HasPrevious and HasMore. I’ve just made them up as I’m typing the code. So don’t be alarmed that IntelliSense doesn’t suggest this to you. You can create any dynamic properties you’d like.

If you’ve been using the ViewPage.ViewData dictionary and are curious how this is different, ViewBag does do the same job. But in addition to making your code a little prettier, the properties are typed. For example, HasNext is a dynamic{bool} and CurrentPage is a dynamic{int}. You won’t have to cast the values when you retrieve them later.

In the markup, I still have the customer list in the Model variable, but there’s a ViewBag variable available as well. You’re on your own as you type in the dynamic properties into the markup. A tooltip reminds you that the properties are dynamic, as shown in Figure 3.

Figure 3 ViewBag Properties Aren’t Available Through IntelliSense Because They’re Dynamic

Here’s the markup that uses the ViewBag variables to determine whether or not to display the navigation links:

@{ if (ViewBag.HasPrevious)
  {
    @Html.ActionLink("<<", "Index", new { page = (ViewBag.CurrentPage - 1) })
  }
}
@{ if (ViewBag.HasMore)
   { @Html.ActionLink(">>", "Index", new { page = (ViewBag.CurrentPage + 1) }) 
  }
}

This logic is a twist on markup used in the NerdDinner Application Tutorial, which you can find at nerddinnerbook.s3.amazonaws.com/Intro.htm.

Now when I run the app, I have the ability to navigate from one page of customers to the next.

When I’m on the very first page, I have a link to navigate to the next page but nothing to go to a previous page because there is none (see Figure 4).

Figure 4 The First Page of Customer Has Only a Link to Navigate to the Next Page

When I click the link and navigate to the next page, you can see that there are now links to go to the previous or next page (see Figure 5).

Figure 5 A Single Page of Customers with Navigation Links to Go to Previous or Next Page of Customers

 The next step, of course, will be to work with a designer to make this paging more attractive.

Critical Piece of Your Toolbox

Summing up, while there are a number of tools to streamline client-side paging, such as the jQuery DataTables extension and the new ASP.NET MVC 3 WebGrid, your application needs may not always benefit from bringing back large amounts of data. Being able to perform efficient server-side paging is a critical piece of your toolbox. The Entity Framework and ASP.NET MVC work together to provide a great user experience and at the same time simplify your development task to pull this off.

Julie Lerman is a Microsoft MVP, .NET mentor and consultant who lives in the hills of Vermont. You can find her presenting on data access and other Microsoft .NET topics at user groups and conferences around the world. Lerman blogs at thedatafarm.com/blog and is the author of the highly acclaimed book, “Programming Entity Framework” (O’Reilly Media, 2009). You can follow her at Twitter.com/julielerman.

Thanks to the following technical expert for reviewing this article: Vishal Joshi