private async Task SetStudents() { Quarter quar = GetSelectedQuarter(); var queryParams = new Dictionary <string, string>(); if (quar == null) { //_logger.LogDebug("AddScores - SetStudents() - Have to get current quarter."); Utility util = new Utility(_config); quar = util.getCurrentQuarter(_context); } //set up initial query /** Use IQueryable so additional conditionals can be added before converting to a * collection (at which time the query goes to the db) * **/ IQueryable <Student> StudentsIQ = (from s in _context.Students where s.StuType == StudentType.Returning && s.YearQuarterEnrolled == quar.Id orderby s.LastName, s.FirstName //default ordering select s).Include(st => st.Level).Where(st => st.Level != null); // Based on input search filters, filter student data if (!String.IsNullOrEmpty(LnameSearch)) { StudentsIQ = StudentsIQ.Where(s => s.LastName.ToLower().StartsWith(LnameSearch.ToLower())); queryParams.Add("LnameSearch", LnameSearch); } if (!String.IsNullOrEmpty(SidSearch)) { StudentsIQ = StudentsIQ.Where(s => s.Sid.StartsWith(SidSearch)); queryParams.Add("SidSearch", SidSearch); } if (!String.IsNullOrEmpty(FnameSearch)) { StudentsIQ = StudentsIQ.Where(s => s.FirstName.ToLower().StartsWith(FnameSearch.ToLower())); queryParams.Add("FnameSearch", FnameSearch); } // Now sort data per any inputs FnameSort = "fname"; LnameSort = "lname_desc"; SidSort = "sid"; //used for determining icon direction SortDirSid = SortDirFname = SortDirLname = "down"; if (!String.IsNullOrEmpty(SortType)) { switch (SortType) { case "lname": StudentsIQ = StudentsIQ.OrderBy(s => s.LastName); LnameSort = "lname_desc"; break; case "lname_desc": StudentsIQ = StudentsIQ.OrderByDescending(s => s.LastName); LnameSort = "lname"; SortDirLname = "up"; break; case "fname": StudentsIQ = StudentsIQ.OrderBy(s => s.FirstName); FnameSort = "fname_desc"; break; case "fname_desc": StudentsIQ = StudentsIQ.OrderByDescending(s => s.FirstName); FnameSort = "fname"; SortDirFname = "up"; break; case "sid": StudentsIQ = StudentsIQ.OrderBy(s => s.Sid); SidSort = "sid_desc"; break; case "sid_desc": StudentsIQ = StudentsIQ.OrderByDescending(s => s.Sid); SidSort = "sid"; SortDirSid = "up"; break; } CurrentSortType = SortType; } else { FnameSort = "fname"; LnameSort = "lname_desc"; SidSort = "sid"; } QueryParams = queryParams; Students = await StudentsIQ.ToListAsync(); }
public void OnGet(string sortType) { //_context.Database.SetCommandTimeout(200); /** Use IQueryable so additional conditionals can be added before converting to a * collection (at which time the query goes to the db) * **/ IQueryable <Student> StudentsIQ = _context.Students.OrderByDescending(s => s.YearQuarterEnrolled).ThenBy(s => s.LastName).ThenBy(s => s.FirstName); /*** * get groups, countries, and quarters to fill filter drop downs * need to do before student data is filtered ***/ //save to separate IQueryable so conditionals can later be used against original before query is sent to database IQueryable <Student> StudentsIQForGrouping = StudentsIQ; var g = StudentsIQForGrouping.Select(s => s.Group).Distinct(); if (!String.IsNullOrEmpty(GroupSearch)) { SelectGroups = new SelectList(g, GroupSearch); } else { SelectGroups = new SelectList(g); } var c = StudentsIQForGrouping.Select(s => s.Country).Distinct(); if (!String.IsNullOrEmpty(CountrySearch)) { SelectCountries = new SelectList(c, CountrySearch); } else { SelectCountries = new SelectList(c); } var q = StudentsIQForGrouping.Select(s => s.YearQuarterEnrolled).Distinct(); if (!String.IsNullOrEmpty(QuarterSearch)) { SelectQuarters = new SelectList(q, QuarterSearch); } else { SelectQuarters = new SelectList(q); } /*** end generate drop down filters ***/ /*** * Based on input search filters, filter student data ***/ if (!String.IsNullOrEmpty(LnameSearch)) { StudentsIQ = StudentsIQ.Where(s => s.LastName.ToLower().StartsWith(LnameSearch.ToLower())); } if (!String.IsNullOrEmpty(SidSearch)) { StudentsIQ = StudentsIQ.Where(s => s.Sid.StartsWith(SidSearch)); } if (!String.IsNullOrEmpty(FnameSearch)) { StudentsIQ = StudentsIQ.Where(s => s.FirstName.ToLower().StartsWith(FnameSearch.ToLower())); } if (!String.IsNullOrEmpty(GroupSearch)) { StudentsIQ = StudentsIQ.Where(s => s.Group.Equals(GroupSearch)); } if (!String.IsNullOrEmpty(CountrySearch)) { StudentsIQ = StudentsIQ.Where(s => s.Country.Equals(CountrySearch)); } if (!String.IsNullOrEmpty(QuarterSearch)) { StudentsIQ = StudentsIQ.Where(s => s.YearQuarterEnrolled.Equals(QuarterSearch)); } /*** * Now sort data per any inputs ***/ FnameSort = "fname"; LnameSort = "lname_desc"; SidSort = "sid"; GroupSort = "group"; CountrySort = "country"; QuarterSort = "quarter"; SortDirSid = SortDirFname = SortDirLname = SortDirGroup = SortDirCountry = SortDirQuarter = "down"; if (!String.IsNullOrEmpty(sortType)) { switch (sortType) { case "lname_desc": StudentsIQ = StudentsIQ.OrderByDescending(s => s.LastName); LnameSort = "lname"; SortDirLname = "up"; break; case "lname": StudentsIQ = StudentsIQ.OrderBy(s => s.LastName); LnameSort = "lname_desc"; break; case "fname_desc": StudentsIQ = StudentsIQ.OrderByDescending(s => s.FirstName); FnameSort = "fname"; SortDirFname = "up"; break; case "fname": StudentsIQ = StudentsIQ.OrderBy(s => s.FirstName); FnameSort = "fname_desc"; break; case "sid": StudentsIQ = StudentsIQ.OrderBy(s => s.Sid); SidSort = "sid_desc"; break; case "sid_desc": StudentsIQ = StudentsIQ.OrderByDescending(s => s.Sid); SidSort = "sid"; SortDirSid = "up"; break; case "group": StudentsIQ = StudentsIQ.OrderBy(s => s.Group); GroupSort = "group_desc"; break; case "group_desc": StudentsIQ = StudentsIQ.OrderByDescending(s => s.Group); GroupSort = "group"; SortDirGroup = "up"; break; case "country": StudentsIQ = StudentsIQ.OrderBy(s => s.Country); CountrySort = "country_desc"; break; case "country_desc": StudentsIQ = StudentsIQ.OrderByDescending(s => s.Country); CountrySort = "country"; SortDirCountry = "up"; break; case "quarter": StudentsIQ = StudentsIQ.OrderBy(s => s.YearQuarterEnrolled); QuarterSort = "quarter_desc"; break; case "quarter_desc": StudentsIQ = StudentsIQ.OrderByDescending(s => s.YearQuarterEnrolled); QuarterSort = "quarter"; SortDirQuarter = "up"; break; } } else { FnameSort = "fname"; LnameSort = "lname_desc"; SidSort = "sid"; GroupSort = "group"; CountrySort = "country"; QuarterSort = "quarter"; } //Now set to list and save to variable StudentData = StudentsIQ.ToList(); }