// 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; } }