public async Task <IActionResult> Index() { ViewBag.SemesterId = new SelectList(await _context.Semesters.OrderByDescending(s => s.StartedDate).ToListAsync(), "Id", "Name"); ViewBag.ProjectStatuses = new MultiSelectList(SeletectListHelper.GetEnumSelectList <ProjectStatus>(), "Value", "Text"); return(View()); }
public async Task <IActionResult> Index(string search, short?type, ProjectStatus?status, string orderBy, short?semester) { #region LINQ var projects = _context.Projects.AsQueryable(); if (User.IsInRole("Student")) { projects = projects .Include(p => p.ProjectMembers) .Where(p => p.ProjectMembers.Any(pm => pm.StudentId == GetUserId())); } else { projects = projects .Include(p => p.ProjectLecturers) .Where(p => p.ProjectLecturers.Any(pl => pl.LecturerId == GetUserId())); } #endregion //#region FromSqlRaw //var tableName = User.IsInRole("Student") ? "ProjectMembers" : "ProjectLecturers"; //var columnName = User.IsInRole("Student") ? "StudentId" : "LecturerId"; //var userId = new SqlParameter("UserId", GetUserId()); //var projects = _context.Projects.FromSqlRaw( // "Select [Projects].* From [Projects] " + // $"Inner Join (Select [ProjectId] From [{tableName}] Where [{columnName}] = @UserId) [UserProjects] " + // "On [Projects].Id = [UserProjects].ProjectId", userId); //#endregion if (!string.IsNullOrWhiteSpace(search)) { projects = projects.Where(p => p.Title.Contains(search)); } if (type.HasValue) { projects = projects.Where(p => p.ProjectTypeId == type); } if (status.HasValue) { projects = projects.Where(p => p.Status == status); } if (semester.HasValue) { projects = projects.Where(p => p.SemesterId == semester); } switch (orderBy) { case "title-asc": projects = projects.OrderBy(p => p.Title); break; case "title-desc": projects = projects.OrderByDescending(p => p.Title); break; case "date-asc": projects = projects.OrderBy(p => p.CreatedDate); break; case "date-desc": default: projects = projects.OrderByDescending(p => p.CreatedDate); break; } var orderByList = new List <SelectListItem> { new SelectListItem { Text = "Title Asc", Value = "title-asc" }, new SelectListItem { Text = "Title Desc", Value = "title-desc" }, new SelectListItem { Text = "Date Asc", Value = "date-asc" }, new SelectListItem { Text = "Date Desc", Value = "date-desc" }, }; ViewBag.Search = search; ViewBag.Status = status; ViewBag.Status = new SelectList(SeletectListHelper.GetEnumSelectList <ProjectStatus>(), "Value", "Text", status); ViewBag.Semester = new SelectList(await _context.Semesters.OrderByDescending(s => s.StartedDate).ToListAsync(), "Id", "Name", semester); ViewBag.OrderBy = new SelectList(orderByList, "Value", "Text", orderBy); ViewBag.TypeId = new SelectList(await _context.ProjectTypes.ToListAsync(), "Id", "Name", type); return(View(await projects .Include(p => p.ProjectType) .Include(p => p.Semester) .AsNoTracking() .ToListAsync())); }