Sorting, filtering, paging, and grouping - EF Core with ASP.NET Core MVC tutorial (3 of 10)
By Tom Dykstra and Rick Anderson
The Contoso University sample web application demonstrates how to create ASP.NET Core MVC web applications using Entity Framework Core and Visual Studio. For information about the tutorial series, see the first tutorial in the series.
In the previous tutorial, you implemented a set of web pages for basic CRUD operations for Student entities. In this tutorial you’ll add sorting, filtering, and paging functionality to the Students Index page. You’ll also create a page that does simple grouping.
The following illustration shows what the page will look like when you’re done. The column headings are links that the user can click to sort by that column. Clicking a column heading repeatedly toggles between ascending and descending sort order.
Add Column Sort Links to the Students Index Page
To add sorting to the Student Index page, you’ll change the Index
method of the Students controller and add code to the Student Index view.
Add sorting Functionality to the Index method
In StudentsController.cs, replace the Index
method with the following code:
[!code-csharpMain]
1: #define SortFilterPage //or ScaffoldedIndex or SortOnly or SortFilter or DynamicLinq
2: #define ReadFirst //or CreateAndAttach
3: #define DeleteWithReadFirst // or DeleteWithoutReadFirst
4:
5: using System.Linq;
6: using System.Threading.Tasks;
7: using Microsoft.AspNetCore.Mvc;
8: using Microsoft.AspNetCore.Mvc.Rendering;
9: using Microsoft.EntityFrameworkCore;
10: using ContosoUniversity.Data;
11: using ContosoUniversity.Models;
12: using System;
13: using Microsoft.Extensions.Logging;
14:
15: #region snippet_Context
16: namespace ContosoUniversity.Controllers
17: {
18: public class StudentsController : Controller
19: {
20: private readonly SchoolContext _context;
21:
22: public StudentsController(SchoolContext context)
23: {
24: _context = context;
25: }
26: #endregion
27:
28: // GET: Students
29:
30: #if (ScaffoldedIndex)
31: #region snippet_ScaffoldedIndex
32: public async Task<IActionResult> Index()
33: {
34: return View(await _context.Students.ToListAsync());
35: }
36: #endregion
37: #elif (SortOnly)
38: #region snippet_SortOnly
39: public async Task<IActionResult> Index(string sortOrder)
40: {
41: ViewData["NameSortParm"] = String.IsNullOrEmpty(sortOrder) ? "name_desc" : "";
42: ViewData["DateSortParm"] = sortOrder == "Date" ? "date_desc" : "Date";
43: var students = from s in _context.Students
44: select s;
45: switch (sortOrder)
46: {
47: case "name_desc":
48: students = students.OrderByDescending(s => s.LastName);
49: break;
50: case "Date":
51: students = students.OrderBy(s => s.EnrollmentDate);
52: break;
53: case "date_desc":
54: students = students.OrderByDescending(s => s.EnrollmentDate);
55: break;
56: default:
57: students = students.OrderBy(s => s.LastName);
58: break;
59: }
60: return View(await students.AsNoTracking().ToListAsync());
61: }
62: #endregion
63: #elif (SortFilter)
64: #region snippet_SortFilter
65: public async Task<IActionResult> Index(string sortOrder, string searchString)
66: {
67: ViewData["NameSortParm"] = String.IsNullOrEmpty(sortOrder) ? "name_desc" : "";
68: ViewData["DateSortParm"] = sortOrder == "Date" ? "date_desc" : "Date";
69: ViewData["CurrentFilter"] = searchString;
70:
71: var students = from s in _context.Students
72: select s;
73: if (!String.IsNullOrEmpty(searchString))
74: {
75: students = students.Where(s => s.LastName.Contains(searchString)
76: || s.FirstMidName.Contains(searchString));
77: }
78: switch (sortOrder)
79: {
80: case "name_desc":
81: students = students.OrderByDescending(s => s.LastName);
82: break;
83: case "Date":
84: students = students.OrderBy(s => s.EnrollmentDate);
85: break;
86: case "date_desc":
87: students = students.OrderByDescending(s => s.EnrollmentDate);
88: break;
89: default:
90: students = students.OrderBy(s => s.LastName);
91: break;
92: }
93: return View(await students.AsNoTracking().ToListAsync());
94: }
95: #endregion
96: #elif (SortFilterPage)
97: #region snippet_SortFilterPage
98: public async Task<IActionResult> Index(
99: string sortOrder,
100: string currentFilter,
101: string searchString,
102: int? page)
103: {
104: ViewData["CurrentSort"] = sortOrder;
105: ViewData["NameSortParm"] = String.IsNullOrEmpty(sortOrder) ? "name_desc" : "";
106: ViewData["DateSortParm"] = sortOrder == "Date" ? "date_desc" : "Date";
107:
108: if (searchString != null)
109: {
110: page = 1;
111: }
112: else
113: {
114: searchString = currentFilter;
115: }
116:
117: ViewData["CurrentFilter"] = searchString;
118:
119: var students = from s in _context.Students
120: select s;
121: if (!String.IsNullOrEmpty(searchString))
122: {
123: students = students.Where(s => s.LastName.Contains(searchString)
124: || s.FirstMidName.Contains(searchString));
125: }
126: switch (sortOrder)
127: {
128: case "name_desc":
129: students = students.OrderByDescending(s => s.LastName);
130: break;
131: case "Date":
132: students = students.OrderBy(s => s.EnrollmentDate);
133: break;
134: case "date_desc":
135: students = students.OrderByDescending(s => s.EnrollmentDate);
136: break;
137: default:
138: students = students.OrderBy(s => s.LastName);
139: break;
140: }
141:
142: int pageSize = 3;
143: return View(await PaginatedList<Student>.CreateAsync(students.AsNoTracking(), page ?? 1, pageSize));
144: }
145: #endregion
146: #elif (DynamicLinq)
147: #region snippet_DynamicLinq
148: public async Task<IActionResult> Index(
149: string sortOrder,
150: string currentFilter,
151: string searchString,
152: int? page)
153: {
154: ViewData["CurrentSort"] = sortOrder;
155: ViewData["NameSortParm"] =
156: String.IsNullOrEmpty(sortOrder) ? "LastName_desc" : "";
157: ViewData["DateSortParm"] =
158: sortOrder == "EnrollmentDate" ? "EnrollmentDate_desc" : "EnrollmentDate";
159:
160: if (searchString != null)
161: {
162: page = 1;
163: }
164: else
165: {
166: searchString = currentFilter;
167: }
168:
169: ViewData["CurrentFilter"] = searchString;
170:
171: var students = from s in _context.Students
172: select s;
173:
174: if (!String.IsNullOrEmpty(searchString))
175: {
176: students = students.Where(s => s.LastName.Contains(searchString)
177: || s.FirstMidName.Contains(searchString));
178: }
179:
180: if (string.IsNullOrEmpty(sortOrder))
181: {
182: sortOrder = "LastName";
183: }
184:
185: bool descending = false;
186: if (sortOrder.EndsWith("_desc"))
187: {
188: sortOrder = sortOrder.Substring(0, sortOrder.Length - 5);
189: descending = true;
190: }
191:
192: if (descending)
193: {
194: students = students.OrderByDescending(e => EF.Property<object>(e, sortOrder));
195: }
196: else
197: {
198: students = students.OrderBy(e => EF.Property<object>(e, sortOrder));
199: }
200:
201: int pageSize = 3;
202: return View(await PaginatedList<Student>.CreateAsync(students.AsNoTracking(),
203: page ?? 1, pageSize));
204: }
205: #endregion
206: #endif
207:
208: // GET: Students/Details/5
209: #region snippet_Details
210: public async Task<IActionResult> Details(int? id)
211: {
212: if (id == null)
213: {
214: return NotFound();
215: }
216:
217: var student = await _context.Students
218: .Include(s => s.Enrollments)
219: .ThenInclude(e => e.Course)
220: .AsNoTracking()
221: .SingleOrDefaultAsync(m => m.ID == id);
222:
223: if (student == null)
224: {
225: return NotFound();
226: }
227:
228: return View(student);
229: }
230: #endregion
231:
232: // GET: Students/Create
233: public IActionResult Create()
234: {
235: return View();
236: }
237:
238: // POST: Students/Create
239: #region snippet_Create
240: [HttpPost]
241: [ValidateAntiForgeryToken]
242: public async Task<IActionResult> Create(
243: [Bind("EnrollmentDate,FirstMidName,LastName")] Student student)
244: {
245: try
246: {
247: if (ModelState.IsValid)
248: {
249: _context.Add(student);
250: await _context.SaveChangesAsync();
251: return RedirectToAction(nameof(Index));
252: }
253: }
254: catch (DbUpdateException /* ex */)
255: {
256: //Log the error (uncomment ex variable name and write a log.
257: ModelState.AddModelError("", "Unable to save changes. " +
258: "Try again, and if the problem persists " +
259: "see your system administrator.");
260: }
261: return View(student);
262: }
263: #endregion
264:
265: // GET: Students/Edit/5
266: public async Task<IActionResult> Edit(int? id)
267: {
268: if (id == null)
269: {
270: return NotFound();
271: }
272:
273: var student = await _context.Students
274: .AsNoTracking()
275: .SingleOrDefaultAsync(m => m.ID == id);
276: if (student == null)
277: {
278: return NotFound();
279: }
280: return View(student);
281: }
282:
283: // POST: Students/Edit/5
284: #if (CreateAndAttach)
285: #region snippet_CreateAndAttach
286: public async Task<IActionResult> Edit(int id, [Bind("ID,EnrollmentDate,FirstMidName,LastName")] Student student)
287: {
288: if (id != student.ID)
289: {
290: return NotFound();
291: }
292: if (ModelState.IsValid)
293: {
294: try
295: {
296: _context.Update(student);
297: await _context.SaveChangesAsync();
298: return RedirectToAction(nameof(Index));
299: }
300: catch (DbUpdateException /* ex */)
301: {
302: //Log the error (uncomment ex variable name and write a log.)
303: ModelState.AddModelError("", "Unable to save changes. " +
304: "Try again, and if the problem persists, " +
305: "see your system administrator.");
306: }
307: }
308: return View(student);
309: }
310: #endregion
311: #elif (ReadFirst)
312: #region snippet_ReadFirst
313: [HttpPost, ActionName("Edit")]
314: [ValidateAntiForgeryToken]
315: public async Task<IActionResult> EditPost(int? id)
316: {
317: if (id == null)
318: {
319: return NotFound();
320: }
321: var studentToUpdate = await _context.Students.SingleOrDefaultAsync(s => s.ID == id);
322: if (await TryUpdateModelAsync<Student>(
323: studentToUpdate,
324: "",
325: s => s.FirstMidName, s => s.LastName, s => s.EnrollmentDate))
326: {
327: try
328: {
329: await _context.SaveChangesAsync();
330: return RedirectToAction(nameof(Index));
331: }
332: catch (DbUpdateException /* ex */)
333: {
334: //Log the error (uncomment ex variable name and write a log.)
335: ModelState.AddModelError("", "Unable to save changes. " +
336: "Try again, and if the problem persists, " +
337: "see your system administrator.");
338: }
339: }
340: return View(studentToUpdate);
341: }
342: #endregion
343: #endif
344:
345: // GET: Students/Delete/5
346: #region snippet_DeleteGet
347: public async Task<IActionResult> Delete(int? id, bool? saveChangesError = false)
348: {
349: if (id == null)
350: {
351: return NotFound();
352: }
353:
354: var student = await _context.Students
355: .AsNoTracking()
356: .SingleOrDefaultAsync(m => m.ID == id);
357: if (student == null)
358: {
359: return NotFound();
360: }
361:
362: if (saveChangesError.GetValueOrDefault())
363: {
364: ViewData["ErrorMessage"] =
365: "Delete failed. Try again, and if the problem persists " +
366: "see your system administrator.";
367: }
368:
369: return View(student);
370: }
371: #endregion
372: // POST: Students/Delete/5
373: #if (DeleteWithReadFirst)
374: #region snippet_DeleteWithReadFirst
375: [HttpPost, ActionName("Delete")]
376: [ValidateAntiForgeryToken]
377: public async Task<IActionResult> DeleteConfirmed(int id)
378: {
379: var student = await _context.Students
380: .AsNoTracking()
381: .SingleOrDefaultAsync(m => m.ID == id);
382: if (student == null)
383: {
384: return RedirectToAction(nameof(Index));
385: }
386:
387: try
388: {
389: _context.Students.Remove(student);
390: await _context.SaveChangesAsync();
391: return RedirectToAction(nameof(Index));
392: }
393: catch (DbUpdateException /* ex */)
394: {
395: //Log the error (uncomment ex variable name and write a log.)
396: return RedirectToAction(nameof(Delete), new { id = id, saveChangesError = true });
397: }
398: }
399: #endregion
400: #elif (DeleteWithoutReadFirst)
401: #region snippet_DeleteWithoutReadFirst
402: [HttpPost]
403: [ValidateAntiForgeryToken]
404: public async Task<IActionResult> DeleteConfirmed(int id)
405: {
406: try
407: {
408: Student studentToDelete = new Student() { ID = id };
409: _context.Entry(studentToDelete).State = EntityState.Deleted;
410: await _context.SaveChangesAsync();
411: return RedirectToAction(nameof(Index));
412: }
413: catch (DbUpdateException /* ex */)
414: {
415: //Log the error (uncomment ex variable name and write a log.)
416: return RedirectToAction(nameof(Delete), new { id = id, saveChangesError = true });
417: }
418: }
419: #endregion
420: #endif
421: }
422: }
This code receives a sortOrder
parameter from the query string in the URL. The query string value is provided by ASP.NET Core MVC as a parameter to the action method. The parameter will be a string that’s either “Name” or “Date”, optionally followed by an underscore and the string “desc” to specify descending order. The default sort order is ascending.
The first time the Index page is requested, there’s no query string. The students are displayed in ascending order by last name, which is the default as established by the fall-through case in the switch
statement. When the user clicks a column heading hyperlink, the appropriate sortOrder
value is provided in the query string.
The two ViewData
elements (NameSortParm and DateSortParm) are used by the view to configure the column heading hyperlinks with the appropriate query string values.
[!code-csharpMain]
1: #define SortFilterPage //or ScaffoldedIndex or SortOnly or SortFilter or DynamicLinq
2: #define ReadFirst //or CreateAndAttach
3: #define DeleteWithReadFirst // or DeleteWithoutReadFirst
4:
5: using System.Linq;
6: using System.Threading.Tasks;
7: using Microsoft.AspNetCore.Mvc;
8: using Microsoft.AspNetCore.Mvc.Rendering;
9: using Microsoft.EntityFrameworkCore;
10: using ContosoUniversity.Data;
11: using ContosoUniversity.Models;
12: using System;
13: using Microsoft.Extensions.Logging;
14:
15: #region snippet_Context
16: namespace ContosoUniversity.Controllers
17: {
18: public class StudentsController : Controller
19: {
20: private readonly SchoolContext _context;
21:
22: public StudentsController(SchoolContext context)
23: {
24: _context = context;
25: }
26: #endregion
27:
28: // GET: Students
29:
30: #if (ScaffoldedIndex)
31: #region snippet_ScaffoldedIndex
32: public async Task<IActionResult> Index()
33: {
34: return View(await _context.Students.ToListAsync());
35: }
36: #endregion
37: #elif (SortOnly)
38: #region snippet_SortOnly
39: public async Task<IActionResult> Index(string sortOrder)
40: {
41: ViewData["NameSortParm"] = String.IsNullOrEmpty(sortOrder) ? "name_desc" : "";
42: ViewData["DateSortParm"] = sortOrder == "Date" ? "date_desc" : "Date";
43: var students = from s in _context.Students
44: select s;
45: switch (sortOrder)
46: {
47: case "name_desc":
48: students = students.OrderByDescending(s => s.LastName);
49: break;
50: case "Date":
51: students = students.OrderBy(s => s.EnrollmentDate);
52: break;
53: case "date_desc":
54: students = students.OrderByDescending(s => s.EnrollmentDate);
55: break;
56: default:
57: students = students.OrderBy(s => s.LastName);
58: break;
59: }
60: return View(await students.AsNoTracking().ToListAsync());
61: }
62: #endregion
63: #elif (SortFilter)
64: #region snippet_SortFilter
65: public async Task<IActionResult> Index(string sortOrder, string searchString)
66: {
67: ViewData["NameSortParm"] = String.IsNullOrEmpty(sortOrder) ? "name_desc" : "";
68: ViewData["DateSortParm"] = sortOrder == "Date" ? "date_desc" : "Date";
69: ViewData["CurrentFilter"] = searchString;
70:
71: var students = from s in _context.Students
72: select s;
73: if (!String.IsNullOrEmpty(searchString))
74: {
75: students = students.Where(s => s.LastName.Contains(searchString)
76: || s.FirstMidName.Contains(searchString));
77: }
78: switch (sortOrder)
79: {
80: case "name_desc":
81: students = students.OrderByDescending(s => s.LastName);
82: break;
83: case "Date":
84: students = students.OrderBy(s => s.EnrollmentDate);
85: break;
86: case "date_desc":
87: students = students.OrderByDescending(s => s.EnrollmentDate);
88: break;
89: default:
90: students = students.OrderBy(s => s.LastName);
91: break;
92: }
93: return View(await students.AsNoTracking().ToListAsync());
94: }
95: #endregion
96: #elif (SortFilterPage)
97: #region snippet_SortFilterPage
98: public async Task<IActionResult> Index(
99: string sortOrder,
100: string currentFilter,
101: string searchString,
102: int? page)
103: {
104: ViewData["CurrentSort"] = sortOrder;
105: ViewData["NameSortParm"] = String.IsNullOrEmpty(sortOrder) ? "name_desc" : "";
106: ViewData["DateSortParm"] = sortOrder == "Date" ? "date_desc" : "Date";
107:
108: if (searchString != null)
109: {
110: page = 1;
111: }
112: else
113: {
114: searchString = currentFilter;
115: }
116:
117: ViewData["CurrentFilter"] = searchString;
118:
119: var students = from s in _context.Students
120: select s;
121: if (!String.IsNullOrEmpty(searchString))
122: {
123: students = students.Where(s => s.LastName.Contains(searchString)
124: || s.FirstMidName.Contains(searchString));
125: }
126: switch (sortOrder)
127: {
128: case "name_desc":
129: students = students.OrderByDescending(s => s.LastName);
130: break;
131: case "Date":
132: students = students.OrderBy(s => s.EnrollmentDate);
133: break;
134: case "date_desc":
135: students = students.OrderByDescending(s => s.EnrollmentDate);
136: break;
137: default:
138: students = students.OrderBy(s => s.LastName);
139: break;
140: }
141:
142: int pageSize = 3;
143: return View(await PaginatedList<Student>.CreateAsync(students.AsNoTracking(), page ?? 1, pageSize));
144: }
145: #endregion
146: #elif (DynamicLinq)
147: #region snippet_DynamicLinq
148: public async Task<IActionResult> Index(
149: string sortOrder,
150: string currentFilter,
151: string searchString,
152: int? page)
153: {
154: ViewData["CurrentSort"] = sortOrder;
155: ViewData["NameSortParm"] =
156: String.IsNullOrEmpty(sortOrder) ? "LastName_desc" : "";
157: ViewData["DateSortParm"] =
158: sortOrder == "EnrollmentDate" ? "EnrollmentDate_desc" : "EnrollmentDate";
159:
160: if (searchString != null)
161: {
162: page = 1;
163: }
164: else
165: {
166: searchString = currentFilter;
167: }
168:
169: ViewData["CurrentFilter"] = searchString;
170:
171: var students = from s in _context.Students
172: select s;
173:
174: if (!String.IsNullOrEmpty(searchString))
175: {
176: students = students.Where(s => s.LastName.Contains(searchString)
177: || s.FirstMidName.Contains(searchString));
178: }
179:
180: if (string.IsNullOrEmpty(sortOrder))
181: {
182: sortOrder = "LastName";
183: }
184:
185: bool descending = false;
186: if (sortOrder.EndsWith("_desc"))
187: {
188: sortOrder = sortOrder.Substring(0, sortOrder.Length - 5);
189: descending = true;
190: }
191:
192: if (descending)
193: {
194: students = students.OrderByDescending(e => EF.Property<object>(e, sortOrder));
195: }
196: else
197: {
198: students = students.OrderBy(e => EF.Property<object>(e, sortOrder));
199: }
200:
201: int pageSize = 3;
202: return View(await PaginatedList<Student>.CreateAsync(students.AsNoTracking(),
203: page ?? 1, pageSize));
204: }
205: #endregion
206: #endif
207:
208: // GET: Students/Details/5
209: #region snippet_Details
210: public async Task<IActionResult> Details(int? id)
211: {
212: if (id == null)
213: {
214: return NotFound();
215: }
216:
217: var student = await _context.Students
218: .Include(s => s.Enrollments)
219: .ThenInclude(e => e.Course)
220: .AsNoTracking()
221: .SingleOrDefaultAsync(m => m.ID == id);
222:
223: if (student == null)
224: {
225: return NotFound();
226: }
227:
228: return View(student);
229: }
230: #endregion
231:
232: // GET: Students/Create
233: public IActionResult Create()
234: {
235: return View();
236: }
237:
238: // POST: Students/Create
239: #region snippet_Create
240: [HttpPost]
241: [ValidateAntiForgeryToken]
242: public async Task<IActionResult> Create(
243: [Bind("EnrollmentDate,FirstMidName,LastName")] Student student)
244: {
245: try
246: {
247: if (ModelState.IsValid)
248: {
249: _context.Add(student);
250: await _context.SaveChangesAsync();
251: return RedirectToAction(nameof(Index));
252: }
253: }
254: catch (DbUpdateException /* ex */)
255: {
256: //Log the error (uncomment ex variable name and write a log.
257: ModelState.AddModelError("", "Unable to save changes. " +
258: "Try again, and if the problem persists " +
259: "see your system administrator.");
260: }
261: return View(student);
262: }
263: #endregion
264:
265: // GET: Students/Edit/5
266: public async Task<IActionResult> Edit(int? id)
267: {
268: if (id == null)
269: {
270: return NotFound();
271: }
272:
273: var student = await _context.Students
274: .AsNoTracking()
275: .SingleOrDefaultAsync(m => m.ID == id);
276: if (student == null)
277: {
278: return NotFound();
279: }
280: return View(student);
281: }
282:
283: // POST: Students/Edit/5
284: #if (CreateAndAttach)
285: #region snippet_CreateAndAttach
286: public async Task<IActionResult> Edit(int id, [Bind("ID,EnrollmentDate,FirstMidName,LastName")] Student student)
287: {
288: if (id != student.ID)
289: {
290: return NotFound();
291: }
292: if (ModelState.IsValid)
293: {
294: try
295: {
296: _context.Update(student);
297: await _context.SaveChangesAsync();
298: return RedirectToAction(nameof(Index));
299: }
300: catch (DbUpdateException /* ex */)
301: {
302: //Log the error (uncomment ex variable name and write a log.)
303: ModelState.AddModelError("", "Unable to save changes. " +
304: "Try again, and if the problem persists, " +
305: "see your system administrator.");
306: }
307: }
308: return View(student);
309: }
310: #endregion
311: #elif (ReadFirst)
312: #region snippet_ReadFirst
313: [HttpPost, ActionName("Edit")]
314: [ValidateAntiForgeryToken]
315: public async Task<IActionResult> EditPost(int? id)
316: {
317: if (id == null)
318: {
319: return NotFound();
320: }
321: var studentToUpdate = await _context.Students.SingleOrDefaultAsync(s => s.ID == id);
322: if (await TryUpdateModelAsync<Student>(
323: studentToUpdate,
324: "",
325: s => s.FirstMidName, s => s.LastName, s => s.EnrollmentDate))
326: {
327: try
328: {
329: await _context.SaveChangesAsync();
330: return RedirectToAction(nameof(Index));
331: }
332: catch (DbUpdateException /* ex */)
333: {
334: //Log the error (uncomment ex variable name and write a log.)
335: ModelState.AddModelError("", "Unable to save changes. " +
336: "Try again, and if the problem persists, " +
337: "see your system administrator.");
338: }
339: }
340: return View(studentToUpdate);
341: }
342: #endregion
343: #endif
344:
345: // GET: Students/Delete/5
346: #region snippet_DeleteGet
347: public async Task<IActionResult> Delete(int? id, bool? saveChangesError = false)
348: {
349: if (id == null)
350: {
351: return NotFound();
352: }
353:
354: var student = await _context.Students
355: .AsNoTracking()
356: .SingleOrDefaultAsync(m => m.ID == id);
357: if (student == null)
358: {
359: return NotFound();
360: }
361:
362: if (saveChangesError.GetValueOrDefault())
363: {
364: ViewData["ErrorMessage"] =
365: "Delete failed. Try again, and if the problem persists " +
366: "see your system administrator.";
367: }
368:
369: return View(student);
370: }
371: #endregion
372: // POST: Students/Delete/5
373: #if (DeleteWithReadFirst)
374: #region snippet_DeleteWithReadFirst
375: [HttpPost, ActionName("Delete")]
376: [ValidateAntiForgeryToken]
377: public async Task<IActionResult> DeleteConfirmed(int id)
378: {
379: var student = await _context.Students
380: .AsNoTracking()
381: .SingleOrDefaultAsync(m => m.ID == id);
382: if (student == null)
383: {
384: return RedirectToAction(nameof(Index));
385: }
386:
387: try
388: {
389: _context.Students.Remove(student);
390: await _context.SaveChangesAsync();
391: return RedirectToAction(nameof(Index));
392: }
393: catch (DbUpdateException /* ex */)
394: {
395: //Log the error (uncomment ex variable name and write a log.)
396: return RedirectToAction(nameof(Delete), new { id = id, saveChangesError = true });
397: }
398: }
399: #endregion
400: #elif (DeleteWithoutReadFirst)
401: #region snippet_DeleteWithoutReadFirst
402: [HttpPost]
403: [ValidateAntiForgeryToken]
404: public async Task<IActionResult> DeleteConfirmed(int id)
405: {
406: try
407: {
408: Student studentToDelete = new Student() { ID = id };
409: _context.Entry(studentToDelete).State = EntityState.Deleted;
410: await _context.SaveChangesAsync();
411: return RedirectToAction(nameof(Index));
412: }
413: catch (DbUpdateException /* ex */)
414: {
415: //Log the error (uncomment ex variable name and write a log.)
416: return RedirectToAction(nameof(Delete), new { id = id, saveChangesError = true });
417: }
418: }
419: #endregion
420: #endif
421: }
422: }
These are ternary statements. The first one specifies that if the sortOrder
parameter is null or empty, NameSortParm should be set to “name_desc”; otherwise, it should be set to an empty string. These two statements enable the view to set the column heading hyperlinks as follows:
Current sort order | Last Name Hyperlink | Date Hyperlink |
---|---|---|
Last Name ascending | descending | ascending |
Last Name descending | ascending | ascending |
Date ascending | ascending | descending |
Date descending | ascending | ascending |
The method uses LINQ to Entities to specify the column to sort by. The code creates an IQueryable
variable before the switch statement, modifies it in the switch statement, and calls the ToListAsync
method after the switch
statement. When you create and modify IQueryable
variables, no query is sent to the database. The query is not executed until you convert the IQueryable
object into a collection by calling a method such as ToListAsync
. Therefore, this code results in a single query that is not executed until the return View
statement.
This code could get verbose with a large number of columns. The last tutorial in this series shows how to write code that lets you pass the name of the OrderBy
column in a string variable.
Add column heading hyperlinks to the Student Index view
Replace the code in Views/Students/Index.cshtml, with the following code to add column heading hyperlinks. The changed lines are highlighted.
[!code-htmlMain]
1: @model IEnumerable<ContosoUniversity.Models.Student>
2:
3: @{
4: ViewData["Title"] = "Index";
5: }
6:
7: <h2>Index</h2>
8:
9: <p>
10: <a asp-action="Create">Create New</a>
11: </p>
12: <table class="table">
13: <thead>
14: <tr>
15: <th>
16: <a asp-action="Index" asp-route-sortOrder="@ViewData["NameSortParm"]">@Html.DisplayNameFor(model => model.LastName)</a>
17: </th>
18: <th>
19: @Html.DisplayNameFor(model => model.FirstMidName)
20: </th>
21: <th>
22: <a asp-action="Index" asp-route-sortOrder="@ViewData["DateSortParm"]">@Html.DisplayNameFor(model => model.EnrollmentDate)</a>
23: </th>
24: <th></th>
25: </tr>
26: </thead>
27: <tbody>
28: @foreach (var item in Model) {
29: <tr>
30: <td>
31: @Html.DisplayFor(modelItem => item.LastName)
32: </td>
33: <td>
34: @Html.DisplayFor(modelItem => item.FirstMidName)
35: </td>
36: <td>
37: @Html.DisplayFor(modelItem => item.EnrollmentDate)
38: </td>
39: <td>
40: <a asp-action="Edit" asp-route-id="@item.ID">Edit</a> |
41: <a asp-action="Details" asp-route-id="@item.ID">Details</a> |
42: <a asp-action="Delete" asp-route-id="@item.ID">Delete</a>
43: </td>
44: </tr>
45: }
46: </tbody>
47: </table>
This code uses the information in ViewData
properties to set up hyperlinks with the appropriate query string values.
Run the app, select the Students tab, and click the Last Name and Enrollment Date column headings to verify that sorting works.
Add a Search Box to the Students Index page
To add filtering to the Students Index page, you’ll add a text box and a submit button to the view and make corresponding changes in the Index
method. The text box will let you enter a string to search for in the first name and last name fields.
Add filtering functionality to the Index method
In StudentsController.cs, replace the Index
method with the following code (the changes are highlighted).
[!code-csharpMain]
1: #define SortFilterPage //or ScaffoldedIndex or SortOnly or SortFilter or DynamicLinq
2: #define ReadFirst //or CreateAndAttach
3: #define DeleteWithReadFirst // or DeleteWithoutReadFirst
4:
5: using System.Linq;
6: using System.Threading.Tasks;
7: using Microsoft.AspNetCore.Mvc;
8: using Microsoft.AspNetCore.Mvc.Rendering;
9: using Microsoft.EntityFrameworkCore;
10: using ContosoUniversity.Data;
11: using ContosoUniversity.Models;
12: using System;
13: using Microsoft.Extensions.Logging;
14:
15: #region snippet_Context
16: namespace ContosoUniversity.Controllers
17: {
18: public class StudentsController : Controller
19: {
20: private readonly SchoolContext _context;
21:
22: public StudentsController(SchoolContext context)
23: {
24: _context = context;
25: }
26: #endregion
27:
28: // GET: Students
29:
30: #if (ScaffoldedIndex)
31: #region snippet_ScaffoldedIndex
32: public async Task<IActionResult> Index()
33: {
34: return View(await _context.Students.ToListAsync());
35: }
36: #endregion
37: #elif (SortOnly)
38: #region snippet_SortOnly
39: public async Task<IActionResult> Index(string sortOrder)
40: {
41: ViewData["NameSortParm"] = String.IsNullOrEmpty(sortOrder) ? "name_desc" : "";
42: ViewData["DateSortParm"] = sortOrder == "Date" ? "date_desc" : "Date";
43: var students = from s in _context.Students
44: select s;
45: switch (sortOrder)
46: {
47: case "name_desc":
48: students = students.OrderByDescending(s => s.LastName);
49: break;
50: case "Date":
51: students = students.OrderBy(s => s.EnrollmentDate);
52: break;
53: case "date_desc":
54: students = students.OrderByDescending(s => s.EnrollmentDate);
55: break;
56: default:
57: students = students.OrderBy(s => s.LastName);
58: break;
59: }
60: return View(await students.AsNoTracking().ToListAsync());
61: }
62: #endregion
63: #elif (SortFilter)
64: #region snippet_SortFilter
65: public async Task<IActionResult> Index(string sortOrder, string searchString)
66: {
67: ViewData["NameSortParm"] = String.IsNullOrEmpty(sortOrder) ? "name_desc" : "";
68: ViewData["DateSortParm"] = sortOrder == "Date" ? "date_desc" : "Date";
69: ViewData["CurrentFilter"] = searchString;
70:
71: var students = from s in _context.Students
72: select s;
73: if (!String.IsNullOrEmpty(searchString))
74: {
75: students = students.Where(s => s.LastName.Contains(searchString)
76: || s.FirstMidName.Contains(searchString));
77: }
78: switch (sortOrder)
79: {
80: case "name_desc":
81: students = students.OrderByDescending(s => s.LastName);
82: break;
83: case "Date":
84: students = students.OrderBy(s => s.EnrollmentDate);
85: break;
86: case "date_desc":
87: students = students.OrderByDescending(s => s.EnrollmentDate);
88: break;
89: default:
90: students = students.OrderBy(s => s.LastName);
91: break;
92: }
93: return View(await students.AsNoTracking().ToListAsync());
94: }
95: #endregion
96: #elif (SortFilterPage)
97: #region snippet_SortFilterPage
98: public async Task<IActionResult> Index(
99: string sortOrder,
100: string currentFilter,
101: string searchString,
102: int? page)
103: {
104: ViewData["CurrentSort"] = sortOrder;
105: ViewData["NameSortParm"] = String.IsNullOrEmpty(sortOrder) ? "name_desc" : "";
106: ViewData["DateSortParm"] = sortOrder == "Date" ? "date_desc" : "Date";
107:
108: if (searchString != null)
109: {
110: page = 1;
111: }
112: else
113: {
114: searchString = currentFilter;
115: }
116:
117: ViewData["CurrentFilter"] = searchString;
118:
119: var students = from s in _context.Students
120: select s;
121: if (!String.IsNullOrEmpty(searchString))
122: {
123: students = students.Where(s => s.LastName.Contains(searchString)
124: || s.FirstMidName.Contains(searchString));
125: }
126: switch (sortOrder)
127: {
128: case "name_desc":
129: students = students.OrderByDescending(s => s.LastName);
130: break;
131: case "Date":
132: students = students.OrderBy(s => s.EnrollmentDate);
133: break;
134: case "date_desc":
135: students = students.OrderByDescending(s => s.EnrollmentDate);
136: break;
137: default:
138: students = students.OrderBy(s => s.LastName);
139: break;
140: }
141:
142: int pageSize = 3;
143: return View(await PaginatedList<Student>.CreateAsync(students.AsNoTracking(), page ?? 1, pageSize));
144: }
145: #endregion
146: #elif (DynamicLinq)
147: #region snippet_DynamicLinq
148: public async Task<IActionResult> Index(
149: string sortOrder,
150: string currentFilter,
151: string searchString,
152: int? page)
153: {
154: ViewData["CurrentSort"] = sortOrder;
155: ViewData["NameSortParm"] =
156: String.IsNullOrEmpty(sortOrder) ? "LastName_desc" : "";
157: ViewData["DateSortParm"] =
158: sortOrder == "EnrollmentDate" ? "EnrollmentDate_desc" : "EnrollmentDate";
159:
160: if (searchString != null)
161: {
162: page = 1;
163: }
164: else
165: {
166: searchString = currentFilter;
167: }
168:
169: ViewData["CurrentFilter"] = searchString;
170:
171: var students = from s in _context.Students
172: select s;
173:
174: if (!String.IsNullOrEmpty(searchString))
175: {
176: students = students.Where(s => s.LastName.Contains(searchString)
177: || s.FirstMidName.Contains(searchString));
178: }
179:
180: if (string.IsNullOrEmpty(sortOrder))
181: {
182: sortOrder = "LastName";
183: }
184:
185: bool descending = false;
186: if (sortOrder.EndsWith("_desc"))
187: {
188: sortOrder = sortOrder.Substring(0, sortOrder.Length - 5);
189: descending = true;
190: }
191:
192: if (descending)
193: {
194: students = students.OrderByDescending(e => EF.Property<object>(e, sortOrder));
195: }
196: else
197: {
198: students = students.OrderBy(e => EF.Property<object>(e, sortOrder));
199: }
200:
201: int pageSize = 3;
202: return View(await PaginatedList<Student>.CreateAsync(students.AsNoTracking(),
203: page ?? 1, pageSize));
204: }
205: #endregion
206: #endif
207:
208: // GET: Students/Details/5
209: #region snippet_Details
210: public async Task<IActionResult> Details(int? id)
211: {
212: if (id == null)
213: {
214: return NotFound();
215: }
216:
217: var student = await _context.Students
218: .Include(s => s.Enrollments)
219: .ThenInclude(e => e.Course)
220: .AsNoTracking()
221: .SingleOrDefaultAsync(m => m.ID == id);
222:
223: if (student == null)
224: {
225: return NotFound();
226: }
227:
228: return View(student);
229: }
230: #endregion
231:
232: // GET: Students/Create
233: public IActionResult Create()
234: {
235: return View();
236: }
237:
238: // POST: Students/Create
239: #region snippet_Create
240: [HttpPost]
241: [ValidateAntiForgeryToken]
242: public async Task<IActionResult> Create(
243: [Bind("EnrollmentDate,FirstMidName,LastName")] Student student)
244: {
245: try
246: {
247: if (ModelState.IsValid)
248: {
249: _context.Add(student);
250: await _context.SaveChangesAsync();
251: return RedirectToAction(nameof(Index));
252: }
253: }
254: catch (DbUpdateException /* ex */)
255: {
256: //Log the error (uncomment ex variable name and write a log.
257: ModelState.AddModelError("", "Unable to save changes. " +
258: "Try again, and if the problem persists " +
259: "see your system administrator.");
260: }
261: return View(student);
262: }
263: #endregion
264:
265: // GET: Students/Edit/5
266: public async Task<IActionResult> Edit(int? id)
267: {
268: if (id == null)
269: {
270: return NotFound();
271: }
272:
273: var student = await _context.Students
274: .AsNoTracking()
275: .SingleOrDefaultAsync(m => m.ID == id);
276: if (student == null)
277: {
278: return NotFound();
279: }
280: return View(student);
281: }
282:
283: // POST: Students/Edit/5
284: #if (CreateAndAttach)
285: #region snippet_CreateAndAttach
286: public async Task<IActionResult> Edit(int id, [Bind("ID,EnrollmentDate,FirstMidName,LastName")] Student student)
287: {
288: if (id != student.ID)
289: {
290: return NotFound();
291: }
292: if (ModelState.IsValid)
293: {
294: try
295: {
296: _context.Update(student);
297: await _context.SaveChangesAsync();
298: return RedirectToAction(nameof(Index));
299: }
300: catch (DbUpdateException /* ex */)
301: {
302: //Log the error (uncomment ex variable name and write a log.)
303: ModelState.AddModelError("", "Unable to save changes. " +
304: "Try again, and if the problem persists, " +
305: "see your system administrator.");
306: }
307: }
308: return View(student);
309: }
310: #endregion
311: #elif (ReadFirst)
312: #region snippet_ReadFirst
313: [HttpPost, ActionName("Edit")]
314: [ValidateAntiForgeryToken]
315: public async Task<IActionResult> EditPost(int? id)
316: {
317: if (id == null)
318: {
319: return NotFound();
320: }
321: var studentToUpdate = await _context.Students.SingleOrDefaultAsync(s => s.ID == id);
322: if (await TryUpdateModelAsync<Student>(
323: studentToUpdate,
324: "",
325: s => s.FirstMidName, s => s.LastName, s => s.EnrollmentDate))
326: {
327: try
328: {
329: await _context.SaveChangesAsync();
330: return RedirectToAction(nameof(Index));
331: }
332: catch (DbUpdateException /* ex */)
333: {
334: //Log the error (uncomment ex variable name and write a log.)
335: ModelState.AddModelError("", "Unable to save changes. " +
336: "Try again, and if the problem persists, " +
337: "see your system administrator.");
338: }
339: }
340: return View(studentToUpdate);
341: }
342: #endregion
343: #endif
344:
345: // GET: Students/Delete/5
346: #region snippet_DeleteGet
347: public async Task<IActionResult> Delete(int? id, bool? saveChangesError = false)
348: {
349: if (id == null)
350: {
351: return NotFound();
352: }
353:
354: var student = await _context.Students
355: .AsNoTracking()
356: .SingleOrDefaultAsync(m => m.ID == id);
357: if (student == null)
358: {
359: return NotFound();
360: }
361:
362: if (saveChangesError.GetValueOrDefault())
363: {
364: ViewData["ErrorMessage"] =
365: "Delete failed. Try again, and if the problem persists " +
366: "see your system administrator.";
367: }
368:
369: return View(student);
370: }
371: #endregion
372: // POST: Students/Delete/5
373: #if (DeleteWithReadFirst)
374: #region snippet_DeleteWithReadFirst
375: [HttpPost, ActionName("Delete")]
376: [ValidateAntiForgeryToken]
377: public async Task<IActionResult> DeleteConfirmed(int id)
378: {
379: var student = await _context.Students
380: .AsNoTracking()
381: .SingleOrDefaultAsync(m => m.ID == id);
382: if (student == null)
383: {
384: return RedirectToAction(nameof(Index));
385: }
386:
387: try
388: {
389: _context.Students.Remove(student);
390: await _context.SaveChangesAsync();
391: return RedirectToAction(nameof(Index));
392: }
393: catch (DbUpdateException /* ex */)
394: {
395: //Log the error (uncomment ex variable name and write a log.)
396: return RedirectToAction(nameof(Delete), new { id = id, saveChangesError = true });
397: }
398: }
399: #endregion
400: #elif (DeleteWithoutReadFirst)
401: #region snippet_DeleteWithoutReadFirst
402: [HttpPost]
403: [ValidateAntiForgeryToken]
404: public async Task<IActionResult> DeleteConfirmed(int id)
405: {
406: try
407: {
408: Student studentToDelete = new Student() { ID = id };
409: _context.Entry(studentToDelete).State = EntityState.Deleted;
410: await _context.SaveChangesAsync();
411: return RedirectToAction(nameof(Index));
412: }
413: catch (DbUpdateException /* ex */)
414: {
415: //Log the error (uncomment ex variable name and write a log.)
416: return RedirectToAction(nameof(Delete), new { id = id, saveChangesError = true });
417: }
418: }
419: #endregion
420: #endif
421: }
422: }
You’ve added a searchString
parameter to the Index
method. The search string value is received from a text box that you’ll add to the Index view. You’ve also added to the LINQ statement a where clause that selects only students whose first name or last name contains the search string. The statement that adds the where clause is executed only if there’s a value to search for.
[!NOTE] Here you are calling the
Where
method on anIQueryable
object, and the filter will be processed on the server. In some scenarios you might be calling theWhere
method as an extension method on an in-memory collection. (For example, suppose you change the reference to_context.Students
so that instead of an EFDbSet
it references a repository method that returns anIEnumerable
collection.) The result would normally be the same but in some cases may be different.For example, the .NET Framework implementation of the
Contains
method performs a case-sensitive comparison by default, but in SQL Server this is determined by the collation setting of the SQL Server instance. That setting defaults to case-insensitive. You could call theToUpper
method to make the test explicitly case-insensitive: Where(s => s.LastName.ToUpper().Contains(searchString.ToUpper()). That would ensure that results stay the same if you change the code later to use a repository which returns anIEnumerable
collection instead of anIQueryable
object. (When you call theContains
method on anIEnumerable
collection, you get the .NET Framework implementation; when you call it on anIQueryable
object, you get the database provider implementation.) However, there is a performance penalty for this solution. TheToUpper
code would put a function in the WHERE clause of the TSQL SELECT statement. That would prevent the optimizer from using an index. Given that SQL is mostly installed as case-insensitive, it’s best to avoid theToUpper
code until you migrate to a case-sensitive data store.
Add a Search Box to the Student Index View
In Views/Student/Index.cshtml, add the highlighted code immediately before the opening table tag in order to create a caption, a text box, and a Search button.
[!code-htmlMain]
1: @model IEnumerable<ContosoUniversity.Models.Student>
2:
3: @{
4: ViewData["Title"] = "Index";
5: }
6:
7: <h2>Index</h2>
8:
9: <p>
10: <a asp-action="Create">Create New</a>
11: </p>
12:
13: <form asp-action="Index" method="get">
14: <div class="form-actions no-color">
15: <p>
16: Find by name: <input type="text" name="SearchString" value="@ViewData["currentFilter"]" />
17: <input type="submit" value="Search" class="btn btn-default" /> |
18: <a asp-action="Index">Back to Full List</a>
19: </p>
20: </div>
21: </form>
22:
23: <table class="table">
24: <thead>
25: <tr>
26: <th>
27: <a asp-action="Index" asp-route-sortOrder="@ViewData["NameSortParm"]">@Html.DisplayNameFor(model => model.LastName)</a>
28: </th>
29: <th>
30: @Html.DisplayNameFor(model => model.FirstMidName)
31: </th>
32: <th>
33: <a asp-action="Index" asp-route-sortOrder="@ViewData["DateSortParm"]">@Html.DisplayNameFor(model => model.EnrollmentDate)</a>
34: </th>
35: <th></th>
36: </tr>
37: </thead>
38: <tbody>
39: @foreach (var item in Model)
40: {
41: <tr>
42: <td>
43: @Html.DisplayFor(modelItem => item.LastName)
44: </td>
45: <td>
46: @Html.DisplayFor(modelItem => item.FirstMidName)
47: </td>
48: <td>
49: @Html.DisplayFor(modelItem => item.EnrollmentDate)
50: </td>
51: <td>
52: <a asp-action="Edit" asp-route-id="@item.ID">Edit</a> |
53: <a asp-action="Details" asp-route-id="@item.ID">Details</a> |
54: <a asp-action="Delete" asp-route-id="@item.ID">Delete</a>
55: </td>
56: </tr>
57: }
58: </tbody>
59: </table>
This code uses the <form>
(xref:)tag helper to add the search text box and button. By default, the <form>
tag helper submits form data with a POST, which means that parameters are passed in the HTTP message body and not in the URL as query strings. When you specify HTTP GET, the form data is passed in the URL as query strings, which enables users to bookmark the URL. The W3C guidelines recommend that you should use GET when the action does not result in an update.
Run the app, select the Students tab, enter a search string, and click Search to verify that filtering is working.
Notice that the URL contains the search string.
If you bookmark this page, you’ll get the filtered list when you use the bookmark. Adding method="get"
to the form
tag is what caused the query string to be generated.
At this stage, if you click a column heading sort link you’ll lose the filter value that you entered in the Search box. You’ll fix that in the next section.
Add paging functionality to the Students Index page
To add paging to the Students Index page, you’ll create a PaginatedList
class that uses Skip
and Take
statements to filter data on the server instead of always retrieving all rows of the table. Then you’ll make additional changes in the Index
method and add paging buttons to the Index
view. The following illustration shows the paging buttons.
In the project folder, create PaginatedList.cs
, and then replace the template code with the following code.
[!code-csharpMain]
1: using System;
2: using System.Collections.Generic;
3: using System.Linq;
4: using System.Threading.Tasks;
5: using Microsoft.EntityFrameworkCore;
6:
7: namespace ContosoUniversity
8: {
9: public class PaginatedList<T> : List<T>
10: {
11: public int PageIndex { get; private set; }
12: public int TotalPages { get; private set; }
13:
14: public PaginatedList(List<T> items, int count, int pageIndex, int pageSize)
15: {
16: PageIndex = pageIndex;
17: TotalPages = (int)Math.Ceiling(count / (double)pageSize);
18:
19: this.AddRange(items);
20: }
21:
22: public bool HasPreviousPage
23: {
24: get
25: {
26: return (PageIndex > 1);
27: }
28: }
29:
30: public bool HasNextPage
31: {
32: get
33: {
34: return (PageIndex < TotalPages);
35: }
36: }
37:
38: public static async Task<PaginatedList<T>> CreateAsync(IQueryable<T> source, int pageIndex, int pageSize)
39: {
40: var count = await source.CountAsync();
41: var items = await source.Skip((pageIndex - 1) * pageSize).Take(pageSize).ToListAsync();
42: return new PaginatedList<T>(items, count, pageIndex, pageSize);
43: }
44: }
45: }
The CreateAsync
method in this code takes page size and page number and applies the appropriate Skip
and Take
statements to the IQueryable
. When ToListAsync
is called on the IQueryable
, it will return a List containing only the requested page. The properties HasPreviousPage
and HasNextPage
can be used to enable or disable Previous and Next paging buttons.
A CreateAsync
method is used instead of a constructor to create the PaginatedList<T>
object because constructors can’t run asynchronous code.
Add paging functionality to the Index method
In StudentsController.cs, replace the Index
method with the following code.
[!code-csharpMain]
1: #define SortFilterPage //or ScaffoldedIndex or SortOnly or SortFilter or DynamicLinq
2: #define ReadFirst //or CreateAndAttach
3: #define DeleteWithReadFirst // or DeleteWithoutReadFirst
4:
5: using System.Linq;
6: using System.Threading.Tasks;
7: using Microsoft.AspNetCore.Mvc;
8: using Microsoft.AspNetCore.Mvc.Rendering;
9: using Microsoft.EntityFrameworkCore;
10: using ContosoUniversity.Data;
11: using ContosoUniversity.Models;
12: using System;
13: using Microsoft.Extensions.Logging;
14:
15: #region snippet_Context
16: namespace ContosoUniversity.Controllers
17: {
18: public class StudentsController : Controller
19: {
20: private readonly SchoolContext _context;
21:
22: public StudentsController(SchoolContext context)
23: {
24: _context = context;
25: }
26: #endregion
27:
28: // GET: Students
29:
30: #if (ScaffoldedIndex)
31: #region snippet_ScaffoldedIndex
32: public async Task<IActionResult> Index()
33: {
34: return View(await _context.Students.ToListAsync());
35: }
36: #endregion
37: #elif (SortOnly)
38: #region snippet_SortOnly
39: public async Task<IActionResult> Index(string sortOrder)
40: {
41: ViewData["NameSortParm"] = String.IsNullOrEmpty(sortOrder) ? "name_desc" : "";
42: ViewData["DateSortParm"] = sortOrder == "Date" ? "date_desc" : "Date";
43: var students = from s in _context.Students
44: select s;
45: switch (sortOrder)
46: {
47: case "name_desc":
48: students = students.OrderByDescending(s => s.LastName);
49: break;
50: case "Date":
51: students = students.OrderBy(s => s.EnrollmentDate);
52: break;
53: case "date_desc":
54: students = students.OrderByDescending(s => s.EnrollmentDate);
55: break;
56: default:
57: students = students.OrderBy(s => s.LastName);
58: break;
59: }
60: return View(await students.AsNoTracking().ToListAsync());
61: }
62: #endregion
63: #elif (SortFilter)
64: #region snippet_SortFilter
65: public async Task<IActionResult> Index(string sortOrder, string searchString)
66: {
67: ViewData["NameSortParm"] = String.IsNullOrEmpty(sortOrder) ? "name_desc" : "";
68: ViewData["DateSortParm"] = sortOrder == "Date" ? "date_desc" : "Date";
69: ViewData["CurrentFilter"] = searchString;
70:
71: var students = from s in _context.Students
72: select s;
73: if (!String.IsNullOrEmpty(searchString))
74: {
75: students = students.Where(s => s.LastName.Contains(searchString)
76: || s.FirstMidName.Contains(searchString));
77: }
78: switch (sortOrder)
79: {
80: case "name_desc":
81: students = students.OrderByDescending(s => s.LastName);
82: break;
83: case "Date":
84: students = students.OrderBy(s => s.EnrollmentDate);
85: break;
86: case "date_desc":
87: students = students.OrderByDescending(s => s.EnrollmentDate);
88: break;
89: default:
90: students = students.OrderBy(s => s.LastName);
91: break;
92: }
93: return View(await students.AsNoTracking().ToListAsync());
94: }
95: #endregion
96: #elif (SortFilterPage)
97: #region snippet_SortFilterPage
98: public async Task<IActionResult> Index(
99: string sortOrder,
100: string currentFilter,
101: string searchString,
102: int? page)
103: {
104: ViewData["CurrentSort"] = sortOrder;
105: ViewData["NameSortParm"] = String.IsNullOrEmpty(sortOrder) ? "name_desc" : "";
106: ViewData["DateSortParm"] = sortOrder == "Date" ? "date_desc" : "Date";
107:
108: if (searchString != null)
109: {
110: page = 1;
111: }
112: else
113: {
114: searchString = currentFilter;
115: }
116:
117: ViewData["CurrentFilter"] = searchString;
118:
119: var students = from s in _context.Students
120: select s;
121: if (!String.IsNullOrEmpty(searchString))
122: {
123: students = students.Where(s => s.LastName.Contains(searchString)
124: || s.FirstMidName.Contains(searchString));
125: }
126: switch (sortOrder)
127: {
128: case "name_desc":
129: students = students.OrderByDescending(s => s.LastName);
130: break;
131: case "Date":
132: students = students.OrderBy(s => s.EnrollmentDate);
133: break;
134: case "date_desc":
135: students = students.OrderByDescending(s => s.EnrollmentDate);
136: break;
137: default:
138: students = students.OrderBy(s => s.LastName);
139: break;
140: }
141:
142: int pageSize = 3;
143: return View(await PaginatedList<Student>.CreateAsync(students.AsNoTracking(), page ?? 1, pageSize));
144: }
145: #endregion
146: #elif (DynamicLinq)
147: #region snippet_DynamicLinq
148: public async Task<IActionResult> Index(
149: string sortOrder,
150: string currentFilter,
151: string searchString,
152: int? page)
153: {
154: ViewData["CurrentSort"] = sortOrder;
155: ViewData["NameSortParm"] =
156: String.IsNullOrEmpty(sortOrder) ? "LastName_desc" : "";
157: ViewData["DateSortParm"] =
158: sortOrder == "EnrollmentDate" ? "EnrollmentDate_desc" : "EnrollmentDate";
159:
160: if (searchString != null)
161: {
162: page = 1;
163: }
164: else
165: {
166: searchString = currentFilter;
167: }
168:
169: ViewData["CurrentFilter"] = searchString;
170:
171: var students = from s in _context.Students
172: select s;
173:
174: if (!String.IsNullOrEmpty(searchString))
175: {
176: students = students.Where(s => s.LastName.Contains(searchString)
177: || s.FirstMidName.Contains(searchString));
178: }
179:
180: if (string.IsNullOrEmpty(sortOrder))
181: {
182: sortOrder = "LastName";
183: }
184:
185: bool descending = false;
186: if (sortOrder.EndsWith("_desc"))
187: {
188: sortOrder = sortOrder.Substring(0, sortOrder.Length - 5);
189: descending = true;
190: }
191:
192: if (descending)
193: {
194: students = students.OrderByDescending(e => EF.Property<object>(e, sortOrder));
195: }
196: else
197: {
198: students = students.OrderBy(e => EF.Property<object>(e, sortOrder));
199: }
200:
201: int pageSize = 3;
202: return View(await PaginatedList<Student>.CreateAsync(students.AsNoTracking(),
203: page ?? 1, pageSize));
204: }
205: #endregion
206: #endif
207:
208: // GET: Students/Details/5
209: #region snippet_Details
210: public async Task<IActionResult> Details(int? id)
211: {
212: if (id == null)
213: {
214: return NotFound();
215: }
216:
217: var student = await _context.Students
218: .Include(s => s.Enrollments)
219: .ThenInclude(e => e.Course)
220: .AsNoTracking()
221: .SingleOrDefaultAsync(m => m.ID == id);
222:
223: if (student == null)
224: {
225: return NotFound();
226: }
227:
228: return View(student);
229: }
230: #endregion
231:
232: // GET: Students/Create
233: public IActionResult Create()
234: {
235: return View();
236: }
237:
238: // POST: Students/Create
239: #region snippet_Create
240: [HttpPost]
241: [ValidateAntiForgeryToken]
242: public async Task<IActionResult> Create(
243: [Bind("EnrollmentDate,FirstMidName,LastName")] Student student)
244: {
245: try
246: {
247: if (ModelState.IsValid)
248: {
249: _context.Add(student);
250: await _context.SaveChangesAsync();
251: return RedirectToAction(nameof(Index));
252: }
253: }
254: catch (DbUpdateException /* ex */)
255: {
256: //Log the error (uncomment ex variable name and write a log.
257: ModelState.AddModelError("", "Unable to save changes. " +
258: "Try again, and if the problem persists " +
259: "see your system administrator.");
260: }
261: return View(student);
262: }
263: #endregion
264:
265: // GET: Students/Edit/5
266: public async Task<IActionResult> Edit(int? id)
267: {
268: if (id == null)
269: {
270: return NotFound();
271: }
272:
273: var student = await _context.Students
274: .AsNoTracking()
275: .SingleOrDefaultAsync(m => m.ID == id);
276: if (student == null)
277: {
278: return NotFound();
279: }
280: return View(student);
281: }
282:
283: // POST: Students/Edit/5
284: #if (CreateAndAttach)
285: #region snippet_CreateAndAttach
286: public async Task<IActionResult> Edit(int id, [Bind("ID,EnrollmentDate,FirstMidName,LastName")] Student student)
287: {
288: if (id != student.ID)
289: {
290: return NotFound();
291: }
292: if (ModelState.IsValid)
293: {
294: try
295: {
296: _context.Update(student);
297: await _context.SaveChangesAsync();
298: return RedirectToAction(nameof(Index));
299: }
300: catch (DbUpdateException /* ex */)
301: {
302: //Log the error (uncomment ex variable name and write a log.)
303: ModelState.AddModelError("", "Unable to save changes. " +
304: "Try again, and if the problem persists, " +
305: "see your system administrator.");
306: }
307: }
308: return View(student);
309: }
310: #endregion
311: #elif (ReadFirst)
312: #region snippet_ReadFirst
313: [HttpPost, ActionName("Edit")]
314: [ValidateAntiForgeryToken]
315: public async Task<IActionResult> EditPost(int? id)
316: {
317: if (id == null)
318: {
319: return NotFound();
320: }
321: var studentToUpdate = await _context.Students.SingleOrDefaultAsync(s => s.ID == id);
322: if (await TryUpdateModelAsync<Student>(
323: studentToUpdate,
324: "",
325: s => s.FirstMidName, s => s.LastName, s => s.EnrollmentDate))
326: {
327: try
328: {
329: await _context.SaveChangesAsync();
330: return RedirectToAction(nameof(Index));
331: }
332: catch (DbUpdateException /* ex */)
333: {
334: //Log the error (uncomment ex variable name and write a log.)
335: ModelState.AddModelError("", "Unable to save changes. " +
336: "Try again, and if the problem persists, " +
337: "see your system administrator.");
338: }
339: }
340: return View(studentToUpdate);
341: }
342: #endregion
343: #endif
344:
345: // GET: Students/Delete/5
346: #region snippet_DeleteGet
347: public async Task<IActionResult> Delete(int? id, bool? saveChangesError = false)
348: {
349: if (id == null)
350: {
351: return NotFound();
352: }
353:
354: var student = await _context.Students
355: .AsNoTracking()
356: .SingleOrDefaultAsync(m => m.ID == id);
357: if (student == null)
358: {
359: return NotFound();
360: }
361:
362: if (saveChangesError.GetValueOrDefault())
363: {
364: ViewData["ErrorMessage"] =
365: "Delete failed. Try again, and if the problem persists " +
366: "see your system administrator.";
367: }
368:
369: return View(student);
370: }
371: #endregion
372: // POST: Students/Delete/5
373: #if (DeleteWithReadFirst)
374: #region snippet_DeleteWithReadFirst
375: [HttpPost, ActionName("Delete")]
376: [ValidateAntiForgeryToken]
377: public async Task<IActionResult> DeleteConfirmed(int id)
378: {
379: var student = await _context.Students
380: .AsNoTracking()
381: .SingleOrDefaultAsync(m => m.ID == id);
382: if (student == null)
383: {
384: return RedirectToAction(nameof(Index));
385: }
386:
387: try
388: {
389: _context.Students.Remove(student);
390: await _context.SaveChangesAsync();
391: return RedirectToAction(nameof(Index));
392: }
393: catch (DbUpdateException /* ex */)
394: {
395: //Log the error (uncomment ex variable name and write a log.)
396: return RedirectToAction(nameof(Delete), new { id = id, saveChangesError = true });
397: }
398: }
399: #endregion
400: #elif (DeleteWithoutReadFirst)
401: #region snippet_DeleteWithoutReadFirst
402: [HttpPost]
403: [ValidateAntiForgeryToken]
404: public async Task<IActionResult> DeleteConfirmed(int id)
405: {
406: try
407: {
408: Student studentToDelete = new Student() { ID = id };
409: _context.Entry(studentToDelete).State = EntityState.Deleted;
410: await _context.SaveChangesAsync();
411: return RedirectToAction(nameof(Index));
412: }
413: catch (DbUpdateException /* ex */)
414: {
415: //Log the error (uncomment ex variable name and write a log.)
416: return RedirectToAction(nameof(Delete), new { id = id, saveChangesError = true });
417: }
418: }
419: #endregion
420: #endif
421: }
422: }
This code adds a page number parameter, a current sort order parameter, and a current filter parameter to the method signature.
public async Task<IActionResult> Index(
string sortOrder,
string currentFilter,
string searchString,
int? page)
The first time the page is displayed, or if the user hasn’t clicked a paging or sorting link, all the parameters will be null. If a paging link is clicked, the page variable will contain the page number to display.
The ViewData
element named CurrentSort provides the view with the current sort order, because this must be included in the paging links in order to keep the sort order the same while paging.
The ViewData
element named CurrentFilter provides the view with the current filter string. This value must be included in the paging links in order to maintain the filter settings during paging, and it must be restored to the text box when the page is redisplayed.
If the search string is changed during paging, the page has to be reset to 1, because the new filter can result in different data to display. The search string is changed when a value is entered in the text box and the Submit button is pressed. In that case, the searchString
parameter is not null.
At the end of the Index
method, the PaginatedList.CreateAsync
method converts the student query to a single page of students in a collection type that supports paging. That single page of students is then passed to the view.
return View(await PaginatedList<Student>.CreateAsync(students.AsNoTracking(), page ?? 1, pageSize));
The PaginatedList.CreateAsync
method takes a page number. The two question marks represent the null-coalescing operator. The null-coalescing operator defines a default value for a nullable type; the expression (page ?? 1)
means return the value of page
if it has a value, or return 1 if page
is null.
Add paging links to the Student Index view
In Views/Students/Index.cshtml, replace the existing code with the following code. The changes are highlighted.
[!code-htmlMain]
1: @model PaginatedList<ContosoUniversity.Models.Student>
2:
3: @{
4: ViewData["Title"] = "Index";
5: }
6:
7: <h2>Index</h2>
8:
9: <p>
10: <a asp-action="Create">Create New</a>
11: </p>
12:
13: <form asp-action="Index" method="get">
14: <div class="form-actions no-color">
15: <p>
16: Find by name: <input type="text" name="SearchString" value="@ViewData["currentFilter"]" />
17: <input type="submit" value="Search" class="btn btn-default" /> |
18: <a asp-action="Index">Back to Full List</a>
19: </p>
20: </div>
21: </form>
22:
23: <table class="table">
24: <thead>
25: <tr>
26: <th>
27: <a asp-action="Index" asp-route-sortOrder="@ViewData["NameSortParm"]" asp-route-currentFilter="@ViewData["CurrentFilter"]">Last Name</a>
28: </th>
29: <th>
30: First Name
31: </th>
32: <th>
33: <a asp-action="Index" asp-route-sortOrder="@ViewData["DateSortParm"]" asp-route-currentFilter="@ViewData["CurrentFilter"]">Enrollment Date</a>
34: </th>
35: <th></th>
36: </tr>
37: </thead>
38: <tbody>
39: @foreach (var item in Model)
40: {
41: <tr>
42: <td>
43: @Html.DisplayFor(modelItem => item.LastName)
44: </td>
45: <td>
46: @Html.DisplayFor(modelItem => item.FirstMidName)
47: </td>
48: <td>
49: @Html.DisplayFor(modelItem => item.EnrollmentDate)
50: </td>
51: <td>
52: <a asp-action="Edit" asp-route-id="@item.ID">Edit</a> |
53: <a asp-action="Details" asp-route-id="@item.ID">Details</a> |
54: <a asp-action="Delete" asp-route-id="@item.ID">Delete</a>
55: </td>
56: </tr>
57: }
58: </tbody>
59: </table>
60:
61: @{
62: var prevDisabled = !Model.HasPreviousPage ? "disabled" : "";
63: var nextDisabled = !Model.HasNextPage ? "disabled" : "";
64: }
65:
66: <a asp-action="Index"
67: asp-route-sortOrder="@ViewData["CurrentSort"]"
68: asp-route-page="@(Model.PageIndex - 1)"
69: asp-route-currentFilter="@ViewData["CurrentFilter"]"
70: class="btn btn-default @prevDisabled">
71: Previous
72: </a>
73: <a asp-action="Index"
74: asp-route-sortOrder="@ViewData["CurrentSort"]"
75: asp-route-page="@(Model.PageIndex + 1)"
76: asp-route-currentFilter="@ViewData["CurrentFilter"]"
77: class="btn btn-default @nextDisabled">
78: Next
79: </a>
The @model
statement at the top of the page specifies that the view now gets a PaginatedList<T>
object instead of a List<T>
object.
The column header links use the query string to pass the current search string to the controller so that the user can sort within filter results:
The paging buttons are displayed by tag helpers:
<a asp-action="Index"
asp-route-sortOrder="@ViewData["CurrentSort"]"
asp-route-page="@(Model.PageIndex - 1)"
asp-route-currentFilter="@ViewData["CurrentFilter"]"
class="btn btn-default @prevDisabled">
Previous
</a>
Run the app and go to the Students page.
Click the paging links in different sort orders to make sure paging works. Then enter a search string and try paging again to verify that paging also works correctly with sorting and filtering.
Create an About page that shows Student statistics
For the Contoso University website’s About page, you’ll display how many students have enrolled for each enrollment date. This requires grouping and simple calculations on the groups. To accomplish this, you’ll do the following:
Create a view model class for the data that you need to pass to the view.
Modify the About method in the Home controller.
Modify the About view.
Create the view model
Create a SchoolViewModels folder in the Models folder.
In the new folder, add a class file EnrollmentDateGroup.cs and replace the template code with the following code:
[!code-csharpMain]
1: using System;
2: using System.ComponentModel.DataAnnotations;
3:
4: namespace ContosoUniversity.Models.SchoolViewModels
5: {
6: public class EnrollmentDateGroup
7: {
8: [DataType(DataType.Date)]
9: public DateTime? EnrollmentDate { get; set; }
10:
11: public int StudentCount { get; set; }
12: }
13: }
Modify the Home Controller
In HomeController.cs, add the following using statements at the top of the file:
[!code-csharpMain]
1: #define UseDbSet // or UseRawSQL
2:
3: using System.Collections.Generic;
4: using System.Linq;
5: using System.Threading.Tasks;
6: using Microsoft.AspNetCore.Mvc;
7: #region snippet_Usings2
8: using System.Data.Common;
9: #endregion
10: #region snippet_Usings1
11: using Microsoft.EntityFrameworkCore;
12: using ContosoUniversity.Data;
13: using ContosoUniversity.Models.SchoolViewModels;
14: #endregion
15:
16: namespace ContosoUniversity.Controllers
17: {
18: #region snippet_AddContext
19: public class HomeController : Controller
20: {
21: private readonly SchoolContext _context;
22:
23: public HomeController(SchoolContext context)
24: {
25: _context = context;
26: }
27: #endregion
28: public IActionResult Index()
29: {
30: return View();
31: }
32:
33: #if UseDbSet
34: #region snippet_UseDbSet
35: public async Task<ActionResult> About()
36: {
37: IQueryable<EnrollmentDateGroup> data =
38: from student in _context.Students
39: group student by student.EnrollmentDate into dateGroup
40: select new EnrollmentDateGroup()
41: {
42: EnrollmentDate = dateGroup.Key,
43: StudentCount = dateGroup.Count()
44: };
45: return View(await data.AsNoTracking().ToListAsync());
46: }
47: #endregion
48: #elif UseRawSQL
49: #region snippet_UseRawSQL
50: public async Task<ActionResult> About()
51: {
52: List<EnrollmentDateGroup> groups = new List<EnrollmentDateGroup>();
53: var conn = _context.Database.GetDbConnection();
54: try
55: {
56: await conn.OpenAsync();
57: using (var command = conn.CreateCommand())
58: {
59: string query = "SELECT EnrollmentDate, COUNT(*) AS StudentCount "
60: + "FROM Person "
61: + "WHERE Discriminator = 'Student' "
62: + "GROUP BY EnrollmentDate";
63: command.CommandText = query;
64: DbDataReader reader = await command.ExecuteReaderAsync();
65:
66: if (reader.HasRows)
67: {
68: while (await reader.ReadAsync())
69: {
70: var row = new EnrollmentDateGroup { EnrollmentDate = reader.GetDateTime(0), StudentCount = reader.GetInt32(1) };
71: groups.Add(row);
72: }
73: }
74: reader.Dispose();
75: }
76: }
77: finally
78: {
79: conn.Close();
80: }
81: return View(groups);
82: }
83: #endregion
84: #endif
85: public IActionResult Contact()
86: {
87: ViewData["Message"] = "Your contact page.";
88:
89: return View();
90: }
91:
92: public IActionResult Error()
93: {
94: return View();
95: }
96: }
97: }
Add a class variable for the database context immediately after the opening curly brace for the class, and get an instance of the context from ASP.NET Core DI:
[!code-csharpMain]
1: #define UseDbSet // or UseRawSQL
2:
3: using System.Collections.Generic;
4: using System.Linq;
5: using System.Threading.Tasks;
6: using Microsoft.AspNetCore.Mvc;
7: #region snippet_Usings2
8: using System.Data.Common;
9: #endregion
10: #region snippet_Usings1
11: using Microsoft.EntityFrameworkCore;
12: using ContosoUniversity.Data;
13: using ContosoUniversity.Models.SchoolViewModels;
14: #endregion
15:
16: namespace ContosoUniversity.Controllers
17: {
18: #region snippet_AddContext
19: public class HomeController : Controller
20: {
21: private readonly SchoolContext _context;
22:
23: public HomeController(SchoolContext context)
24: {
25: _context = context;
26: }
27: #endregion
28: public IActionResult Index()
29: {
30: return View();
31: }
32:
33: #if UseDbSet
34: #region snippet_UseDbSet
35: public async Task<ActionResult> About()
36: {
37: IQueryable<EnrollmentDateGroup> data =
38: from student in _context.Students
39: group student by student.EnrollmentDate into dateGroup
40: select new EnrollmentDateGroup()
41: {
42: EnrollmentDate = dateGroup.Key,
43: StudentCount = dateGroup.Count()
44: };
45: return View(await data.AsNoTracking().ToListAsync());
46: }
47: #endregion
48: #elif UseRawSQL
49: #region snippet_UseRawSQL
50: public async Task<ActionResult> About()
51: {
52: List<EnrollmentDateGroup> groups = new List<EnrollmentDateGroup>();
53: var conn = _context.Database.GetDbConnection();
54: try
55: {
56: await conn.OpenAsync();
57: using (var command = conn.CreateCommand())
58: {
59: string query = "SELECT EnrollmentDate, COUNT(*) AS StudentCount "
60: + "FROM Person "
61: + "WHERE Discriminator = 'Student' "
62: + "GROUP BY EnrollmentDate";
63: command.CommandText = query;
64: DbDataReader reader = await command.ExecuteReaderAsync();
65:
66: if (reader.HasRows)
67: {
68: while (await reader.ReadAsync())
69: {
70: var row = new EnrollmentDateGroup { EnrollmentDate = reader.GetDateTime(0), StudentCount = reader.GetInt32(1) };
71: groups.Add(row);
72: }
73: }
74: reader.Dispose();
75: }
76: }
77: finally
78: {
79: conn.Close();
80: }
81: return View(groups);
82: }
83: #endregion
84: #endif
85: public IActionResult Contact()
86: {
87: ViewData["Message"] = "Your contact page.";
88:
89: return View();
90: }
91:
92: public IActionResult Error()
93: {
94: return View();
95: }
96: }
97: }
Replace the About
method with the following code:
[!code-csharpMain]
1: #define UseDbSet // or UseRawSQL
2:
3: using System.Collections.Generic;
4: using System.Linq;
5: using System.Threading.Tasks;
6: using Microsoft.AspNetCore.Mvc;
7: #region snippet_Usings2
8: using System.Data.Common;
9: #endregion
10: #region snippet_Usings1
11: using Microsoft.EntityFrameworkCore;
12: using ContosoUniversity.Data;
13: using ContosoUniversity.Models.SchoolViewModels;
14: #endregion
15:
16: namespace ContosoUniversity.Controllers
17: {
18: #region snippet_AddContext
19: public class HomeController : Controller
20: {
21: private readonly SchoolContext _context;
22:
23: public HomeController(SchoolContext context)
24: {
25: _context = context;
26: }
27: #endregion
28: public IActionResult Index()
29: {
30: return View();
31: }
32:
33: #if UseDbSet
34: #region snippet_UseDbSet
35: public async Task<ActionResult> About()
36: {
37: IQueryable<EnrollmentDateGroup> data =
38: from student in _context.Students
39: group student by student.EnrollmentDate into dateGroup
40: select new EnrollmentDateGroup()
41: {
42: EnrollmentDate = dateGroup.Key,
43: StudentCount = dateGroup.Count()
44: };
45: return View(await data.AsNoTracking().ToListAsync());
46: }
47: #endregion
48: #elif UseRawSQL
49: #region snippet_UseRawSQL
50: public async Task<ActionResult> About()
51: {
52: List<EnrollmentDateGroup> groups = new List<EnrollmentDateGroup>();
53: var conn = _context.Database.GetDbConnection();
54: try
55: {
56: await conn.OpenAsync();
57: using (var command = conn.CreateCommand())
58: {
59: string query = "SELECT EnrollmentDate, COUNT(*) AS StudentCount "
60: + "FROM Person "
61: + "WHERE Discriminator = 'Student' "
62: + "GROUP BY EnrollmentDate";
63: command.CommandText = query;
64: DbDataReader reader = await command.ExecuteReaderAsync();
65:
66: if (reader.HasRows)
67: {
68: while (await reader.ReadAsync())
69: {
70: var row = new EnrollmentDateGroup { EnrollmentDate = reader.GetDateTime(0), StudentCount = reader.GetInt32(1) };
71: groups.Add(row);
72: }
73: }
74: reader.Dispose();
75: }
76: }
77: finally
78: {
79: conn.Close();
80: }
81: return View(groups);
82: }
83: #endregion
84: #endif
85: public IActionResult Contact()
86: {
87: ViewData["Message"] = "Your contact page.";
88:
89: return View();
90: }
91:
92: public IActionResult Error()
93: {
94: return View();
95: }
96: }
97: }
The LINQ statement groups the student entities by enrollment date, calculates the number of entities in each group, and stores the results in a collection of EnrollmentDateGroup
view model objects. > [!NOTE] > In the 1.0 version of Entity Framework Core, the entire result set is returned to the client, and grouping is done on the client. In some scenarios this could create performance problems. Be sure to test performance with production volumes of data, and if necessary use raw SQL to do the grouping on the server. For information about how to use raw SQL, see the last tutorial in this series.
Modify the About View
Replace the code in the Views/Home/About.cshtml file with the following code:
[!code-htmlMain]
1: @model IEnumerable<ContosoUniversity.Models.SchoolViewModels.EnrollmentDateGroup>
2:
3: @{
4: ViewData["Title"] = "Student Body Statistics";
5: }
6:
7: <h2>Student Body Statistics</h2>
8:
9: <table>
10: <tr>
11: <th>
12: Enrollment Date
13: </th>
14: <th>
15: Students
16: </th>
17: </tr>
18:
19: @foreach (var item in Model)
20: {
21: <tr>
22: <td>
23: @Html.DisplayFor(modelItem => item.EnrollmentDate)
24: </td>
25: <td>
26: @item.StudentCount
27: </td>
28: </tr>
29: }
30: </table>
Run the app and go to the About page. The count of students for each enrollment date is displayed in a table.
Summary
In this tutorial, you’ve seen how to perform sorting, filtering, paging, and grouping. In the next tutorial, you’ll learn how to handle data model changes by using migrations.
<SITEMAP> <MVC> <ASP> <NET> <DATA> <KIOSK> <FLEX> <SQL> <NOTES> <LINUX> <MONO> <FREEWARE> <DOCS> <ENG> <CHAT ME> <ABOUT ME> < THANKS ME> |