Sorting, Filtering and Paging MVC

Question

So essentially I've done all my sorting, filtering and paging with the help of this tutorial, which has been very, very handy because I'm very new to this material. Anyways, I'm having issues now trying to sort and filter a few of my tables which have more than one primary key.

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

I created the var applications and var databases but I think if I had a way to combine them I wouldn't have issues with my paging. Because my return view would be more concise.

public ActionResult Index(string sortOrder, string searchString, int? page)
    {
        ViewBag.CurrentSort = sortOrder;
        ViewBag.IDSortParm = String.IsNullOrEmpty(sortOrder) ? "AppID_desc" : "";
        ViewBag.NameSortParm = sortOrder == "Name" ? "AppName_desc" : "Name";
        ViewBag.ID2SortParm = sortOrder == "ID" ? "DatabaseID_desc" : "ID";
        ViewBag.Name2SortParm = sortOrder == "Name2" ? "DatabaseName_desc" : "Name2";

        if (Request.HttpMethod != "GET")
        {
            page = 1;
        }
        ViewBag.CurrentFilter = searchString;


        var applications = from a in db.Application_
                      select a;
        var databases = from d in db.Database_ //this is what I added
                        select d;
        if (!String.IsNullOrEmpty(searchString))
        {
            applications = applications.Where(s => s.AppName.ToUpper().Contains(searchString.ToUpper()));
            databases = databases.Where(d => d.DatabaseName.ToUpper().Contains(searchString.ToUpper())); //also what I added
        }
        switch (sortOrder)
        {
            case "AppID_desc":
                applications = applications.OrderByDescending(a => a.AppID);
                break;
            case "Name":
                applications = applications.OrderBy(a => a.AppName);
                break;
            case "AppName_desc":
                applications = applications.OrderByDescending(a => a.AppName);
                break;
            case "Name2":
                databases = databases.OrderBy(d=> d.DatabaseName);
                break;
            case "DatabaseName_desc":
                databases = databases.OrderByDescending(d => d.DatabaseName);
                break;
            default:
                applications = applications.OrderBy(a => a.AppID);
                break;

        }
        int pageSize = 10;
        int pageNumber = (page ?? 1);
        return View(applications.ToPagedList(pageNumber, pageSize));
    }

I added the var database because I need to search for values in the database_ table along with the application table.

The index:

@using (Html.BeginForm())
{
<p>
    Search Name: @Html.TextBox("Search_Data", ViewBag.FilterValue as string)
    <input type="submit" value="Find" />
</p>
}
<table class="table">

<tr>
    <th>
        @Html.ActionLink("AppID", "Index", new { sortOrder = ViewBag.IDSortParm })
    </th>
    <th>
        @Html.ActionLink("ApplicationName", "Index", new { sortOrder = ViewBag.NameSortParm })
    </th>
    <th>
        @Html.ActionLink("DatabaseID", "Index", new { sortOrder = ViewBag.ADSortParm })
    </th>
    <th>
        @Html.ActionLink("DatabaseName", "Index", new { sortOrder = ViewBag.Name2SortParm })
    </th>

I believe I'm having an issue with the index, but obviously I'm pretty clueless in general so whatever assistance you can offer would be greatly appreciated.

Thanks!!

EDIT: For more clarity, plus I found a way to explain myself better.

"I'm having issues now trying to sort and filter a few of my tables" - what is the issue (it doesn't sort, it doesn't sort sometimes, only certain pages sort, it doesn't filter, etc)? Your question is currently unclear about what the actual problem is (expected behavior, current behavior). — Josh Darnell
– Josh Darnell, Commented Nov 5, 2014 at 15:50
The tables with more than one primary key won't sort at all. — momofierce
– momofierce, Commented Nov 5, 2014 at 15:51
As far I understand you are trying to see both table applications and databases in one grid, and be able to click on the header to sort data? — Pavel
– Pavel, Commented Nov 5, 2014 at 16:01
Yes, I'm sorry if my rambling is hard to understand. It's really hard to word this stuff. — momofierce
– momofierce, Commented Nov 5, 2014 at 16:02
Please, provide your entity definition for tables applications and databases. — Pavel
– Pavel, Commented Nov 5, 2014 at 16:07

Sam whinchest · Accepted Answer · 2015-01-14 15:12:25Z

Here is an example that should help you with your problem

 public ActionResult Index(string sortOrder)
    {
       ViewBag.NameSortParm = String.IsNullOrEmpty(sortOrder) ? "Name_desc" : "";
       ViewBag.DateSortParm = sortOrder == "Date" ? "Date_desc" : "Date";
       var students = from s in db.Students
                      select s;
       switch (sortOrder)
       {
          case "Name_desc":
             students = students.OrderByDescending(s => s.LastName);
             break;
          case "Date":
             students = students.OrderBy(s => s.EnrollmentDate);
             break;
          case "Date_desc":
             students = students.OrderByDescending(s => s.EnrollmentDate);
             break;
          default:
             students = students.OrderBy(s => s.LastName);
             break;
       }
       return View(students.ToList());
    }

Example of sort

 ViewBag.NameSortParm = String.IsNullOrEmpty(sortOrder) ? "name_desc" : "";
    ViewBag.DateSortParm = sortOrder == "Date" ? "date_desc" : "Date";

Here is an example of a view to display

<p>
    @Html.ActionLink("Create New", "Create")
</p>
<table>
    <tr>
        <th>
            @Html.ActionLink("Last Name", "Index", new { sortOrder = ViewBag.NameSortParm })
        </th>
        <th>First Name
        </th>
        <th>
            @Html.ActionLink("Enrollment Date", "Index", new { sortOrder = ViewBag.DateSortParm })
        </th>
        <th></th>
    </tr>

@foreach (var item in Model) {

I am also posting a search deature whigh might be handy

public ViewResult Index(string sortOrder, string searchString)
{
    ViewBag.NameSortParm = String.IsNullOrEmpty(sortOrder) ? "name_desc" : "";
    ViewBag.DateSortParm = sortOrder == "Date" ? "date_desc" : "Date";
    var students = from s in db.Students
                   select s;
    if (!String.IsNullOrEmpty(searchString))
    {
        students = students.Where(s => s.LastName.Contains(searchString)
                               || s.FirstMidName.Contains(searchString));
    }
    switch (sortOrder)
    {
        case "name_desc":
            students = students.OrderByDescending(s => s.LastName);
            break;
        case "Date":
            students = students.OrderBy(s => s.EnrollmentDate);
            break;
        case "date_desc":
            students = students.OrderByDescending(s => s.EnrollmentDate);
            break;
        default:
            students = students.OrderBy(s => s.LastName);
            break;
    }

    return View(students.ToList());
}

the view to display

<p>
    @Html.ActionLink("Create New", "Create")
</p>

@using (Html.BeginForm())
{
    <p>
        Find by name: @Html.TextBox("SearchString")  
        <input type="submit" value="Search" /></p>
}

<table>
    <tr>

Billal BEGUERADJ · Accepted Answer · 2017-09-22 09:20:47Z

1

I figured out the sorting problem I was having.

   var applications = from a in db.Application_
                  select a;
   var databases = from d in db.Database_ //this is what I added
                    select d;

Need to be:

   var appdb = from a in db.AppDB_
                      select a;

It was a mistake on my part.

I also just figured out how I'm going to address my searching problem. I just converted the int from AppID to a string.

  if (!String.IsNullOrEmpty(searchString))
  {
     appdb = appdb.Where(a => a.AppID.ToString().Contains(searchString.ToUpper()));
  }

edited Sep 22, 2017 at 9:20

Billal BEGUERADJ

23k45 gold badges125 silver badges142 bronze badges

answered Nov 5, 2014 at 18:26

momofierce

891 gold badge3 silver badges11 bronze badges

Comments

StriplingWarrior · Accepted Answer · 2014-11-05 21:05:18Z

0

Presumably the Database data that you're searching for is related in some way to the Application data. I'm going to go out on a limb and say that each Application probably has a Database. Rather than using a separate variable, you probably want to use navigation properties to modify the applications query, like so:

        case "Name2":
            applications = applications.OrderBy(a => a.Database.DatabaseName);
            break;
        case "DatabaseName_desc":
            applications = applications.OrderByDescending(a => a.Database.DatabaseName);
            break;

edited Nov 5, 2014 at 21:05

answered Nov 5, 2014 at 16:08

StriplingWarrior

158k29 gold badges261 silver badges326 bronze badges

4 Comments

momofierce Over a year ago

It doesn't seem to be working. it's not recognizing d. in the first context and then Database. in the second context

Pavel Over a year ago

I suppose the d is misprint. Change it to a

momofierce Over a year ago

It doesn't recognize Database. now. I suppose it's because of how my var is declared. var applications = from a in db.Application_ select a;

StriplingWarrior Over a year ago

@momofierce: Do you have navigation properties on your entities? What's the relationship between them like?

Collectives™ on Stack Overflow

Sorting, Filtering and Paging MVC

3 Answers 3

Comments

Comments

4 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

4 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related