Entity Framework Recipe: Alphabetical Paging In ASP.NET MVC

This article looks at using the alphabet for paging data in MVC 5 with Entity Framework 6. Where it differs from most existing articles on alphabetical paging is that this article looks at generating paging links from the data instead of the alphabet. It also features a Bootstrap-friendly helper for generating the paging links.

Generating paging links directly from the alphabet is not necessarily a bad thing, but if there is no data to display against certain letters, your users will find themselves clicking links for no reason. You wouldn't render numerical paging links that yield no results, so why do it with the alphabet? I believe that it is better from a usability point of view to either refrain from displaying links that generate no results or to display them but make them inactive. Either way, you need to query the data to determine which links have matching results.This example uses the Northwind database, specifically data from the Products table. The default Northwind database contains no products beginning with the letters D, H, X or Y. Therefore it will serve admirably to illustrate the point about managing paging links that return no data.

The view needs a ViewModel:

public class AlphabeticalPagingViewModel
{
    public List<string> ProductNames { get; set; }
    public List<string> Alphabet
    {
        get
        {
            var alphabet = Enumerable.Range(65, 26).Select(i => ((char)i).ToString()).ToList();
            alphabet.Insert(0, "All");
            alphabet.Insert(1, "0-9");
            return alphabet;
        }
    }
    public List<string> FirstLetters { get; set; }
    public string SelectedLetter { get; set; }
    public bool NamesStartWithNumbers
    {
        get
        {
            var numbers = Enumerable.Range(0, 10).Select(i => i.ToString());
            return FirstLetters.Intersect(numbers).Any();
        }
    }
}

There's a List<string> for the product names to be displayed. The Alphabet property is populated with all the letters of the alphabet and has two further values prepended to the List - one to represent an "All" option and another to represent names beginning with numbers. The FirstLetter property holds the first letters/characters of all the existing products. The SelectedLetter property will be used to represent the value of the paging link that's clicked. The final property is a bool which will return true if any of the FirstLetters values are numbers. In this example, none of the product names begin with numbers, but if the example included e.g. company names like 3M, the property value would be true.

The model is instantiated in the Index action:

public ActionResult Index(string selectedLetter)
{
    var model = new AlphabeticalPagingViewModel {  SelectedLetter = selectedLetter };
    using (var context = new EFRecipeContext())
    {
        model.FirstLetters = context.Products
            .GroupBy(p => p.ProductName.Substring(0, 1))
            .Select(x => x.Key.ToUpper())
            .ToList();

        if (string.IsNullOrEmpty(selectedLetter) || selectedLetter == "All")
        {
            model.ProductNames = context.Products
                .Select(p => p.ProductName)
                .ToList();
        }
        else
        {
            if (selectedLetter == "0-9")
            {
                var numbers = Enumerable.Range(0, 10).Select(i => i.ToString());
                model.ProductNames = context.Products
                    .Where(p => numbers.Contains(p.ProductName.Substring(0, 1)))
                    .Select(p => p.ProductName)
                    .ToList();
            }
            else
            {
                model.ProductNames = context.Products
                    .Where(p => p.ProductName.StartsWith(selectedLetter))
                    .Select(p => p.ProductName)
                    .ToList();
            }
        }
    }
    return View(model);
}

The link that was clicked is fed into the method as a parameter - the selectedLetter. It's value is used to populate its respective property in the newly instantiated ViewModel. The first letters of all existing products are obtained from the database using a LINQ query. Instinct might lead you to try using a query that includes the Distinct() extension method, but in fact, GroupBy is the best operator for this type of query. Data is grouped by the first letter of each ProductName, which then becomes the Key for each group. That value is then projected into the List<string> that forms the FirstLetter property of the ViewModel.

The SelectedLetter value is then examined to establish what data to return. If it is empty or its value is "All", unfiltered data is returned. If the selectedLetter value is "0-9", all data that begins with any character between 0 and 9 is returned. Otherwise only data that starts with the selected letter is returned.

The next section of code shows how this data can be displayed in the view:

<ul class="pagination alpha">
    @foreach (var link in Model.Alphabet)
    {
        if (Model.FirstLetters.Contains(link) || (Model.NamesStartWithNumbers && link == "0-9") || link == "All")
        {
            if (Model.SelectedLetter == link || Model.SelectedLetter.IsEmpty() && link == "All")
            {
                <li class="active"><span>@link</span></li>
            }
            else
            {
                <li>@Html.ActionLink(link, "Index", new {selectedLetter = link})</li>
            }
        }
        else
        {
            <li class="inactive"><span>@link</span></li>
        }
    }
</ul>
<ul>
    @foreach (var item in Model.ProductNames)
    {
        <li>@item</li>
    }
</ul>

I have chosen to render the complete alphabet, but only to make letters that have matching data active links. So this code loops through all the entries in the Alphabet collection and checks each in turn. If the current letter of the Alphabet collection is also in the FirstLetters collection, or the current letter is "0-9" and there are entries that start with numbers, or the current letter is the "All" option, then a link should be rendered. Otherwise the list item is given a class of inactive and the letter is rendered in a span instead of an anchor element. If the current letter is the same as the SelectedLetter value, or there is no selected letter and the letter is "All", the link is given a class of active, representing the current page of data. Finally, the product names are rendered to the browser.

MVC5 With EF6 Alphabetical Paging

 

The design of the paging links is controlled by simply applying the pagination class to the ul element that contains the list items and including Bootstrap.

Html Helper

If you want to add alphabetical paging to other pages in your site, or if you simply prefer not to have so much looping in your views, you can create an Html Helper to generate the paging links. Here's one I made earlier:

public static class HtmlHelpers
{
    public static HtmlString AlphabeticalPager(this HtmlHelper html, string selectedLetter, IEnumerable<string> firstLetters, Func<string, string> pageLink)
    {
        var sb = new StringBuilder();
        var numbers = Enumerable.Range(0, 10).Select(i => i.ToString());
        var alphabet = Enumerable.Range(65, 26).Select(i => ((char)i).ToString()).ToList();
        alphabet.Insert(0, "All");
        alphabet.Insert(1, "0-9");

        var ul = new TagBuilder("ul");
        ul.AddCssClass("pagination");
        ul.AddCssClass("alpha");

        foreach (var letter in alphabet)
        {
            var li = new TagBuilder("li");
            if (firstLetters.Contains(letter) || (firstLetters.Intersect(numbers).Any() && letter == "0-9") || letter == "All")
            {
                if (selectedLetter == letter || selectedLetter.IsEmpty() && letter == "All")
                {
                    li.AddCssClass("active");
                    var span = new TagBuilder("span");
                    span.SetInnerText(letter);
                    li.InnerHtml = span.ToString();
                }
                else
                {
                    var a = new TagBuilder("a");
                    a.MergeAttribute("href", pageLink(letter));
                    a.InnerHtml = letter;
                    li.InnerHtml = a.ToString();
                }
            }
            else
            {
                li.AddCssClass("inactive");
                var span = new TagBuilder("span");
                span.SetInnerText(letter);
                li.InnerHtml = span.ToString();
            }
            sb.Append(li.ToString()); 
        }
        ul.InnerHtml = sb.ToString();
        return new HtmlString(ul.ToString());
    }
}

The helper moves the construction of the alphabet away from the ViewModel and into the body of the method. It also removes the need for the NamesStartWithNumber property, so the revised ViewModel is slimmer:

public class AlphabeticalPagingViewModel
{
    public List<string> ProductNames { get; set; }
    public List<string> FirstLetters { get; set; }
    public string SelectedLetter { get; set; }
}

The view itself is also a lot slimmer:

@Html.AlphabeticalPager(Model.SelectedLetter, Model.FirstLetters, x => Url.Action("Index", new { selectedLetter = x }))
<ul>
    @foreach (var item in Model.ProductNames)
    {
        <li>@item</li>
    }
</ul>

This article has shown how to use the LINQ GroupBy operator to get the first letters of existing data so that the generation of alphabetical paging links can be controlled to only include those that will yield results. In addition, the article presented a reusable Html Helper that takes care of generating the paging links while keeping both ViewModel and View uncluttered.