Efficiently Displaying Hierarchical Data With The jQuery Accordion In Razor Web Pages

A frequent requirement is to display data hierarchically, such as products by category. This article shows how to use LINQ to Objects and the jQuery Accordion to display the result of a database query in such a fashion within Razor Web Pages.

One of the problems that people get into when attempting to present hierarchical data on a web page is to fall into the trap if executing multiple database queries to obtain data. If they want to display a list of products by category, they first obtain all of the categories, and then iterate the categories and retrieve a list of products for each category. The code might look something like this:

@foreach(var category in db.Query("SELECT * FROM Categories")){
    <h3>@category.CategoryName</h3>
    foreach(var product in db.Query("SELECT * FROM Products WHERE CategoryId = @0", category.CategoryId)){
        <div>@product.ProductName</div>
    }
}

This is known as the n + 1 select problem, when 1 select query is executed to get the categories, then n further select queries are executed to get the products for each category. Generally, developers are encouraged to minimise the number of times their application makes calls to a database as this can be a common cause of performance issues. Ideally, you should get your data in one shot and then shape it appropriately. A one shot retrieval of data might result in a structure that looks like this:

Ideally, that should be shaped, so that each category's products are grouped together:

LINQ offers a GroupBy method that does just that - it groups elements of a sequence together based on a specified key. The sequence is the output from the database query, and the key around which items are grouped is the name of the category. The code looks like this:

@{
    var db = Database.Open("Northwind");
    var sql = @"SELECT CategoryName, ProductId, ProductName FROM Categories 
                INNER JOIN Products ON Products.CategoryId = Categories.CategoryId 
                ORDER BY Categories.CategoryId";
    var categories = db.Query(sql).GroupBy(p => p.CategoryName);
}

Here's how that looks to the Visual Studio debugger:

 

Data is grouped around the 8 categories in the Northwind database. Getting this on the web page is a matter of two loops - one that iterates the groups, and another inner loop that iterates the content of the group:

@foreach (var category in categories) {
    <h3>@category.Key</h3>
    <div>
    @foreach (var product in category.OrderBy(p => p.ProductName)) {
        <div>@product.ProductName</div>
    }
    </div>
}

The result is exactly as desired:

However, it is a long list, and might benefit from some UI enhancements like the jQuery accordion. The accordion hides all but one section of structured content until the user is ready to see it. It is available as part of the UI download, and is very easy to deploy to the page. Each section of data needs a header-content pair of elements. The example above has that; the category name appears in an h3 element and the list of products appear in a div. It needs a little modification to work with the accordion - a container and some links:

<div id="accordion">
    @foreach (var category in categories) {
        <h3><a href="#">@category.Key</a></h3>
        <div>
        @foreach (var product in category.OrderBy(p => p.ProductName)) {
            <div>@product.ProductName</div>
        }
        </div>
    }
</div>

You can download the accordion and associated styles or link to one of the CDN hosters, and then specify one command to apply the accordion effect to the div labelled with the id of accordion and its contents:

<link href="http://ajax.googleapis.com/ajax/libs/jqueryui/1.8/themes/base/jquery-ui.css" rel="stylesheet" type="text/css"/>
<script src="http://ajax.googleapis.com/ajax/libs/jquery/1.7.2/jquery.min.js"></script>
<script src="http://ajax.googleapis.com/ajax/libs/jqueryui/1.8/jquery-ui.min.js"></script>
<script type="text/javascript">
    $(function(){
        $('#accordion').accordion({ autoHeight: false });
    });
</script>

And finally here's how the result looks:

This purpose of this article was to illustrate how to use the LINQ GroupBy operator to prepare data in such a way that displaying it on a Razor Web Page hierarchically is simple and efficient. Finally, the jQuery Accordion was used to apply some user friendly features to the resulting data.

A GitHub repo is available containing the code as a sample web site

 

Date Posted: Sunday, June 3, 2012 10:30 AM
Last Updated: Monday, December 17, 2012 9:13 PM
Posted by: Mikesdotnetting
Total Views to date: 19005

6 Comments

Sunday, June 3, 2012 10:35 AM - Keef

Excellent and easy to understand as usual.

Sunday, June 3, 2012 8:18 PM - Zeljko

Great article, the only thing I would change in your code is to move the query to the controller and send the prepared object to the view via a customized viewmodel.

Sunday, June 3, 2012 8:41 PM - Mike

@Zeljko

In MVC, your suggestion is exactly how it should be done. There are, however, no controllers in Razor Web Pages.

Sunday, June 3, 2012 8:45 PM - Zeljko

You're absolutely right. I should have paid more attention to the title of your blog post. Somehow I was under the impression it was an MVC post.

Monday, June 4, 2012 4:49 PM - Maran

Hello Mike,
I can't see the images...

Monday, June 4, 2012 4:56 PM - Mike

@Maran,

Check your firewall rules. They are hosted at livestore.com.
Add your comment

If you have any comments to make about this article, please use this form to do so. Make sure that your comment relates specifically to the article above. More general comments can be posted through the form on the Contact page.

Please note, all comments are moderated, and some may not be published. The kind of things that will ensure your comment is deleted without ever seeing the light of day are as follows:

  • Not relevant to the article
  • Gratuitous links to your own site or product
  • Anything abusive or libellous
  • Spam
  • Anything in a language I don't understand including gibberish.

I do not pass email addresses on to spammers, so a valid one will assist me in responding to you personally if required.

Recent Comments

Allen Michaels 12/17/2014 4:37 PM
In response to Cascading DropDownLists with jQuery and ASP.NET
Fantastic thank you so much!...

Emily 12/17/2014 12:36 PM
In response to Parameterized IN clauses with ADO.NET and LINQ
Thanks, very helpful!!!! Can i use this for multiple in's ????? IN (.....) and IN(...) and IN...

sss 12/16/2014 3:06 PM
In response to Solving the Operation Must Use An Updateable Query error
good...

Gjuro 12/15/2014 10:30 PM
In response to Examining the Edit Methods and Edit View
You have one fromr (and it should be from, I suppose). :-)...

Gjuro 12/15/2014 10:27 PM
In response to Adding Search
Hi, thnx for all this C#->VB translations. Yet, the following code block is (slightly) in error it a...

Scot 12/14/2014 1:39 PM
In response to Entity Framework 6 Recipe - Alphabetical Paging In ASP.NET MVC
Thanks,Mike I found solution....

Gjuro 12/13/2014 10:52 PM
In response to Accessing Your Model's Data from a Controller
The article mentions "Creating an Entity Framework Data Model for an ASP.NET MVC Application" (at is...

Samuel 12/13/2014 8:40 AM
In response to Displaying The First n Characters Of Text
I have failed to use the extension because it throws an error that it doesn't recognise the chop be...

Ignas 12/12/2014 5:11 PM
In response to Cleaner Conditional HTML Attributes In Razor Web Pages
Any suggestions for Html Helper elements with HtmlAttributes, when you need to conditionally set it...

Gautam 12/11/2014 8:50 PM
In response to Validation In Razor Web Pages 2
Hi Mike Is this required for V3, non html helper input...