// GET: Instructors - Scafolding
/*public async Task<IActionResult> Index()
* {
* return View(await _context.Instructors.ToListAsync());
* }*/
public async Task <IActionResult> Index(int?id, int?courseID)
{
var viewModel = new InstructorIndexData();
viewModel.Instructors = await _context.Instructors
.Include(i => i.OfficeAssignment)
.Include(i => i.CourseAssignments)
.ThenInclude(i => i.Course)
.ThenInclude(i => i.Enrollments)
.ThenInclude(i => i.Student)
.Include(i => i.CourseAssignments)
.ThenInclude(i => i.Course)
.ThenInclude(i => i.Department)
.AsNoTracking()
.OrderBy(i => i.LastName)
.ToListAsync();
if (id != null)
{
ViewData["InstructorID"] = id.Value;
Instructor instructor = viewModel.Instructors.Where(
i => i.ID == id.Value).Single(); //O método Where retorna uma coleção, mas nesse caso, os critérios passado para esse método resultam no retorno de apenas uma única entidade Instructor. O método Single converte a coleção em uma única entidade Instructor, que fornece acesso à propriedade CourseAssignments dessa entidade. A propriedade CourseAssignments contém entidades CourseAssignment, das quais você deseja apenas entidades Course relacionadas.
//Use o método Single em uma coleção quando souber que a coleção terá apenas um item. O método Single gera uma exceção se a coleção passada para ele está vazia ou se há mais de um item.Uma alternativa é SingleOrDefault, que retorna um valor padrão(nulo, nesse caso) se a coleção está vazia.No entanto, nesse caso, isso ainda resultará em uma exceção(da tentativa de encontrar uma propriedade Courses em uma referência nula), e a mensagem de exceção menos claramente indicará a causa do problema.Quando você chama o método Single, também pode passar a condição Where, em vez de chamar o método Where separadamente:
//.Single(i => i.ID == id.Value) OU
//.Where(I => i.ID == id.Value).Single()
viewModel.Courses = instructor.CourseAssignments.Select(s => s.Course);
}
if (courseID != null)
{
ViewData["CourseID"] = courseID.Value;
viewModel.Enrollments = viewModel.Courses.Where(
x => x.CourseID == courseID).Single().Enrollments;
}
return(View(viewModel));
}
// GET: Instructors
public async Task <IActionResult> Index(int?id, int?courseID)
{
var viewModel = new InstructorIndexData();
viewModel.Instructors = await _context.Instructors
.Include(i => i.OfficeAssignment)
.Include(i => i.Courses)
.ThenInclude(i => i.Course)
.ThenInclude(i => i.Enrollments)
.ThenInclude(i => i.Student)
.Include(i => i.Courses)
.ThenInclude(i => i.Course)
.ThenInclude(i => i.Department)
.AsNoTracking()
.OrderBy(i => i.LastName)
.ToListAsync();
if (id != null)
{
ViewData["InstructorID"] = id.Value;
Instructor instructor = viewModel.Instructors.Where(
i => i.ID == id.Value).Single();
viewModel.Courses = instructor.Courses.Select(s => s.Course);
}
if (courseID != null)
{
ViewData["CourseID"] = courseID.Value;
_context.Enrollments
.Include(i => i.Student)
.Where(c => c.CourseID == courseID.Value).Load();
viewModel.Enrollments = viewModel.Courses.Where(
x => x.CourseID == courseID).Single().Enrollments;
}
return(View(viewModel));
}
public async Task OnGetAsync(int?id, int?courseID)
{
Instructor = new InstructorIndexData();
Instructor.Instructors = await _context.Instructors
.Include(i => i.OfficeAssignment)
.Include(i => i.CourseAssignments)
.ThenInclude(i => i.Course)
.ThenInclude(i => i.Department)
//.Include(i=>i.CourseAssignments)
// .ThenInclude(i=>i.Course)
// .ThenInclude(i=>i.Enrollments)
// .ThenInclude(i=>i.Student)
//.AsNoTracking()
.OrderBy(i => i.LastName)
.ToListAsync();
if (id != null)
{
InstructorID = id.Value;
Instructor instructor = Instructor.Instructors.Single(
i => i.ID == id.Value);
Instructor.Courses = instructor.CourseAssignments.Select(s => s.Course);
}
if (courseID != null)
{
CourseID = courseID.Value;
var selectedCourse = Instructor.Courses.Where(x => x.CourseID == courseID).Single();
await _context.Entry(selectedCourse).Collection(x => x.Enrollments).LoadAsync();
foreach (Enrollment enrollment in selectedCourse.Enrollments)
{
await _context.Entry(enrollment).Reference(x => x.Student).LoadAsync();
}
Instructor.Enrollments = selectedCourse.Enrollments;
}
}
// GET: Instructor
public ActionResult Index(int?id, int?courseID)
{
var viewModel = new InstructorIndexData();
viewModel.Instructors = db.Instructors
.Include(i => i.Courses)
.Include(i => i.Courses.Select(c => c.Department))
.OrderBy(i => i.LastName);
if (id != null)
{
ViewBag.InstructorID = id.Value;
viewModel.Courses = viewModel.Instructors.Where(
i => i.ID == id.Value).Single().Courses;
}
if (courseID != null)
{
ViewBag.CourseID = courseID.Value;
// Lazy loading
//viewModel.Enrollments = viewModel.Courses.Where(
// x => x.CourseID == courseID).Single().Enrollments;
// Explicit loading
var selectedCourse = viewModel.Courses.Where(x => x.CourseID == courseID).Single();
db.Entry(selectedCourse).Collection(x => x.Enrollments).Load();
foreach (Enrollment enrollment in selectedCourse.Enrollments)
{
db.Entry(enrollment).Reference(x => x.Student).Load();
}
viewModel.Enrollments = selectedCourse.Enrollments;
}
return(View(viewModel));
}
// GET: Instructors
public async Task <IActionResult> Index(int?id, int?courseId)
{
var viewModel = new InstructorIndexData();
// The first string of .ThenInclude() gets CourseAssignment.Course, Course.Enrollments, and Enrollment.Student. An additional .ThenInclude() would be to load navigation properties of Student
// When .Include() is called again after the first string of .ThenInclude()'s, it goes back to loading Instructor properties, so the second chain of .ThenInclude() gets CourseAssignment.Course followed by Course.Department.
viewModel.Instructors = await _context.Instructors
.Include(i => i.OfficeAssignment)
.Include(i => i.CourseAssignments)
.ThenInclude(i => i.Course)
.ThenInclude(i => i.Enrollments)
.ThenInclude(i => i.Student)
.Include(i => i.CourseAssignments)
.ThenInclude(i => i.Course)
.ThenInclude(i => i.Department)
.AsNoTracking()
.OrderBy(i => i.LastName)
.ToListAsync();
// Executes when an instructor was selected and is loaded with the Course entities from the instructor's CourseAssignments navigation property
// The .Single() method converts the Instructors collection into a single Instructor entity
if (id != null)
{
ViewData["InstructorId"] = id.Value;
Instructor instructor = viewModel.Instructors.Where(i => i.Id == id.Value).Single();
viewModel.Courses = instructor.CourseAssignments.Select(s => s.Course);
}
// Executes when a course is selected. Gets the matching Enrollment out of the Enrollments collection in the viewModel based on its Id and converts it to a single entity
if (courseId != null)
{
ViewData["CourseId"] = courseId.Value;
viewModel.Enrollments = viewModel.Courses.Where(x => x.CourseId == courseId).Single().Enrollments;
}
return(View(viewModel));
}
// GET: Instructors
public async Task <IActionResult> Index(int?id, int?courseID)
{
#region Явная загрузка
var viewModel = new InstructorIndexData();
viewModel.Instructors = await _context.Instructors
.Include(i => i.OfficeAssignment)
.Include(i => i.CourseAssignments)
.ThenInclude(i => i.Course)
.ThenInclude(i => i.Department)
.OrderBy(i => i.LastName)
.ToListAsync();
if (id != null)
{
ViewData["InstructorID"] = id.Value;
Instructor instructor = viewModel.Instructors.Where(
i => i.ID == id.Value).Single();
viewModel.Courses = instructor.CourseAssignments.Select(s => s.Course);
}
if (courseID != null)
{
ViewData["CourseID"] = courseID.Value;
var selectedCourse = viewModel.Courses.Where(x => x.CourseID == courseID).Single();
await _context.Entry(selectedCourse).Collection(x => x.Enrollments).LoadAsync();
foreach (Enrollment enrollment in selectedCourse.Enrollments)
{
await _context.Entry(enrollment).Reference(x => x.Student).LoadAsync();
}
viewModel.Enrollments = selectedCourse.Enrollments;
}
return(View(viewModel));
#endregion
}
public async Task OnGetAsync(int?id, int?courseID)
{
InstructorData = new InstructorIndexData();
InstructorData.Instructors = await _context.Instructors
.Include(i => i.OfficeAssignment)
.Include(i => i.CourseAssignments)
.ThenInclude(i => i.Course)
.ThenInclude(i => i.Department)
.Include(i => i.CourseAssignments)
.ThenInclude(i => i.Course)
.ThenInclude(i => i.Enrollments)
.ThenInclude(i => i.Student)
.AsNoTracking()
.OrderBy(i => i.LastName)
.ToListAsync();
if (id != null)
{
InstructorID = id.Value;
Instructor instructor = InstructorData.Instructors
.Where(i => i.ID == InstructorID)
.Single();
InstructorData.Courses = instructor.CourseAssignments.Select(c => c.Course);
}
if (courseID != null)
{
CourseID = courseID.Value;
var selectedCourse = InstructorData.Courses
.Where(c => c.CourseID == CourseID)
.Single();
InstructorData.Enrollments = selectedCourse.Enrollments;
}
}
// GET: Instructors
public ActionResult Index(int?id, int?courseID)
{
var viewModel = new InstructorIndexData();
// implement eager loading on instructor
viewModel.Instructors =
db.Instructors.Include(i => i.OfficeAssignment)
.Include(i => i.Courses.Select(c => c.Department))
.OrderBy(i => i.LastName);
if (id != null)
{
ViewBag.InstructorID = id.Value;
viewModel.Courses = viewModel.Instructors.Where(i => i.ID == id.Value).Single().Courses;
}
if (courseID != null)
{
ViewBag.CourseID = courseID.Value;
//viewModel.Enrollments = viewModel.Courses.Where(x => x.CourseID == courseID).Single().Enrollments; // lazy loading..
//viewModel.Enrollments = db.Enrollments.Include(i => i.Student).Where(x => x.CourseID == courseID); // another way to load
// explicit loading
var selectedCourse = viewModel.Courses.Where(x => x.CourseID == courseID).Single();
db.Entry(selectedCourse).Collection(x => x.Enrollments).Load();
foreach (Enrollment e in selectedCourse.Enrollments)
{
db.Entry(e).Reference(x => x.Student).Load();
}
viewModel.Enrollments = selectedCourse.Enrollments;
}
var courses = db.Courses.Include(a => a.Department);
return(View(viewModel));
}
// GET: Instructor
public ActionResult Index(int?id, int?courseID)
{
// var instructors = db.Instructors.Include(i => i.OfficeAssignment);
// return View(instructors.ToList());
var viewModel = new InstructorIndexData();
viewModel.Instructors = db.Instructors
.Include(i => i.OfficeAssignment) //预加载office assignment
.Include(i => i.Courses.Select(c => c.Department)) //预加载Course,同时为Course预加载Department
.OrderBy(i => i.LastName);
if (id != null)
{
ViewBag.InstructorID = id.Value;
viewModel.Courses = viewModel.Instructors.Single(i => i.ID == id.Value).Courses;//Single方法将集合转换为一个讲师实体，使您能够访问该实体的Courses属性。
}
if (courseID != null)
{
ViewBag.CourseID = courseID.Value;
viewModel.Enrollments = viewModel.Courses.Single(x => x.CourseID == courseID).Enrollments;
}
return(View(viewModel));
}
// GET: Instructor
public async Task <IActionResult> Index(int?id, int?courseID)
{
// Eager loading, give me all the info at once
var viewModel = new InstructorIndexData();
viewModel.Instructors = await _context.Instructors
.Include(i => i.OfficeAssignment)
.Include(i => i.CourseAssignments)
// ThenInclude infers that it is off of course assignments
.ThenInclude(i => i.Course)
.ThenInclude(i => i.Enrollments)
.ThenInclude(i => i.Student)
// Need to start back from course assignment
.Include(i => i.CourseAssignments)
.ThenInclude(i => i.Course)
.ThenInclude(i => i.Department)
// Read only representation, don't set up tracking for editing.
.AsNoTracking()
.OrderBy(i => i.LastName)
.ToListAsync();
if (id != null)
{
ViewData["InstructorID"] = id.Value;
Instructor instructor = viewModel.Instructors.Where(
i => i.ID == id.Value).Single();
viewModel.Courses = instructor.CourseAssignments.Select(s => s.Course);
}
if (courseID != null)
{
ViewData["CourseID"] = courseID.Value;
viewModel.Enrollments = viewModel.Courses.Where(
x => x.CourseID == courseID).Single().Enrollments;
}
return(View(viewModel));
}
// GET: Instructor
public ActionResult Index(int?id, int?courseId)
{
var viewModel = new InstructorIndexData
{
Instructors = db.Instructors
.Include(i => i.OfficeAssignment)
.Include(i => i.Courses.Select(c => c.Department))
.OrderBy(i => i.LastName)
};
if (id != null)
{
ViewBag.InstructorID = id.Value;
viewModel.Courses = viewModel.Instructors.Single(_ => _.InstructorId == id.Value).Courses;
}
if (courseId != null)
{
ViewBag.CourseID = courseId.Value;
viewModel.Enrollments = viewModel.Courses.Single(_ => _.CourseId == courseId).Enrollments;
}
return(View(viewModel));
}
// GET: Instructors
public async Task <IActionResult> Index(int?id, int?courseId)
{
InstructorIndexData viewModel = new InstructorIndexData();
viewModel.Instructors = await _context.Instructors
.Include(i => i.OfficeAssignment)
.Include(i => i.CourseAssignments)
.ThenInclude(c => c.Course)
.ThenInclude(c => c.Enrollments)
.ThenInclude(e => e.Student)
.Include(i => i.CourseAssignments)
.ThenInclude(c => c.Course)
.ThenInclude(c => c.Department)
.OrderBy(i => i.LastName)
.AsNoTracking()
.ToListAsync();
if (id != null)
{
ViewData["InstructorId"] = id.Value;
Instructor instructor = viewModel.Instructors
.Single(i => i.InstructorId == id.Value);
viewModel.Courses = instructor.CourseAssignments
.Select(ca => ca.Course);
}
if (courseId != null)
{
ViewData["CourseId"] = courseId.Value;
viewModel.Enrollments = viewModel.Courses
.Single(c => c.CourseId == courseId.Value)
.Enrollments;
}
return(View(viewModel));
}
// GET: Instructors
public ActionResult Index(int?id, int?courseID)
{
var viewModel = new InstructorIndexData();
viewModel.Instructors = db.Instructors
.Include(i => i.OfficeAssignment)
.Include(i => i.Courses.Select(c => c.Department))
.OrderBy(i => i.LastName);
if (id != null)
{
ViewBag.InstructorID = id.Value;
viewModel.Courses = viewModel.Instructors.Where(
i => i.ID == id.Value).Single().Courses;
}
if (courseID != null)
{
ViewBag.CourseID = courseID;
viewModel.Enrollments = viewModel.Courses.Where(
x => x.CourseID == courseID).Single().Enrollments;
}
//var instructors = db.Instructors.Include(i => i.OfficeAssignment);
return(View(viewModel));
}
//// GET: Instructors
//public async Task<IActionResult> Index()
//{
// return View(await _context.Instructors.ToListAsync());
//}
//public async Task<IActionResult> Index(int? id, int? courseID)
//{
//viene caricato tutto in un colpo solo anche le tabelle di dettaglio in fondo alla pagina
// var viewModel = new InstructorIndexData();
// viewModel.Instructors = await _context.Instructors
// .Include(i => i.OfficeAssignment)
// .Include(i => i.CourseAssignments)
// //all'imnterno della proprietà courseassignement viene cariacata course
// .ThenInclude(i => i.Course)
// .ThenInclude(i => i.Enrollments)
// .ThenInclude(i => i.Student)
// .Include(i => i.CourseAssignments)
// .ThenInclude(i => i.Course)
// .ThenInclude(i => i.Department)
// .AsNoTracking()
// .OrderBy(i => i.LastName)
// .ToListAsync();
// if (id != null)
// {
// ViewData["InstructorID"] = id.Value;
// Instructor instructor = viewModel.Instructors.Where(
// i => i.ID == id.Value).Single();
// viewModel.Courses = instructor.CourseAssignments.Select(s => s.Course);
// }
// if (courseID != null)
// {
// ViewData["CourseID"] = courseID.Value;
// viewModel.Enrollments = viewModel.Courses.Where(
// x => x.CourseID == courseID).Single().Enrollments;
// }
// return View(viewModel);
//}
public async Task <IActionResult> Index(int?id, int?courseID)
{
//in caso non si visualizzi di frequente il dettaglio è meglio caricare corsi ed allievi successivamente
var viewModel = new InstructorIndexData();
viewModel.Instructors = await _context.Instructors
.Include(i => i.OfficeAssignment)
.Include(i => i.CourseAssignments)
.ThenInclude(i => i.Course)
.ThenInclude(i => i.Department)
.OrderBy(i => i.LastName)
.ToListAsync();
if (id != null)
{
ViewData["InstructorID"] = id.Value;
Instructor instructor = viewModel.Instructors.Where(
i => i.ID == id.Value).Single();
viewModel.Courses = instructor.CourseAssignments.Select(s => s.Course);
}
if (courseID != null)
{
ViewData["CourseID"] = courseID.Value;
var selectedCourse = viewModel.Courses.Where(x => x.CourseID == courseID).Single();
await _context.Entry(selectedCourse).Collection(x => x.Enrollments).LoadAsync();
foreach (Enrollment enrollment in selectedCourse.Enrollments)
{
await _context.Entry(enrollment).Reference(x => x.Student).LoadAsync();
}
viewModel.Enrollments = selectedCourse.Enrollments;
}
return(View(viewModel));
}
private Report GetReport(string sortOrder, string currentFilter, string searchString, bool ishtml = false)
{
ViewBag.menu = MENU;
ViewBag.CurrentSort = sortOrder;
ViewBag.NameSortParm = string.IsNullOrEmpty(sortOrder) ? "Name_desc" : "";
ViewBag.FirstNameSortParm = sortOrder == "FirstName" ? "FirstName_desc" : "FirstName";
ViewBag.DateSortParm = sortOrder == "Date" ? "Date_desc" : "Date";
ViewBag.LocationSortParm = sortOrder == "Loc" ? "Loc_desc" : "Loc";
InstructorIndexData viewModel = new InstructorIndexData();
if (searchString == null)
{
searchString = currentFilter;
}
string keyword = string.IsNullOrEmpty(searchString) ? null : searchString.ToUpper();
ViewBag.CurrentFilter = searchString;
var instructors = repository.GetInstructors()
.Include(i => i.OfficeAssignment)
.Include(i => i.Courses.Select(x => x.Department));
if (!string.IsNullOrEmpty(keyword))
{
instructors = instructors.Where(x => x.LastName.ToUpper().Contains(keyword) ||
x.FirstMidName.ToUpper().Contains(keyword));
}
switch (sortOrder)
{
case "Name_desc":
instructors = instructors.OrderByDescending(x => x.LastName);
break;
case "FirstName":
instructors = instructors.OrderBy(x => x.FirstMidName);
break;
case "FirstName_desc":
instructors = instructors.OrderByDescending(x => x.FirstMidName);
break;
case "Date":
instructors = instructors.OrderBy(x => x.HireDate);
break;
case "Date_desc":
instructors = instructors.OrderByDescending(x => x.HireDate);
break;
case "Loc":
instructors = instructors.OrderBy(x => x.OfficeAssignment.Location);
break;
case "Loc_desc":
instructors = instructors.OrderByDescending(x => x.OfficeAssignment.Location);
break;
default:
instructors = instructors.OrderBy(x => x.LastName);
break;
}
var lr = instructors.ToList().Select(x => new
{
LastName = x.LastName,
FirstName = x.FirstMidName,
HireDate = x.HireDate,
Office = x.OfficeAssignment,
Courses = x.GetCourses(ishtml)
});
Report report = new Report(lr.ToReportSource());
report.TextFields.Title = "Instructors Report";
report.TextFields.Header = string.Format(@"Report Generated: {0} Total Instructors: {1}", DateTime.Now, instructors.Count());
report.DataFields["HireDate"].DataFormatString = "{0:yyyy-MM-dd}";
report.RenderHints.FreezeRows = 4;
return(report);
}
// GET: Instructor
public ActionResult Index(int? id, int? courseID)
{
var viewModel = new InstructorIndexData();
viewModel.Instructors = db.Instructors
.Include(i => i.OfficeAssignment)
.Include(i => i.Courses.Select(c => c.Department))
.OrderBy(i => i.LastName);
if(id != null)
{
ViewBag.InstructorID = id.Value;
viewModel.Courses = viewModel.Instructors.Where(
i => i.ID == id.Value).Single().Courses;
}
if(courseID != null)
{
ViewBag.CourseID = courseID.Value;
// Lazy loading
//viewModel.Enrollments = viewModel.Courses.Where(
// x => x.CourseID == courseID).Single().Enrollments;
// Explicit loading
var selectedCourse = viewModel.Courses.Where(
x => x.CourseID == courseID).Single();
db.Entry(selectedCourse).Collection(x => x.Enrollments).Load();
foreach(Enrollment enrollment in selectedCourse.Enrollments)
{
db.Entry(enrollment).Reference(x => x.Student).Load();
}
viewModel.Enrollments = selectedCourse.Enrollments;
}
return View(viewModel);
}
//
// GET: /Instructor/
public ViewResult Index(Int32?id, Int32?courseID)
{
#region Eager Loading
//var viewModel = new InstructorIndexData();
//viewModel.Instructors = db.Instructors
// .Include(i => i.Courses.Select(c => c.Department))
// .OrderBy(i => i.LastName);
//if (null != id)
//{
// //Fetch the matching courses for the selected instructor.
// ViewBag.PersonID = id;
// viewModel.Courses = viewModel.Instructors.Where(i => i.PersonID == id.Value)
// .Single().Courses;
//}
//if (null != courseID)
//{
// //Fetchthe matching enrollments for the selected course.
// ViewBag.CourseID = courseID.Value;
// viewModel.Enrollments = viewModel.Courses.Where(c => c.CourseID == courseID)
// .Single().Enrollments;
//}
//return View(viewModel);
#endregion Eager Loading
#region Explicit Loading
var viewModel = new InstructorIndexData();
viewModel.Instructors = this._db.Instructors
.Include(i => i.Courses.Select(c => c.Department))
.OrderBy(i => i.LastName);
if (id != null)
{
ViewBag.PersonID = id.Value;
viewModel.Courses = viewModel.Instructors
.Where(i => i.PersonID == id.Value)
.Single()
.Courses;
}
if (null != courseID)
{
ViewBag.CourseID = courseID.Value;
var selectedCourse = viewModel.Courses
.Where(c => c.CourseID == courseID.Value)
.Single();
//Use the Collection method to explicitly load a collection of entities.
this._db.Entry(selectedCourse).Collection(x => x.Enrollments).Load();
foreach (var enrollment in selectedCourse.Enrollments)
{
//Use the Reference method to explicitly load an entity.
this._db.Entry(enrollment).Reference(x => x.Student).Load();
}
viewModel.Enrollments = selectedCourse.Enrollments;
}
return(View(viewModel));
#endregion Explicit Loading
}
// GET: Instructors
public async Task <IActionResult> Index(int?id, int?courseID)
{
var viewModel = new InstructorIndexData();
// eager loading for all the related data
/*
* viewModel.Instructors = await _context.Instructors // load Instructors
* .Include(instructor => instructor.OfficeAssignment) // Include their Office Assignments
* .Include(instructor => instructor.CourseAssignments) // Include their Course Assignments
* .ThenInclude(courseAssignment => courseAssignment.Course) // From Course Assignments include related Courses
* .ThenInclude(course => course.Enrollments) // from those related Courses include Enrollments
* .ThenInclude(course => course.Student) // from Enrollments include Students
* .Include(instructor => instructor.CourseAssignments) // Include Course Assignments (again, because from Course we need 2 properties)
* .ThenInclude(courseAssignment => courseAssignment.Course) // Include Courses (again)...
* .ThenInclude(course => course.Department) // ... To include course departments
* .AsNoTracking()
* .OrderBy(instructor => instructor.LastName)
* .ToListAsync();
*
* if (id != null)
* {
* ViewData["InstructorID"] = id.Value;
* Instructor instructor = viewModel.Instructors.Where(
* i => i.ID == id.Value).Single();
* viewModel.Courses = instructor.CourseAssignments.Select(courseAssignment => courseAssignment.Course);
* }
*
* if (courseID != null)
* {
* ViewData["CourseID"] = courseID.Value;
* viewModel.Enrollments = viewModel.Courses.Where(
* x => x.CourseID == courseID).Single().Enrollments;
* }
*
* return View(viewModel);
*/
// Explicit loading (load only when needed)
viewModel.Instructors = await _context.Instructors
.Include(i => i.OfficeAssignment)
.Include(i => i.CourseAssignments)
.ThenInclude(i => i.Course)
.ThenInclude(i => i.Department)
.OrderBy(i => i.LastName)
.ToListAsync();
// we didn't load the Enrollments and Students
if (id != null)
{
ViewData["InstructorID"] = id.Value;
Instructor instructor = viewModel.Instructors.Where(
i => i.ID == id.Value).Single();
viewModel.Courses = instructor.CourseAssignments.Select(s => s.Course);
}
if (courseID != null)
{
ViewData["CourseID"] = courseID.Value;
var selectedCourse = viewModel.Courses.Where(x => x.CourseID == courseID).Single();
await _context.Entry(selectedCourse).Collection(x => x.Enrollments).LoadAsync();
// explicitly load Enrollments as requested
foreach (Enrollment enrollment in selectedCourse.Enrollments)
{
await _context.Entry(enrollment).Reference(x => x.Student).LoadAsync();
}
viewModel.Enrollments = selectedCourse.Enrollments;
}
return(View(viewModel));
}
// GET: Instructors
public async Task <IActionResult> Index(int?id, int?courseID)
{
var viewModel = new InstructorIndexData();
viewModel.Instructors = await _context.Instructors
.Include(i => i.OfficeAssignment)
.Include(i => i.CourseAssignments)
.ThenInclude(i => i.Course)
.ThenInclude(i => i.Enrollments)
.ThenInclude(i => i.Student)
.Include(i => i.CourseAssignments)
.ThenInclude(i => i.Course)
.ThenInclude(i => i.Department)
.AsNoTracking()
.OrderBy(i => i.LastName)
.ToListAsync();
if (id != null)
{
ViewData["InstructorID"] = id.Value;
Instructor instructor = viewModel.Instructors.Where(
i => i.ID == id.Value).Single();
viewModel.Courses = instructor.CourseAssignments.Select(s => s.Course);
}
if (courseID != null)
{
ViewData["CourseID"] = courseID.Value;
viewModel.Enrollments = viewModel.Courses.Where(
x => x.CourseID == courseID).Single().Enrollments;
}
return(View(viewModel));
//var viewModel = new InstructorIndexData();
//viewModel.Instructors = await _context.Instructors
// .Include(i => i.OfficeAssignment)
// .Include(i => i.CourseAssignments)
// .ThenInclude(i => i.Course)
// .ThenInclude(i => i.Department)
// .OrderBy(i => i.LastName)
// .ToListAsync();
//if (id != null)
//{
// ViewData["InstructorID"] = id.Value;
// Instructor instructor = viewModel.Instructors.Where(
// i => i.ID == id.Value).Single();
// viewModel.Courses = instructor.CourseAssignments.Select(s => s.Course);
//}
//if (courseID != null)
//{
// ViewData["CourseID"] = courseID.Value;
// var selectedCourse = viewModel.Courses.Where(x => x.CourseID == courseID).Single();
// await _context.Entry(selectedCourse).Collection(x => x.Enrollments).LoadAsync();
// foreach (Enrollment enrollment in selectedCourse.Enrollments)
// {
// await _context.Entry(enrollment).Reference(x => x.Student).LoadAsync();
// }
// viewModel.Enrollments = selectedCourse.Enrollments;
//}
//return View(viewModel);
}
public async Task OnGetAsync(int?id, int?courseID)
{
InstructorData = new InstructorIndexData();
Services.Instructor.InstructorClient instructorClient = new OpenHack.University.Services.Instructor.InstructorClient();
instructorClient.Endpoint.Address = new System.ServiceModel.EndpointAddress(webserviceUrl);
var instructorListContract = await instructorClient.SearchAsync();
var instructors = new List <Instructor>();
foreach (var instructorContract in instructorListContract)
{
var instructor = new Instructor()
{
FirstMidName = instructorContract.FirstMidName,
HireDate = instructorContract.HireDate,
ID = instructorContract.ID,
LastName = instructorContract.LastName,
CourseAssignments = new List <CourseAssignment>(),
OfficeAssignment = instructorContract.OfficeAssignment != null ? new OfficeAssignment()
{
Location = instructorContract.OfficeAssignment.Location
} : null
};
foreach (var courseAssignmentContract in instructorContract.CourseAssignments)
{
var courseAssignment = new CourseAssignment()
{
CourseID = courseAssignmentContract.CourseID,
InstructorID = courseAssignmentContract.InstructorID
};
if (courseAssignmentContract.Course != null)
{
courseAssignment.Course = new Course()
{
CourseID = courseAssignmentContract.Course.CourseID,
Title = courseAssignmentContract.Course.Title
};
}
instructor.CourseAssignments.Add(courseAssignment);
}
instructors.Add(instructor);
}
InstructorData.Instructors = instructors;
if (id != null)
{
InstructorID = id.Value;
Instructor instructor = InstructorData.Instructors
.Where(i => i.ID == id.Value).Single();
InstructorData.Courses = instructor.CourseAssignments.Select(s => s.Course);
}
if (courseID != null)
{
CourseID = courseID.Value;
//var selectedCourse = InstructorData.Courses
// .Where(x => x.CourseID == courseID).Single();
//await _context.Entry(selectedCourse).Collection(x => x.Enrollments).LoadAsync();
//foreach (Enrollment enrollment in selectedCourse.Enrollments)
//{
// await _context.Entry(enrollment).Reference(x => x.Student).LoadAsync();
//}
//InstructorData.Enrollments = selectedCourse.Enrollments;
}
}
public async Task <IActionResult> Index(string sortOrder, int?id, int?courseID)
{
ViewData["NameSortParm"] = String.IsNullOrEmpty(sortOrder) ? "name_desc" : "";
ViewData["DateSortParm"] = sortOrder == "Date" ? "date_desc" : "Date";
var viewModel = new InstructorIndexData
{
Instructors = await _context.Instructors
.Include(i => i.CourseAssignments)
.ThenInclude(i => i.Course)
.ThenInclude(i => i.Department)
.OrderBy(i => i.LastName)
.ToListAsync()
};
switch (sortOrder)
{
case "name_desc":
viewModel.Instructors = await _context.Instructors
.OrderByDescending(i => i.LastName)
.ToListAsync();
break;
case "Date":
viewModel.Instructors = await _context.Instructors
.OrderBy(i => i.HiredDate)
.ToListAsync();
break;
case "date_desc":
viewModel.Instructors = await _context.Instructors
.OrderByDescending(i => i.HiredDate)
.ToListAsync();
break;
default:
viewModel.Instructors = await _context.Instructors
.OrderBy(i => i.LastName)
.ToListAsync();
break;
}
//Shows all the courses the instructor is teaching
if (id != null)
{
ViewData["InstructorID"] = id.Value;
Instructor instructor = viewModel.Instructors.Where(
i => i.ID == id.Value).Single();
viewModel.Courses = instructor.CourseAssignments.Select(s => s.Course);
}
//Shows all students and their grade in the course after selected the specified course
if (courseID != null)
{
ViewData["CourseID"] = courseID.Value;
var selectedCourse = viewModel.Courses.Where(x => x.CourseID == courseID).Single();
await _context.Entry(selectedCourse).Collection(x => x.Enrollments).LoadAsync();
foreach (Enrollment enrollment in selectedCourse.Enrollments)
{
await _context.Entry(enrollment).Reference(x => x.Student).LoadAsync();
}
viewModel.Enrollments = selectedCourse.Enrollments;
}
return(View(viewModel));
}
public ActionResult Index(string sortOrder, string searchString, int?page)
{
string keyword = string.IsNullOrEmpty(searchString) ? null : searchString.ToUpper();
InstructorIndexData viewModel = new InstructorIndexData();
if (searchString != null)
{
page = 1;
}
var instructors = repository.GetInstructors()
.Include(i => i.OfficeAssignment)
.Include(i => i.Courses.Select(x => x.Department));
if (!string.IsNullOrEmpty(keyword))
{
instructors = instructors.Where(x => x.LastName.ToUpper().Contains(keyword) ||
x.FirstMidName.ToUpper().Contains(keyword));
}
switch (sortOrder)
{
case "Name_desc":
instructors = instructors.OrderByDescending(x => x.LastName);
break;
case "FirstName":
instructors = instructors.OrderBy(x => x.FirstMidName);
break;
case "FirstName_desc":
instructors = instructors.OrderByDescending(x => x.FirstMidName);
break;
case "Date":
instructors = instructors.OrderBy(x => x.HireDate);
break;
case "Date_desc":
instructors = instructors.OrderByDescending(x => x.HireDate);
break;
case "Loc":
instructors = instructors.OrderBy(x => x.OfficeAssignment.Location);
break;
case "Loc_desc":
instructors = instructors.OrderByDescending(x => x.OfficeAssignment.Location);
break;
default:
instructors = instructors.OrderBy(x => x.LastName);
break;
}
int pageSize = Constants.PAGE_SIZE;
int pageNumber = (page ?? 1);
var l = instructors.ToPagedList(pageNumber, pageSize);
var lx = l.Select(x => new
{
PersonID = x.PersonID,
LastName = x.LastName,
FirstMidName = x.FirstMidName,
HireDate = x.HireDate,
OfficeAssignment = new { Location = x.OfficeAssignment == null ? null : x.OfficeAssignment.Location },
Courses = x.Courses == null ? null : x.Courses.Select(c => new { CourseID = c.CourseID, Title = c.Title })
});
Pager pager = new Pager(l.TotalItemCount, l.PageNumber, l.PageSize);
Dictionary <string, object> res = new Dictionary <string, object>
{
{ "pager", pager },
{ "model", lx }
};
return(Json(res, JsonRequestBehavior.AllowGet));
}
public async Task OnGetAsync(int?id, int?CourseID)
{
//Instructor = await _context.Instructors.ToListAsync();
InstructorData = new InstructorIndexData();
InstructorData.Instructors = await _context.Instructors
.Include(i => i.OfficeAssignment)
.Include(i => i.CourseAssignments)
.ThenInclude(i => i.Course)
.ThenInclude(i => i.Department)
/*
* This was added for eager loading, however, if the user barely need to look at enrolment, we could delegate the query until user selects it
*/
//.Include(i => i.CourseAssignments)
// .ThenInclude(i => i.Course)
// .ThenInclude(i => i.Enrollments)
// .ThenInclude(i => i.Student)
//.AsNoTracking()
.OrderBy(i => i.LastName)
.ToListAsync();
/*
* The filters will return a collection of a single item
* .Single() converts that to a single Instructor/Course entity
* - will complain if the collection is empty though
* .SingleOrDefault() will return null if the collection is empty
*/
if (id != null)
{
InstructorID = id.Value;
/*
* .Where(cond).Single() or .Single(cond) are equivalent
*/
//Instructor instructor = InstructorData.Instructors.Where(i => i.ID == id.Value).Single();
Instructor instructor = InstructorData.Instructors.Single(i => i.ID == id.Value);
InstructorData.Courses = instructor.CourseAssignments.Select(s => s.Course);
}
if (CourseID != null)
{
this.CourseID = CourseID.Value;
var selectedCourse = InstructorData.Courses
.Where(s => s.CourseID == CourseID.Value)
.Single();
/*
* Added this to only load enrolment details when clicked
*/
await _context.Entry(selectedCourse).Collection(x => x.Enrollments).LoadAsync();
foreach (Enrollment en in selectedCourse.Enrollments)
{
await _context.Entry(en).Reference(x => x.Student).LoadAsync();
}
InstructorData.Enrollments = selectedCourse.Enrollments;
}
}
//OnGetAsync method accepts optional route data for the ID of the selected instructor.
public async Task OnGetAsync(int?id, int?courseID, int?labID, int?removeCourseID, int?removeEnrollmentStudent, int?removeEnrollmentCourse)
{
Instructor = new InstructorIndexData();
//Create a DbSet of CourseAssignments
DbSet <CourseAssignment> CourseAssignments = _context.CourseAssignment;
//Use authentication to select the currently signed in Instructor
var claimsIdentity = User.Identity as System.Security.Claims.ClaimsIdentity;
var c = claimsIdentity.FindFirst(System.Security.Claims.ClaimTypes.NameIdentifier);
//Show the CourseAssignments (Courses) and LabAssignments (Labs) for the Instructor signed in
Instructor.Instructors = await _context.Instructor.Where(x => x.CourseAssignments.Any(x2 => x2.InstructorID == int.Parse(c.Value) && x2.InstructorID == x.InstructorID))
.Include(i => i.CourseAssignments) //which brings in the courses taught
.ThenInclude(i => i.Course)
.Include(l => l.LabAssignments)
.ThenInclude(l => l.Lab)
.OrderBy(i => i.LastName)
.ToListAsync();
//When an InstructorID is present, display the information for the CourseAssignments and LabAssignments.
if (id != null)
{
InstructorID = id.Value;
Instructor instructor = Instructor.Instructors.Where(
i => i.InstructorID == id.Value).Single();
Instructor.Courses = instructor.CourseAssignments.Select(s => s.Course).OrderBy(s => s.Title);
Instructor.Labs = instructor.LabAssignments.Select(l => l.Lab);
}
//The following code populates the view's model's CourseEnrollments property when a course is selected.
if (courseID != null)
{
courseID = courseID.Value;
var selectedCourse = Instructor.Courses.Where(x => x.CourseID == courseID).Single();
await _context.Entry(selectedCourse).Collection(x => x.CourseEnrollments).LoadAsync();
foreach (CourseEnrollment enrollment in selectedCourse.CourseEnrollments)
{
await _context.Entry(enrollment).Reference(x => x.Student).LoadAsync();
}
Instructor.CourseEnrollments = selectedCourse.CourseEnrollments;
}
//The following code populates the view's model's LabEnrollments property when a lab is selected.
if (labID != null)
{
labID = labID.Value;
var selectedLab = Instructor.Labs.Where(x => x.LabID == labID).Single();
await _context.Entry(selectedLab).Collection(x => x.LabEnrollments).LoadAsync();
foreach (LabEnrollment enrollment in selectedLab.LabEnrollments)
{
await _context.Entry(enrollment).Reference(x => x.Student).LoadAsync();
}
Instructor.LabEnrollments = selectedLab.LabEnrollments;
}
//When the user selects to remove a courseAssignment, remove the assignment from the database.
if (removeCourseID != null)
{
CourseAssignment courseAssignment = await _context.CourseAssignment.Where(x => x.InstructorID == InstructorID && x.CourseID == removeCourseID).FirstOrDefaultAsync();
_context.CourseAssignment.Remove(courseAssignment);
await _context.SaveChangesAsync();
}
//When the user selects to remove a courseEnrollment, remove the enrollment from the database.
if (removeEnrollmentStudent != null && removeEnrollmentCourse != null)
{
CourseEnrollment courseEnrollment = await _context.CourseEnrollment.Where(x => x.StudentID == removeEnrollmentStudent && x.CourseID == removeEnrollmentCourse).FirstOrDefaultAsync();
_context.CourseEnrollment.Remove(courseEnrollment);
await _context.SaveChangesAsync();
Response.Redirect("/Instructors/Details/" + InstructorID + "?courseID=" + removeEnrollmentCourse.ToString());
}
}
// GET: Instructors
public ActionResult Index(int?id, int?courseID, string sortOrder, string currentFilter, string searchString)
{
ViewBag.CurrentSort = sortOrder;
ViewBag.NameSortParm = String.IsNullOrEmpty(sortOrder) ? "name_desc" : "";
ViewBag.FirstNameSortParm = sortOrder == "firstName" ? "firstName_desc" : "firstName";
ViewBag.DateSortParm = sortOrder == "date" ? "date_desc" : "date";
if (searchString == null)
{
searchString = currentFilter;
}
ViewBag.CurrentFilter = searchString;
var viewModel = new InstructorIndexData();
viewModel.Instructors = db.Instructors
.OrderBy(i => i.LastName);
if (!String.IsNullOrEmpty(searchString))
{
viewModel.Instructors = viewModel.Instructors.Where(i => i.LastName.Contains(searchString, StringComparison.OrdinalIgnoreCase) || i.FirstMidName.Contains(searchString, StringComparison.OrdinalIgnoreCase));
}
switch (sortOrder)
{
case "name_desc":
viewModel.Instructors = viewModel.Instructors.OrderByDescending(i => i.LastName);
break;
case "date":
viewModel.Instructors = viewModel.Instructors.OrderBy(i => i.HireDate);
break;
case "date_desc":
viewModel.Instructors = viewModel.Instructors.OrderByDescending(i => i.HireDate);
break;
case "firstName":
viewModel.Instructors = viewModel.Instructors.OrderBy(i => i.FirstMidName);
break;
case "firstName_desc":
viewModel.Instructors = viewModel.Instructors.OrderByDescending(i => i.FirstMidName);
break;
default:
viewModel.Instructors = viewModel.Instructors.OrderBy(i => i.LastName);
break;
}
if (id != null)
{
ViewBag.InstructorID = id.Value;
viewModel.Courses = viewModel.Instructors.Where(
i => i.ID == id.Value).Single().Courses;
}
if (courseID != null)
{
ViewBag.CourseID = courseID.Value;
viewModel.Enrollments = viewModel.Courses.Where(
x => x.CourseID == courseID).Single().Enrollments;
ViewBag.GradeAverage = Math.Round(decimal.Parse(viewModel.Enrollments.Select(g => g.Grade).Average().ToString()), 2);
ViewBag.TotalStudents = viewModel.Enrollments.Count();
}
return(View(viewModel));
}
//
// GET: /Instructor/
public ActionResult Index(string sortOrder, string currentFilter, string searchString, int?page, int?id, int?courseID)
{
ViewBag.menu = MENU;
ViewBag.CurrentSort = sortOrder;
ViewBag.NameSortParm = string.IsNullOrEmpty(sortOrder) ? "Name_desc" : "";
ViewBag.FirstNameSortParm = sortOrder == "FirstName" ? "FirstName_desc" : "FirstName";
ViewBag.DateSortParm = sortOrder == "Date" ? "Date_desc" : "Date";
ViewBag.LocationSortParm = sortOrder == "Loc" ? "Loc_desc" : "Loc";
InstructorIndexData viewModel = new InstructorIndexData();
if (searchString != null)
{
page = 1;
}
else
{
searchString = currentFilter;
}
string keyword = string.IsNullOrEmpty(searchString) ? null : searchString.ToUpper();
ViewBag.CurrentFilter = searchString;
var instructors = repository.GetInstructors()
.Include(i => i.OfficeAssignment)
.Include(i => i.Courses.Select(x => x.Department));
if (!string.IsNullOrEmpty(keyword))
{
instructors = instructors.Where(x => x.LastName.ToUpper().Contains(keyword) ||
x.FirstMidName.ToUpper().Contains(keyword));
}
if (id != null)
{
ViewBag.PersonID = id.Value;
viewModel.Courses = instructors.Where(i => i.PersonID == id.Value).Single().Courses;
}
if (courseID != null)
{
ViewBag.CourseID = courseID.Value;
viewModel.Enrollments = viewModel.Courses.Where(x => x.CourseID == courseID).Single().Enrollments;
}
switch (sortOrder)
{
case "Name_desc":
instructors = instructors.OrderByDescending(x => x.LastName);
break;
case "FirstName":
instructors = instructors.OrderBy(x => x.FirstMidName);
break;
case "FirstName_desc":
instructors = instructors.OrderByDescending(x => x.FirstMidName);
break;
case "Date":
instructors = instructors.OrderBy(x => x.HireDate);
break;
case "Date_desc":
instructors = instructors.OrderByDescending(x => x.HireDate);
break;
case "Loc":
instructors = instructors.OrderBy(x => x.OfficeAssignment.Location);
break;
case "Loc_desc":
instructors = instructors.OrderByDescending(x => x.OfficeAssignment.Location);
break;
default:
instructors = instructors.OrderBy(x => x.LastName);
break;
}
int pageSize = Constants.PAGE_SIZE;
int pageNumber = (page ?? 1);
viewModel.Instructors = instructors.ToPagedList(pageNumber, pageSize);
return(View(viewModel));
}
public async Task OnGetAsync(int?id, int?courseID)
{
Instructor = new InstructorIndexData();
Instructor.Instructors = await _context.Instructors
.Include(i => i.OfficeAssignment)
.Include(i => i.CourseAssignments)
// .ThenInclude(c => c.Course)
// .ThenInclude(i=>i.Department)
//.Include(i=>i.CourseAssignments)
// .ThenInclude(i=>i.Course)
// .ThenInclude(i=>i.Enrollments)
// .ThenInclude(i=>i.Student)
//.AsNoTracking()
.OrderBy(i => i.LastName)
.ToListAsync();
if (id != null)
{
InstructorID = id.Value;
var seletedInstructor = Instructor.Instructors.Where(i => i.ID == id).Single();
//1. Load를 이용한 별도 쿼리
//await _context.CourseAssignments.Where(c => c.InstructorID == seletedInstructor.ID).LoadAsync();
//foreach (var item in seletedInstructor.CourseAssignments)
//{
// await _context.Courses.Include(c => c.Department).Where(c => c.CourseID == item.CourseID).LoadAsync();
//}
//2. 명시적 로드
await _context.Entry(seletedInstructor).Collection(x => x.CourseAssignments).LoadAsync();
foreach (var item in seletedInstructor.CourseAssignments)
{
await _context.Entry(item).Reference(x => x.Course).LoadAsync();
await _context.Entry(item.Course).Reference(x => x.Department).LoadAsync();
}
Instructor.Courses = seletedInstructor.CourseAssignments.Select(c => c.Course);
}
if (courseID != null)
{
CourseID = courseID.Value;
//1. 즉시로드
//Instructor.Enrollments = Instructor.Courses.Where(c => c.CourseID == courseID).Single().Enrollments;
//2. 별도로드
var selectedCourse = Instructor.Courses.Where(x => x.CourseID == courseID).Single();
await _context.Enrollments.Include(e => e.Student).Where(e => e.CourseID == selectedCourse.CourseID).LoadAsync();
//foreach (var item in selectedCourse.Enrollments)
//{
// await _context.Students.Where(s => s.ID == item.StudentID).LoadAsync();
//}
Instructor.Enrollments = selectedCourse.Enrollments;
//3.명시적 로드
//var selectedCourse = Instructor.Courses.Where(x => x.CourseID == courseID).Single();
//await _context.Entry(selectedCourse).Collection(x => x.Enrollments).LoadAsync();
//foreach (var enrollment in selectedCourse.Enrollments)
//{
// await _context.Entry(enrollment).Reference(x => x.Student).LoadAsync();
//}
//Instructor.Enrollments = selectedCourse.Enrollments;
}
}
public async Task OnGetAsync(int?id, int?courseID)
{
int pageIndex = 0;
int pageSize = 3;
string searchField = string.Empty;
string searchQuery = string.Empty;
bool searchMode = false;
////////////////////////////////////////////////////////////////////////////////////
if (!String.IsNullOrEmpty(Request.Query["SearchField"]) &&
!String.IsNullOrEmpty(Request.Query["SearchQuery"]))
{
searchMode = true;
searchField = Request.Query["SearchField"];
searchQuery = Request.Query["SearchQuery"];
}
searchingVar = new SearchingVar();
searchingVar.SearchMode = searchMode;
searchingVar.SearchField = searchField;
searchingVar.SearchQuery = searchQuery;
searchingVar.DicField = new Dictionary <string, string>()
{
{ "LastName", "성" },
{ "FirstMidName", "이름" },
{ "Name", "전체" }
};
//////////////////////////////////////////////////////////////////////////////
Instructor = new InstructorIndexData();
var iqInstructor = (IQueryable <Instructor>)_context.Instructors
.Include(i => i.OfficeAssignment)
.Include(i => i.CourseAssignments)
.ThenInclude(c => c.Course)
.ThenInclude(c => c.Department);
//.Include(i => i.CourseAssignments)
// .ThenInclude(c => c.Course)
// .ThenInclude(c => c.Enrollments)
// .ThenInclude(e => e.Student);
if (searchMode)
{
switch (searchField)
{
case "LastName":
iqInstructor = iqInstructor.Where(s => s.LastName.Contains(searchQuery));
break;
case "FirstMidName":
iqInstructor = iqInstructor.Where(s => s.FirstMidName.Contains(searchQuery));
break;
default:
iqInstructor = iqInstructor.Where(s => s.FirstMidName.Contains(searchQuery) || s.LastName.Contains(searchQuery));
break;
}
}
iqInstructor = iqInstructor.OrderByDescending(i => i.ID);
//[1] 쿼리스트링에 따른 페이지 보여주기
if (!string.IsNullOrEmpty(Request.Query["Page"]))
{
// Page는 보여지는 쪽은 1, 2, 3, ... 코드단에서는 0, 1, 2, ...
pageIndex = Convert.ToInt32(Request.Query["Page"]) - 1;
//Response.Cookies.Append("Page", pageIndex.ToString());
}
TotalCount = iqInstructor.Count();
PageIndex = pageIndex + 1;
Instructor.Instructors = await iqInstructor
.Skip((pageIndex) *pageSize).Take(pageSize).ToListAsync();
if (id != null)
{
InstructorID = id.Value;
var inss = Instructor.Instructors.Where(i => i.ID == id.Value).Single();
Instructor.Courses = inss.CourseAssignments.Select(c => c.Course);
}
if (courseID != null)
{
CourseID = courseID.Value;
//Instructor.Enrollments =_context.Courses
// .Include(c=>c.Enrollments)
// .ThenInclude(e=>e.Student)
// .Where(x => x.CourseID == courseID).Single().Enrollments;
var selectedCourse = Instructor.Courses.SingleOrDefault(c => c.CourseID == courseID);
await _context.Entry(selectedCourse).Collection(x => x.Enrollments).LoadAsync();
foreach (var item in selectedCourse.Enrollments)
{
await _context.Entry(item).Reference(x => x.Student).LoadAsync();
}
Instructor.Enrollments = selectedCourse.Enrollments;
}
}
//// GET: Instructors
//public async Task<IActionResult> Index()
//{
// return View(await _context.Instructors.ToListAsync());
//}
public async Task <IActionResult> Index(int?id, int?courseID)
{
/*
* The code begins by creating an instance of the view model and putting in it the list of instructors.
* The code specifies eager loading for the Instructor.OfficeAssignment and the Instructor.CourseAssignments
* navigation properties.
*
* Within the CourseAssignments property,
* the Course property is loaded, and within that, the Enrollments and Department properties are loaded,
* and within each Enrollment entity the Student property is loaded.
*
* The code repeats CourseAssignments and Course because you need two properties from Course.
* The first string of ThenInclude calls gets CourseAssignment.Course, Course.Enrollments,
* and Enrollment.Student
*
* At that point in the code, another ThenInclude would be for navigation properties of Student,
* which you don't need.
* But calling Include starts over with Instructor properties, so you have to go through the chain again,
* this time specifying Course.Department instead of Course.Enrollments.
*
*/
var viewModel = new InstructorIndexData();
viewModel.Instructors = await _context.Instructors
.Include(i => i.OfficeAssignment)
.Include(i => i.CourseAssignments)
.ThenInclude(i => i.Course)
.ThenInclude(i => i.Enrollments)
.ThenInclude(i => i.Student)
.Include(i => i.CourseAssignments)
.ThenInclude(i => i.Course)
.ThenInclude(i => i.Department)
.AsNoTracking()
.OrderBy(i => i.LastName)
.ToListAsync();
/*
* The following code executes when an instructor was selected.
* The selected instructor is retrieved from the list of instructors in the view model.
* The view model's Courses property is then loaded with the
* Course entities from that instructor's CourseAssignments navigation property.
*/
if (id != null)
{
ViewData["InstructorID"] = id.Value;
Instructor instructor = viewModel.Instructors.Where(
i => i.ID == id.Value).Single();
viewModel.Courses = instructor.CourseAssignments.Select(s => s.Course);
}
/*
* Next, if a course was selected, the selected course is retrieved from the list of courses in the view model.
* Then the view model's Enrollments property is loaded with the
* Enrollment entities from that course's Enrollments navigation property.
*/
if (courseID != null)
{
ViewData["CourseID"] = courseID.Value;
viewModel.Enrollments = viewModel.Courses.Where(
x => x.CourseID == courseID).Single().Enrollments;
}
return(View(viewModel));
}
// GET: Instructor
public async Task <IActionResult> Index(int?id, //Add param for Selected Instructor
int?courseID) //Add param for Selected Course
{
//Original code - converted to using a ViewModel
//return View(await _context.Instructors
// .Include(i=>i.OfficeAssignment)//Include offices assigned to instructor
// .ToListAsync());
//Create instance of our InstructorIndexData ViewModel
var viewModel = new InstructorIndexData();
//Populate the ViewModel Instructors from database context
viewModel.Instructors = await _context.Instructors
.Include(i => i.OfficeAssignment) //include offices assigned
.Include(i => i.Courses) //include courses
.ThenInclude(i => i.Course) //get the course entity as well
.ThenInclude(i => i.Department) //get the department entity as well
.ToListAsync();
//================ INSTRUCTOR HAS BEEN SELECTED ===============//
//check if id param has been passed in
if (id != null)
{
//Get the single instructor's data
Instructor instructor = viewModel.Instructors
.Where(i => i.ID == id.Value)
.SingleOrDefault();
//check if instructor was found
if (instructor == null)
{
return(NotFound()); //return not found page
}
//if we get this far, ok to get courses that instructor teaches
//populate view model with instructor courses
viewModel.Courses = instructor.Courses.Select(s => s.Course);
//Get the Instructor name for display in view
ViewData["InstructorName"] = instructor.FullName;
//Get the instructor id for use within view (highlighting selected row)
ViewData["InstructorID"] = id.Value; //the id that was passed in url
//or get it from instructor object
//ViewData["InstructorID"] = instructor.ID;
}
//================== END INSTRUCTOR SELECTED ==================//
//===================== COURSE SELECTED =======================//
if (courseID != null)
{
//If we have the courseID param - get all enrollments for this course
//using explicit loading this time: loading only if required
_context.Enrollments //get all enrollments
.Include(i => i.Student) //including student
//Only for selected course
.Where(c => c.CourseID == courseID.Value).Load(); //explicit loading
//get enrollments for this specic course
var enrollments = viewModel.Courses
.Where(e => e.CourseID == courseID).SingleOrDefault();
if (enrollments == null)
{
return(NotFound());
}
//Populate viewModel with enrollments
viewModel.Enrollments = enrollments.Enrollments;
//Pass CourseID back to view (for selected course highlight)
ViewData["CourseID"] = courseID.Value;
}
//================== END COURSE SELECTED ======================//
return(View(viewModel));
}
public async Task OnGetAsync(int?id, int?courseID)
{
Instructor = new InstructorIndexData();
Instructor.Instructors = await _context.Instructors
.Include(i => i.OfficeAssignment)
.Include(i => i.CourseAssignments)
.ThenInclude(i => i.Course)
.ThenInclude(i => i.Department)
// .Include(i => i.CourseAssignments)
// .ThenInclude(i => i.Course)
// .ThenInclude(i => i.Enrollments)
// .ThenInclude(i => i.Student)
//.AsNoTracking() 取消显示全部加载，
.OrderBy(i => i.LastName)
.ToListAsync();
/* SELECT[i].[ID], [i].[FirstName], [i].[HireDate], [i].[LastName], [i.OfficeAssignment].[InstructorID], [i.OfficeAssignment].[Location]
* FROM[Instructor] AS[i]
* LEFT JOIN[OfficeAssignment] AS[i.OfficeAssignment] ON[i].[ID] = [i.OfficeAssignment].[InstructorID]
* ORDER BY[i].[LastName], [i].[ID]
*
* 生成一下两个查询，效率感觉很低呀
*
* SELECT[i.CourseAssignments].[CourseID], [i.CourseAssignments].[InstructorID], [c.Course].[CourseID], [c.Course].[Credits], [c.Course].[DepartmentID], [c.Course].[Title]
* FROM[CourseAssignment] AS[i.CourseAssignments]
* I NNER JOIN[Course] AS[c.Course] ON[i.CourseAssignments].[CourseID] = [c.Course].[CourseID]
* INNER JOIN(
* SELECT DISTINCT [i0].[ID], [i0].[LastName]
* FROM [Instructor] AS [i0]
* LEFT JOIN [OfficeAssignment] AS[i.OfficeAssignment0] ON [i0].[ID] = [i.OfficeAssignment0].[InstructorID]
* ) AS[t] ON[i.CourseAssignments].[InstructorID] = [t].[ID]
* ORDER BY[t].[LastName], [t].[ID]
*/
//if (id != null)
//{
// InstructorID = id.Value;
// Instructor instructor = Instructor.Instructors.Where(i => i.ID == id.Value).Single(); //集合仅包含一个选项的时候才能用，空或者多个都会报异常
// Instructor.Courses = instructor.CourseAssignments.Select(s => s.Course);
//}
//if (courseID != null)
//{
// CourseID = courseID.Value;
// Instructor.Enrollments = Instructor.Courses.Where(x => x.CourseID == courseID).Single().Enrollments;
//}
if (id != null)
{
InstructorID = id.Value;
Instructor instructor = Instructor.Instructors.Single(//直接在Single里面调用where条件
i => i.ID == id.Value);
Instructor.Courses = instructor.CourseAssignments.Select(
s => s.Course);
}
//if (courseID != null)
//{
// CourseID = courseID.Value;
// Instructor.Enrollments = Instructor.Courses.Single(
// x => x.CourseID == courseID).Enrollments;
//} 用下列进行显示load加载
if (courseID != null)
{
CourseID = courseID.Value;
var selectedCourse = Instructor.Courses.Where(x => x.CourseID == courseID).Single();
await _context.Entry(selectedCourse).Collection(x => x.Enrollments).LoadAsync();
foreach (Enrollment enrollment in selectedCourse.Enrollments)
{
await _context.Entry(enrollment).Reference(x => x.Student).LoadAsync();
}
Instructor.Enrollments = selectedCourse.Enrollments;
}
}
