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