// 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.PersonID = id.Value;
viewModel.Courses = viewModel.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;
}
return View(viewModel);
}
// GET: Instructor
public ActionResult Index(int? id, int? courseId)
{
_connection = Connect.Open();
var viewModel = new InstructorIndexData();
//The LEFT JOIN keyword returns all rows from the left table (Instructor),
//with the matching rows in the right table (OfficeAssignment).
//The result is NULL in the right side when there is no match
_query.Append("SELECT i.*, oa.Location FROM dbo.Instructor i " +
"LEFT JOIN dbo.OfficeAssignment oa ON oa.InstructorId = i.InstructorId");
viewModel.Instructors = _connection.Query<Instructor, OfficeAssignment, Instructor>(_query.ToString(),
((instructor, assignment) =>
{
instructor.OfficeAssignment = assignment;
return instructor;
}), splitOn: "Location");
_query.Clear();
viewModel.Instructors.ForEach(s =>
{
GetInstructorCourse(s.InstructorId).ForEach(s.AddCourse);
});
if (id != null)
{
ViewBag.InstructorId = id.Value;
_query.Append(@"SELECT c.CourseId, c.Title, d.Name FROM dbo.Course c " +
"INNER JOIN dbo.Department d ON d.DepartmentId = c.DepartmentId " +
"INNER JOIN dbo.CourseInstructor ci ON ci.CourseId = c.CourseId " +
"WHERE ci.InstructorId = @id");
viewModel.Courses = _connection.Query<Course, Department, Course>(_query.ToString(),
((course, department) =>
{
course.Department = department;
return course;
}),
new { id },
splitOn: "Name");
_query.Clear();
}
if (courseId != null)
{
ViewBag.CourseId = courseId.Value;
_query.Append("SELECT e.Grade, s.LastName, s.FirstMidName FROM dbo.Enrollment e " +
//Note -----------------------------------------------------^ 1st
"INNER JOIN dbo.Student s ON s.StudentId = e.StudentId " +
"WHERE e.CourseId = @courseId");
viewModel.Enrollments = _connection.Query<Enrollment, Student, Enrollment>(_query.ToString(),
//Note -------------------------------------^ 2nd
((enrollment, student) =>
{
enrollment.Student = student;
return enrollment;
}),
new { courseId },
splitOn: "LastName");
_query.Clear();
}
return View(viewModel);
}