How can I sort the list in my view (IEnumerable) and keep my filtering in place?

Question

I am creating a digital guestbook kiosk program. I have an administrative portion where admins can log in and view all guests who have signed up. At the top of the view for this functionality is an area with text-boxes and drop-down lists where the admin can add their filters. When they hit "filter" the list refreshes to include only items that meet the filtered criteria. This works, however I am now trying to include alphabetical sorting to the list. When someone clicks on the table header "First Name" or "Last Name" it sends a command back to the controller to sort the list either ascending or descending on this column. The only problem is that it gets a new list from the database every time and loses all filters.

I have tried to pass the filtered list as a variable through both the ViewBag and as a session variable. For a while I was getting a "The operation cannot be completed because the DbContext has been disposed" on my list when I tried to iterate over it (using a session variable).

I have tried to follow this tutorial: http://www.asp.net/mvc/overview/getting-started/getting-started-with-ef-using-mvc/sorting-filtering-and-paging-with-the-entity-framework-in-an-asp-net-mvc-application but nothing there showed how to keep the filters after all. After doing hours of trial and error and internet research, I am unsure as to where to go next on solving this problem. I have considered looking for a javascript solution but I am not very good at javascript yet an do not know where to begin looking.

Here is the view code:

    @model IEnumerable<VirtualGuestbook.Models.Guest>
    @using System.Collections;
    @{
        ViewBag.Title = "Index";
        Layout = "~/Views/Shared/_BackendLayout.cshtml";
    }


    <div class="blackserif">
        <p class="maroonLfont">Guests</p>
        <hr />
        <h2>Search by:</h2>
        @using (Html.BeginForm("Index", "Guests", FormMethod.Get))
        {
            <div class="searchblackserif">
                <p>
                    <div id="left30search">
                        <span class="maroonfont">Personal Information</span><br /><br />
                        Name (First or Last): @Html.TextBox("NameString")<br /><br />
                        Phone #: @Html.TextBox("PhoneString")<br /><br />
                        Email: @Html.TextBox("EmailString")
                    </div>
                    <div id="right30search">
                        <span class="maroonfont">Visit Specifics</span><br /><br />
                        After (Date): @Html.TextBox("startDate")<br />(AND / OR)<br />
                        Before (Date): @Html.TextBox("endDate")<br /><br />
                        Minimum Times Visited: @Html.TextBox("VisitString")
                    </div>
                    <div id="middle30search">
                        <span class="maroonfont">Address Specifics</span><br /><br />
                        City: @Html.TextBox("CityString")<br /><br />
                        State: @Html.DropDownList("stateList", "All")<br /><br />
                        ZipCode: @Html.TextBox("ZipString")<br /><br />
                        <input type="submit" value="Filter" />
                    </div>
                </p>
            </div>
        }
        <div id="postSearch">
            <table id="myTable" class="table">
                <thead>
                    <tr class="maroonfont">
                        <th>
                            Prefix
                        </th>
                        <th>
                            First Name
                        </th>
                        <th>
                            Last Name
                        </th>
                        <th>
                            Phone #
                        </th>
                        <th>
                            Email
                        </th>
                        <th>
                            Address (Line 1)
                        </th>
                        <th>
                            Address (Line 2)
                        </th>
                        <th>
                            City
                        </th>
                        <th>
                            State
                        </th>
                        <th>
                            ZipCode
                        </th>
                        <th>
                            College Rating
                        </th>
                        <th>
                            Heard About How?
                        </th>
                        <th>
                            Times Visited
                        </th>
                        <th>
                            Date of Last Visit
                        </th>
                        <th></th>
                    </tr>
                </thead>
                <tbody>
                    @foreach (var item in Model)
                    {
                        <tr>
                            <td>
                                @Html.DisplayFor(modelItem => item.Prefix)
                            </td>
                            <td>
                                @Html.DisplayFor(modelItem => item.FirstName)
                            </td>
                            <td>
                                @Html.DisplayFor(modelItem => item.LastName)
                            </td>
                            <td>
                                @Html.DisplayFor(modelItem => item.Phone)
                            </td>
                            <td>
                                @Html.DisplayFor(modelItem => item.Email)
                            </td>
                            <td>
                                @Html.DisplayFor(modelItem => item.AddressL1)
                            </td>
                            <td>
                                @Html.DisplayFor(modelItem => item.AddressL2)
                            </td>
                            <td>
                                @Html.DisplayFor(modelItem => item.City)
                            </td>
                            <td>
                                @Html.DisplayFor(modelItem => item.State)
                            </td>
                            <td>
                                @Html.DisplayFor(modelItem => item.Zip)
                            </td>
                            <td>
                                @Html.DisplayFor(modelItem => item.Rating)
                            </td>
                            <td>
                                @Html.DisplayFor(modelItem => item.HearAbout)
                            </td>
                            <td>
                                @Html.DisplayFor(modelItem => item.VisitNumber)
                            </td>
                            <td>
                                @{string date = item.datevisited.ToShortDateString();
                                }
                                @date
                            </td>

                            <td>
                                @if (User.IsInRole("S"))
                                {
                                    @Html.ActionLink("Delete", "Delete", new { id = item.GuestID })
                                }
                            </td>
                        </tr>
                    }
                </tbody>

            </table>
        </div>
    </div>

   @section Scripts {
    @Scripts.Render("~/bundles/jqueryval")
    <!-- DataTables -->
    <script type="text/javascript" charset="utf8" src="~/Scripts/jquery.dataTables.js">
        $(document).ready(function () {
            $('#myTable').dataTable();
        });
    </script>
}

And here is the controller code for the index method:

using System;
using System.Collections;
using System.Collections.Generic;
using System.Data;
using System.Data.Entity;
using System.Linq;
using System.Net;
using System.Web;
using System.Web.Mvc;
using VirtualGuestbook.Models;

namespace VirtualGuestbook.Controllers
{
    public class GuestsController : Controller
    {
        private VirtualGuestbookDBEntities db = new VirtualGuestbookDBEntities();
        [Authorize(Roles = "A, E, S")]
        public ActionResult Index(string NameString, string PhoneString, string EmailString, string CityString, string stateList, string ZipString, string startDate, string endDate, string VisitString, string sortOrder)
        {
            ViewBag.LastNameSortParam = String.IsNullOrEmpty(sortOrder) ? "last_name_desc" : "";
            ViewBag.FirstNameSortParam = String.IsNullOrEmpty(sortOrder) ? "first_name_desc" : "";
            string[] stateCodeList = { "AL", "AK", "AZ", "AR", "CA", "CO", "CT", "DE", "DC", "FL", "GA", "HI", "ID", "IL", "IN", "IA", "KS", "KY", "LA", "ME", "MD", "MA", "MI", "MN", "MS", "MO", "MT", "NE", "NE", "NV", "NH", "NJ", "NM", "NY", "NC", "ND", "OH", "OK", "OR", "PA", "RI", "SC", "SD", "TN", "TX", "UT", "VT", "VA", "WA", "WV", "WI", "WY" };
            ViewBag.stateList = new SelectList(stateCodeList);

              var guests = from g in db.Guests select g;
                if (!String.IsNullOrEmpty(NameString))
                {
                    guests = guests.Where(s => s.FirstName.Contains(NameString) || s.LastName.Contains(NameString));
                }
                if (!String.IsNullOrEmpty(PhoneString))
                {
                    guests = guests.Where(s => s.Phone.Contains(PhoneString));
                }
                if (!String.IsNullOrEmpty(EmailString))
                {
                    guests = guests.Where(s => s.Email.Contains(EmailString));
                }
                if (!String.IsNullOrEmpty(CityString))
                {
                    guests = guests.Where(s => s.City.Contains(CityString));
                }
                if (!String.IsNullOrEmpty(ZipString))
                {
                    guests = guests.Where(s => s.Zip.Contains(ZipString));
                }
                if (!String.IsNullOrEmpty(stateList))
                {
                    guests = guests.Where(s => s.State.Contains(stateList));
                }
                if (!String.IsNullOrEmpty(startDate))
                {
                    System.DateTime dt = Convert.ToDateTime(startDate);
                    guests = guests.Where(s => s.datevisited >= dt);
                }
                if (!String.IsNullOrEmpty(endDate))
                {
                    System.DateTime dt = Convert.ToDateTime(endDate);
                    guests = guests.Where(s => s.datevisited <= dt);
                }
                if (!String.IsNullOrEmpty(VisitString))
                {
                    int minNumVisits = Convert.ToInt32(VisitString);
                    guests = guests.Where(s => s.VisitNumber >= minNumVisits);
                }

            switch (sortOrder)
            {
                case "last_name_desc":
                    guests = guests.OrderByDescending(s => s.LastName);
                    break;
                case "first_name_desc":
                    guests = guests.OrderByDescending(s => s.FirstName);
                    break;
                default:
                    guests = guests.OrderBy(s => s.LastName);
                    break;
            }
            return View(guests);
        }
    }
}

Finally, here is the layout page behind the view code. This may be where I am messing up with the javascript/css references.

<!DOCTYPE html>
<html>
<head>
    <meta charset="utf-8" />
    <meta name="viewport" content="width=device-width, initial-scale=1.0">
    <link rel="shortcut icon" href="~/Content/images/favicon.ico">
    <title>@ViewBag.Title - VirtualGuestbook</title>
    @Styles.Render("~/Content/css")
    @Scripts.Render("~/bundles/modernizr")
    <script src="~/Scripts/modernizr-2.6.2.js"></script>
    <!-- DataTables CSS -->
    <link rel="stylesheet" type="text/css" href="~/Content/jquery.dataTables.css">
</head>
<body>
    <div class="navbar navbar-inverse navbar-fixed-top">
        <div class="container">

            <div class="navbar-collapse collapse">
                <ul class="nav navbar-nav">
                    <li>@Html.ActionLink("Home", "AdminIndex", "Home")</li>
                    @if (User.Identity.IsAuthenticated)
                    {
                        <li>@Html.ActionLink("Guest List", "Index", "Guests")</li>
                        if (!User.IsInRole("A, S"))
                        {
                            <li>@Html.ActionLink("Employees List", "Index", "Administrators")</li>
                            <li>@Html.ActionLink("Add New Employee", "Create", "Administrators")</li>
                        }
                        if (User.IsInRole("S"))
                        {
                            <li></li>
                        }
                        <li>
                            <img src="~/Content/images/vertBar.png"
                                 alt="vertical bar for logical separation" width="3" height="50" />
                        </li>
                        @*<li><a href="#">Welcome @User.Identity.Name!</a></li>*@
                        <li><a href="#">Welcome!</a></li>
                        if (Session["AdminID"] != null)
                        {
                            int logID = (int)Session["AdminID"];
                            if (logID != null)
                            {
                                <li>@Html.ActionLink("Set/Change Security Questions", "SetQuests", new { controller = "Accounts", id = logID })</li>
                                <li>@Html.ActionLink("Change Password", "ChangePass", new { controller = "Accounts", id = logID })</li>
                            }
                        }
                        <li>@Html.ActionLink("Logout", "Logout", "Accounts")</li>
                    }
                    else
                    {
                        <li>
                            <img src="~/Content/images/vertBar.png"
                                 alt="vertical bar for logical separation" width="3" height="50" />
                        </li>
                        <li><a href="#">Welcome!</a></li>
                        <li>@Html.ActionLink("Login", "Login", "Accounts")</li>
                    }
                </ul>
            </div>
        </div>
    </div>

    <div class="sec-wrapper">
        @RenderBody()
        <hr />
        <footer>
            <div id="clearBoth">
                <p>&copy; @DateTime.Now.Year - VirtualGuestbook</p>
            </div>
        </footer>
    </div>

    <script src="~/Scripts/jquery-1.10.2.min.js"></script>
    <script src="~/Scripts/bootstrap.min.js"></script>
    @Scripts.Render("~/bundles/jquery")
    @Scripts.Render("~/bundles/bootstrap")
    @RenderSection("scripts", required: false)
</body>
</html>

Any help is greatly appreciated!

-Darren

Answer 1

IMHO, you can follow the steps ` 1. On sorting by a column, hyperlink column set the field name in a hidden field.

2. On clicking the filter button, post the hidden field and its direction

3. In controller, validate the sort string and direction along with the value for any malicious inputs

4. Pass these to the data access layer

5. Filter and sort the data

6. Refresh grid `

I would strongly suggest you to use server side paging and sorting because you will lower data transfer between db and application.

Few lines of JavaScript or some plugin should help you.

The sorting information can be passed to the server via a json string in the post method. It can take the format as

var sort data=[{ Field:"username ", Direction:"asc" }]

This way, you can send all sorted field data to your server side code to do processing. Wherein, you will deserialize these data and use while forming the SQL query or in EF

Answer 2

If you are are going to be sorting records then javascript is not going to perform well if you have more than a few hundred records. However using javascript to populate form fields and trigger a form submission is an excellent idea and should deliver a good user experience.

Start off by creating a ViewModel, this is an object that you can use to send data to the view, and to receive data from it.

public class GuestListViewModel
{
    public string Name { get; set; }
    public string Email { get; set; }
    public string Phone { get; set; }
    // And the rest of the fields

    //These fields are new
    public string LastNameSort { get; set; }
    public string FirstNameSort { get; set; }
    public List<Guest> Guests { get; set; }
}

In your controller, you populate this view model with all the data, including the input data, and the sorted and filtered guest list.

Then a stripped down version of your view would look something like this.

@model VirtualGuestbook.Models.GuestListViewModel

@using (Html.BeginForm("Index", "Guests", FormMethod.Get, new { @id="filter-form"))
    {
        <div id="left30search">
            Name (First or Last): @Html.TextBoxFor(x => Model.Name)<br /><br />
            Phone: @Html.TextBoxFor(x => Model.Phone)<br /><br />
            Email: @Html.TextBoxFor(x => Model.Email)
            // Note the TextBoxFor referencing value in the model. This ensures that the previously submitted values are retained
            // Make sure to include the rest of the filter fields
        </div>
        <div>
            @Html.HiddenFor(x => Model.FirstNameSort, new { id= "first-name-sort" })
            @Html.HiddenFor(x => Model.LastNameSort, new { id= "last-name-sort" })
            // Note hidden fields to hold value of sort direction

            <input type="submit" value="Filter" />
        </div>
    </div>
    }

//Now for the table

<table id="myTable" class="table">
    <thead>
        <th>Prefix</th>
        <th id="first-name-header">First Name</th>
        <th id="last-name-header">Last Name</th>
        // Note the id field for easy javascript selection
        <th>Phone</th>
        <th>Email</th>
        // And the rest of your fields
    </thead>
    <tbody>
        @foreach(var guest in Model.Guests)
        {
            // Note change to list within the model, not just the whole model
        }
    </tbody>

And then your javascript would look something like this

$(document).ready(function () {

    $("#first-name-header").on("click", function(){
        var $first = $("#first-name-sort");
        switch($first.val()){
            case "asc":
                $first.val("desc");
                break;
            case "desc":
                $first.val("");
                break;
            default:
                $first.val("asc");
        }

       $("#filter-form").submit();
    };

    // When the header row is click, read the value in the hidden field
    // Toggle the value according to its current value
    // Submit the form, complete with old filter values and new sort values
});

For your controller, you may be able to improve the searching function - take a look at this: Dynamic LINQ OrderBy on IEnumerable<T>