Entity Framework: Reusable Paging Method

OBSOLETE… sort of. Found an issue with this and am posting the issue today. (07/21/2009)

A while back I decided to show some paging stuff and I forgot that recently, well ok in the last few months, I actually put together a sort of generic extension method to take care of most of the paging situations I was running into. Reason being is I got tired of writing the same method over and over. I figured I’d share it in hopes that somehow, somewhere, it would keep someone’s dream alive. That and I figured I needed to make up for not posting for a while… which to some of you might have been a good thing.

Anywho, it’s not to difficult actually. Small note though, when reading the code you will see the TotalCountOfPages method and the GetRealPage method. If you can’t figure out what those are then I just don’t know what to do. (HINT: They are linked somewhere in this very post! Side note: This post is on track to match the normal self linking per post quota on atypical Codding Horror post)

public static IList<K> GetListForGrid<T, K>
(
  this ObjectQuery<T> query,  //Extension method syntax... basically takes in any SomeEntities.SomeTableRepresentation
  Expression<Func<T, Boolean>> somethingEqualsSomething,  //Where clause
  Expression<Func<T, K>> selectClause,
  Expression<Func<K, IComparable>> orderBy,  //Turns out order by just needs anything that is IComparable
  Int32 pageNumber,
  Int32 numberToShow,
  out Int32 realPage,
  out Int32 totalCountOfPages
)
{
   IList<K> returnValue;
   //Get the total count of the items.  Need this to do proper paging.
   Int32 totalItemCount =
      query
      .Count
      (
         somethingEqualsSomething
      );

   totalCountOfPages = TotalCountOfPages(totalItemCount, numberToShow);
   realPage = GetRealPage(totalCountOfPages, pageNumber);

   returnValue = query
   .Where
   (
      somethingEqualsSomething
   )
   .Select
   (
      selectClause
   )
   .OrderBy(orderBy)
   .Skip(numberToShow * realPage)
   .Take(numberToShow)
   .ToList();

   return returnValue;
}

As you can see there are a lot of expressions going on and that’s the way this works. It takes care of the annoying stuff that you would have to copy an paste while you supply it the stuff that changes like what you want to select and what the needed items have to match to be selected. Even has something for an order by. What the usage look like?

  ToolEntities.Tools.GetListForGrid
  (
    something => something.SomethingId == passedInSomethingId,  //Where clause
    found => found,  //Select: Simple here.  Usually I use classes to hold info... next post
    found => found.Name,  //Order by
    pageNumber,
    amountToShow,
    out realPage,
    out totalCountOfPages
  );

Really simple, eh?

Why totalCountOfPages and RealPage out? Well the whole idea is that this infomation is valuable to the picker control you will be constructing outside this method call. RealPage is nice for showing what page the pager is on and how to figure out what the previous and next pages will be. TotalCountOfPages is useful for the last page possible. Now the nice thing about the RealPage method is that it won’t give you a non existent page. It will only give you the best possible page if the pageNumber parameter is over the possible amount of pages.

Also do notice that this is an extention method meant to be used with the Entity Framework’s built in system of handling the Table representations say ToolEntities.Tools.(Method will show up here in intellisense). This makes it cleaner to me than having to pass in the actual current context. As in:

GetListForGrid(ToolEntities.Something, …) vs ToolEntites.Something.GetListForGrid(…)

In the end, either way is fine.

Paging and the Entity Framework, Skip, and Take Part 4

Get the total count of pages. | Get the real page number. | Using Skip and Take to Page | The Actual Paging Controls

Now onto the fourth part of this epic saga that George Washington once called, “The most astounding exercise in futility”

Ok so let’s say you have a grid, items to fill it with, and four buttons. Each button has a direction, say one back, all the way back, one forward, all the way forward. No big deal. Now you want them to page correctly when used. After all, it’s always a bonus when things go right. First you need to set the CommandArguments on the buttons themselves to whatever page they are responsible for.

private void SetAndBind(Int32 pageNumber)
{
  Int32 realPage;
  Int32 totalCount;

  IList<ToolItem> inviteList =
    ToolClass
    .GetSomeTools("Sean", 20, pageNumber, out realPage, out totalCountOfPages);

  btnFullBack.CommandArgument = Convert.ToString(0);
  btnOneBack.CommandArgument = Convert.ToString(realPage - 1);
  btnOneForward.CommandArgument = Convert.ToString(realPage + 1);
  btnFullForward.CommandArgument = Convert.ToString(totalCountOfPages);

  BindGrid(inviteList);
}

First off, GetSomeTools is just a method that is the same as I presented in the third installment of this epic saga that George Washington once called, “The most astounding exercise in futility”. If you’ve been reading up to this point, then you’ll know that. If not, you might have some reading to do.

So, here’s a simple method used to get the info we need (using the page number) and set the buttons. As seen before, realPage is the actual possible page (In case someone passed in page 21 when there are only 20 pages) and totalCountOfPages gives us how many possible pages there are. This makes setting the button CommandArguments cake. Now for the button event:

  private void btnPaging_Click(Object sender, EventArgs e)
  {
    SetAndBind(Convert.ToInt32(((Button)sender).CommandArgument));
  }

And then you just set all the click events to that:

  btnFullBack.Click += btnPaging_Click;
  btnOneBack.Click += btnPaging_Click;
  btnOneForward.Click += btnPaging_Click;
  btnFullForward.Click += btnPaging_Click;

And boom, you’re pretty much set.

Paging and the Entity Framework, Skip, and Take Part 2

Get the total count of pages. | Get the real page number. | Using Skip and Take to Page | The Actual Paging Controls

So in part one I posted the method to find the total count of pages you’ll need so that you don’t go too far while paging. Now it’s about trying to get the best possible page number despite what’s passed in.

Here’s the situation, when you are paging up or down, you want to make sure you don’t go lower than 0 or higher than whatever the total count of pages is. Sometimes though, things go wrong. They go very wrong. It’s possible that your ui design will allow for any number to be passed in as the page number (Too many reasons this could happen to bother with). Say you have only 10 possible pages but the number 12 gets passed in. Well you either allow it to grab a non existant page 12 or you have a method to determine the best possible choice at this point (10). Turns out it’s idiot simple… what? Were you expecting anything different?

public static Int32 GetRealPage(Int32 totalCountOfPages, Int32 pageNumber)
{
  //pageNumber and totalCountOfPages have to be above 0 or problems
  if (pageNumber > 0 && totalCountOfPages > 0)
  {
    //If page number is higher than the possible count of pages,
    //then you just need the total count to be the new page number...
    //but there's one more step
    pageNumber = totalCountOfPages < pageNumber ? totalCountOfPages : pageNumber;
    //If by chance the pageNumber now is the same as totalCountOfPages
    //(Meaning it was larger than totalCountOfPages originally)
    //1 has to be subtracted to make sure it's inline with a 0 based system where
    //page 1 is actually 0.  This will make more sense when using skip and take in
    //the next post.
    pageNumber = totalCountOfPages != pageNumber ? pageNumber : totalCountOfPages - 1;
  }
  else
  {
    pageNumber = 0;
  }

  return pageNumber;
}

Ok so I’m going to claim this is amazing stuff, but it will come in handy with the next post.

Paging and the Entity Framework, Skip, and Take Part 1

Get the total count of pages. | Get the real page number. | Using Skip and Take to Page | The Actual Paging Controls

So something I’ve been doing a lot of lately is making quite possibly the best thing ever: String cheese wrapped in turkey bacon. But when I’m not doing that, I am working with a lot of ListViews and the entity framework. Now I know there are “built in” paging controls but I just haven’t liked what I’ve seen. So I took it upon myself one day to develop a repeatable system for paging. Say you have users and a bunch of invites that are attached to the users. You want to show a list of invites for a particular user, but you want a ListView that doesn’t show every invite… ie you need a pager. Well just so happens I have a solution, but as always you should consult a doctor before use.

First off you need a method to get the total count of pages meaning what the ceiling is when you page upwards. After all, if you go over the possible count of pages, you’ll just get no items returned and look kind of dumb.

There are three situations you have to look out for: You have less items that the required items per page (Say 10 rows but you display 20 row per page), you have a perfect division (20 rows and 20 row per page or 40 rows and 20 rows per page, ect), you have an uneven division (21 rows and 20 rows per page). First two are easy, third isn’t hard exactly but there are some catches.

public static Int32 TotalCountOfPages(Int32 rowCount, Int32 neededPageSize)
{
  Int32 returnValue;
  if(rowCount > 0)
  {
    if (rowCount <= neededPageSize)
    {
      returnValue = 1;
    }
    else
    {
      if ((rowCount % neededPageSize) == 0)
      {
        returnValue = rowCount / neededPageSize;
      }
      else
      {
        Decimal convertedPageSize =
         Convert.ToDecimal(neededPageSize);
        Decimal convertedRowCount =
         Convert.ToDecimal(rowCount);
        Decimal resultRounded =
          Math.Round(convertedRowCount / convertedPageSize);
        Decimal resultNonRounded =
          convertedRowCount / convertedPageSize;

        if (resultRounded < resultNonRounded)
        {
           returnValue =
            Convert.ToInt32(resultRounded + 1);
        }
        else
        {
           returnValue =
            Convert.ToInt32(resultRounded);
        }
      }
    }
  }
  else
  {
    returnValue = 0;
  }
  return returnValue;
}

Ok so first off, I assume this one is pretty obvious:

  if(rowCount > 0)

If there aren’t any rows, the there can’t be a page count.

Next take care the less rows than being shown per page:

  if (rowCount <= neededPageSize)
  {
    returnValue = 1;
  }

Simple enough. Now for the second part, a perfect division between rows and rows to show:

 if ((rowCount % neededPageSize) == 0)
 {
    returnValue = rowCount / neededPageSize;
 }

Basically, for those who don’t know mod or the % operator, that means there is no remainder. If there were, the result of rowCount % neededPageSize would not be 0 since Mod basically means “Give me what’s left over when I divide something by something else.”

Ok, this is where it gets a little messy as I have yet to find a good way to round a number up since, far as I know, there’s no way to do it with the .Net lib’ary. So, I had to come up with something clever… and when that failed I came up with this:

 Decimal convertedPageSize =
    Convert.ToDecimal(neededPageSize);
 Decimal convertedRowCount =
    Convert.ToDecimal(rowCount);
  Decimal resultRounded =
    Math.Round(convertedRowCount / convertedPageSize);
  Decimal resultNonRounded =
    convertedRowCount / convertedPageSize;

  if (resultRounded < resultNonRounded)
  {
      returnValue =
          Convert.ToInt32(resultRounded + 1);
  }
  else
  {
     returnValue =
       Convert.ToInt32(resultRounded);
  }

Ok so what’s going on here? First off, because trying to divide an integer by an integer gives me an integer, I had to covert some stuff to decimal. Why is that? When I divide them, I need to know if the number after the decimal is between 1 and 1.5 since Math.Round will round down in that case killing my ability to tell if I have enough pages.

Example: 19 rows and 10 per page will give me a 1.9 which Round will make 2. This is perfect since I need two pages to show the 19 rows. What if I have 14 rows and 10 per page? I get 1 from rounding. Uh oh. I really need two pages.

How do I get around this? Get the rounded version and the unrounded version. From there I know that if the unrounded version is greater than the rounded version I need an extra page.

So 14 rows / 10 per page = 1.4 unrounded and 1 rounded. 1.4 > 1, so that 1 page isn’t going to do it. I need to add one more page. Now if it 19 rows/10 per page I’ll get 1.9 unrounded and 2 rounded. 2 pages is exactly what I need and I don’t have to add anything.

Next post I’ll show the next needed method: How to tell if the page number being sent in (From the pager/UI) is actually valid. If not, then grab the last possible amount of records.