There are occasions when you want your end users to page records by using something other than just numbers. For example, what if you are viewing a list of business contacts and you know you are looking for someone with the last name of Doe? Do you know what page you could find records starting with a "D" on? In reality, this person's information could be on page 1 or 50. Therefore, wouldn't it make more sense in this scenario to page by people's last name? Absolutely!

Alphabetical paging is actually very simple. We start by building a pager that displays all the letters in the alphabet as hyperlinks. In my application I display all the letters all the time. You could however build more intelligence behind your pager so you only display the letters that have 1 or more records. For example if I had a Contact table with a column title LastName I could use the following query to build my pager:

select distinct( cast( LastName as CHAR(1))) from Contact

I will however warn you that this particular query will generally bypass any indexes you have on the table. Therefore you may want to do this query once and cache it in your application so you can avoid running it multiple times. For this reason, I chose to display all the letters in my pager so I could avoid a clustered index scan and a nasty phone call from my DBA. In any case, here is the code I used to display the pager:

<% for( int i = 65; i <= 90; i++ ) { %>
   <span><%=Html.RouteLink( Convert.ToChar(i).ToString(), 
        new { action="Index", lname = Convert.ToChar(i).ToString() } ) %>
   </span>
<% } %>    
 | <span><%=Html.ActionLink( "All", "Index" )%></span>

Perhaps, you are wondering what is special about the numbers 65 – 90? Well, these are the ASCII codes for the letters in the alphabet. A=65, B=66. C=67 and so on and so on. This code produces a pager that looks like this:

The hyperlink behind each link points to a URL that contains the following pattern:

http://someurl/Contact?lname=A

Now the only thing left to do is to build a action in the controller that takes a parameter named lname. This way when you click on the hyperlink "A" it will search the database for any records where lname like "A%".  If you are familiar with MVC then you know that I will need to add the lname parameter to the Index action because we only specified the controller in the URL.

public ActionResult Index(int? page, string lname, string sidx, string sord) {
    IQueryable<Contact> query;

    if ( String.IsNullOrEmpty(lname) )
        query = repository.FindContacts();
    else
        query = repository.FindContactsByLastName(lname);

    //sort the data
    string sortExpression = (sidx ?? "LastName") + " " + (sord ?? "asc");
    query = query.OrderBy(sortExpression);

    //page the data
    var data = query.ToPagedList(page ?? 1, MySession.PageSize);

    return View(data);
}

In my code you will see that I am using the repository pattern to return a IQueryable list of contacts who have last names that start with a particular pattern. Lets take a detailed look at the FindContactsByLastName method:

public IQueryable<Contact> FindContactsByLastName( string value ) {
    var query = from x in db.Contacts
                    where 
                        x.LastName.StartsWith( value )                    
                    select
                        x;
    return query;
}

The LINQ query above eventually translates into a SQL query that looks like this:

select * from Contact where LastName like 'A%'

I want to point out that I deliberately used the StartsWith function on my LastName column instead of a Contains. This is because I have a index on the LastName column and if I used the Contains function the SQL would have been translated into this instead:

select * from Contact where LastName like '%A%'

The Contains version of the query would not utilize the index because it would have to search the entire contents of the LastName field to find the matching records. Since the index is arranged alphabetically, we use the StartsWith function so we can do an Index Seek instead of an Index Scan. This will ultimately give us better performance and scalability. The only thing left to do is display the records:

<table id="grid">
        <tr>
            <th></th>                                   
            <th>Last Name</th>
            <th>First Name</th>
        </tr>
    <% foreach (var item in Model) { %>    
        <tr>
            <td><%= Html.Encode(item.LastName) %></td>
            <td><%= Html.Encode(item.FirstName) %></td>            
        </tr>    
    <% } %>
</table>