Part 03 – Sorting, Paging, and Grouping

1. Part 03 – Sorting, Paging, and Grouping Entity Framework NTPCUG Tom Perkins

2. Previously … • Contoso University solution created • Database • Student Model, Views, and Controller • CRUD Scaffolding

3. Session Objectives • Sorting – display student data in different orders by clicking on the column headings. • Filtering – Search student names that contain a string of characters • Paging – Show pages of a long list of students • Grouping – Show student statistics derived through grouping

4. Target “Students” Page

5. Objective 1 Column header sort links

6. Column Headings • Headings: Last Name and Enrollment Date • Click on heading to sort list • Repeated clicking – toggles list in ascending versus descending order • Headings are hyperlinks to a controller action

7. Student Activity • Open Visual Studio • Open the Contoso University solution • In the Solution Explorer • In the Controllers section, • Click on StudentController.cs • Locate the Index method • Replace the code in the Index method by cutting and pasting the code in Part 3 Timesaver Modification 1 // GET: /Student/ publicActionResult Index() { return View(db.Students.ToList()); }

8. 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());}

9. The New Index Method • Refer to Modification 1: • Method receives “sortorder” string as input • sortorder values expected: • Name • name_desc ( name descending) • Date • date_desc ( date descending) • Default – Last Name, ascending

10. Processing Logic • Refer to Modification 1: • First time through: • sortorder is null • List is displayed in ascending Name order • Entered as a result of user clicking column heading: • sortorder value will be established in the query string created by the hyperlink – “Name”, “name_desc”, “Date”, “date_desc”)

11. Controller/View Communication • The View simply displays the List of Students sent to it by the Controller. • The Controller establishes the order for the Student List. • If the user wishes to see a different order, s/he clicks on the column header. • This will generate a subsequent query back to the controller, identifying which column has been selected and the requested ascending/descending order.

12. The ViewBag Variables • The ViewBag can be used to share information between the Controller and the View • Two ViewBag variables used here: • ViewBag.NameSortParm • ViewBag.DateSortParm • Values are set in the Controller • Values are used in the View to configure the column header hyperlinks • The column header hyperlinks • Identify what order the display is to be changed to • “toggle” whether the display is to be in ascending or descending order

13. MVC Principle • The bulk of the processing is done by the Controller, on the data in the Model. • The View simply displays the data in the Model. • Controls on the View may be configured from data in the ViewBag to request actions elsewhere in the application – here, in another display from the originating controller.

14. ViewBag Variables ViewBag.NameSortParm = String.IsNullOrEmpty(sortOrder) ? "name_desc" : ""; ViewBag.DateSortParm= sortOrder == "Date" ? "date_desc" : "Date"; • Refer to Modification 1 • Ternary statements – (composed of three parts) • If sortorderparm is null or empty (implies this is the default first time through or a name-ascending display was requested • Set the NameSortParm to “name-descending” (toggle the LastName heading to generate a descending request if clicked) • ELSE • Set the NameSortPart to “ “ (empty string – configure the Last Name heading to generate an ascending request if clicked)

15. The sortOrder parameter • Describes the order in which the student list will be displayed • Sets up the hyperlinks in the column headers for a subsequent user click

16. Student Activity • Apply the highlighted code in Modification 2 to Views\Student\index.cshtml

17. Run the page • Click the Date column header and verify that the list is in Date order • Click the LastName column header and verify that the list is in Last Name order

20. Objective 2 Add a search text box – last name, first name search

21. Student Activity • Apply Modification 4 to Views\Student\index.cshtml

22. Student Activity • Apply the entire Modification 5 to replace the index method in Controllers\StudentController.cs(changes are hightlighted)

23. Run the Student Page • Search for ‘an’ in the first or last name

24. Objective 3 Create a paging feature

26. Paging Feature Approach • Install a NuGet Package (PagedList.MVC) • Change the Index method • Add paging links to the Index view.

27. PagedList.Mvc – NuGet Package • Installs a collection type and extension methods • For both • IQueryable collections (Outside of memory – LINQ or SQL) • IEnumerable collections (In memory) • Extension methods  a single page of data in a PagedList collection • PagedList collection provides properties and methods that facilitate paging • A paging helper is installed that can display the paging buttons

28. STUDENT ACTIVITY • Tools Menu | Library Package Manager | Package Manager Console • In Console, make sure: • Package Source = nuget.org • Default Project = ContosoUniversity • Then type Install-Package PagedList.Mvc

30. Analysis of modified StudentController Index method public ActionResult Index(string sortOrder, string currentFilter, string searchString, int? page) • New parameters: • page • current sort order • current filter • All parameters null if • First time through • User hasn’t clicked sorting or paging link • If paging link has been clicked, page parameter contains the page number to display • Current sort order is passed to View through the ViewBag • The current sort order is required to keep the same sort order whilst paging. ViewBag.CurrentSort = sortOrder;

31. Code analysis, continued … if (searchString != null) { page = 1; } else { searchString = currentFilter; } • currentFilter – provides the current filter string. • If search string is changed during paging • Page must be set to 1 (different data will be displayed) • When new search value is entered into text box and submit pressed, searchString will not be null.

32. Bottom of code – analysis continued … intpageSize = 3; intpageNumber = (page ?? 1); return View(students.ToPagedList(pageNumber, pageSize)); • ToPagedList extension method passes a single page of students to the View. Page is in a collection type that supports paging. • (page??1) – ?? null coalescing operator • Returns page if not null, else returns 1

33. STUDENT ACTIVITYModify Student Index View for Paging • Replace all the code in Views\Student\Index.cshtml with the code in Modification 7. • Note: cut and paste all the code; it extends over two pages

34. Code Analysis • Please refer to code in Modification 7 • @model statement – View now gets a PagedList object instead of a List object • using – access MVC helper for paging buttons • BeginForm now specifies FormMethod.Get • Default BeginForm - parameters passed in message body (POST) • Get recommended when action does not result in an update – W3C • Get passes parameters in query string – user can bookmark the URL

35. Code Analysis, continued • Initializing the text box with the current search string allows the user to see the search string when a new page is displayed • Header links are now passed the current filter so that the user can sort within filter results • Current page and total number of pages are displayed • Page 0 of 0 is displayed • Paging buttons are displayed by PageListPager helper

36. STUDENT ACTIVITY • Run the page • Verify paging works • Verify filtering within paging works

38. Objective 4 Student Statistics page

40. Student Statistics Page • Displays how many students have enrolled for each enrollment date • Involves grouping and simple group calculations • Approach: • New model class for View • Modify About method in HomeController • Modify the About View

41. STUDENT ACTIVITY Add a new folder to the project (ViewModels) Add a new class to this folder (EnrollmentDateGroup.cs) Replace the template code with the code in Modification 8. using System; using System.ComponentModel.DataAnnotations; namespace ContosoUniversity.ViewModels { public class EnrollmentDateGroup { [DataType(DataType.Date)] public DateTime? EnrollmentDate { get; set; } public intStudentCount { get; set; } } }

42. STUDENT ACTIVITY In HomeController.cs: Add the following using statements at the top of the file. using ContosoUniversity.DAL; using ContosoUniversity.ViewModels; Add a class variable for the database context immediately after the curly brace public class HomeController : Controller { private SchoolContextdb = new SchoolContext(); Replace the About method in HomeController.cs with the code in Modification 9. Add a Dispose method to HomeController.cs as shown in Modification 10.

43. STUDENT ACTIVITY • Replace the code in module Views\Home\About.cshtml with the code in Modification 11. • Run the app and view the About screen.

45. FINIS