How do you make an 'attendance table' for

I have been having a tough time getting an attendance table for a classroom to be displayed in an appropriate fashion. I have an Attendance table in my database that I can get to display quite easily with the data from just one classroom, but it displays in a way that looks like how it would look if you browsed to the attendance table directly in the database.

It is probably easier for me to explain by showing my work:

I have the following classes:

The actual classroom class, or Course:

public class Course
{
    public int CourseID { get; set; }
    public string Title { get; set; }
    public int AttendanceDate { get; set;}
    public virtual ICollection<Enrollment> Enrollments { get; set; } // allows Students to be enrolled in a Course
    etc. . .
}

My Students:

public class Student
{
    public int StudentID { get; set; }
    public string Name { get; set; }
    public virtual ICollection<Enrollment> Enrollments { get; set; } // allows Student to be enrolled in a Course
    etc. . .
}

The entity that ties the Students to the Courses:

public class Enrollment
{
    public int EnrollmentID { get; set; }
    public int CourseID { get; set; }
    public int StudentID { get; set; }
    public virtual Course Course { get; set; }
    public virtual Student Student { get; set; }
}

Attendance data:

public class Attendance
{
    public int AttendanceID { get; set; }
    public int CourseID { get; set; }
    public int StudentID { get; set; }
    public int AttendanceDay { get; set; }  // used to set how many days people are supposed to attend this Course (each course has a different length, some are 10 day courses, some are 3, etc.)
    public bool Present { get; set; }  // absent or present (set to absent by default)
    public virtual Course Course { get; set; }
    public virtual Student Student { get; set; }
}

The flow of my project has Instructors creating a Course for Students to sign up. When a Student signs up for a Course, all the necessary Attendance data is input into the Attendance database table with default values (absent every day):

for (int i = 0; i < course.AttendingDays; i++)
{
    Attendance newAttendance = new Attendance
    {
        CourseID = course.CourseID,
        StudentID = thisStudent.StudentID,
        AttendanceDay = i + 1,
        Present = false
    };
    db.Attendance.Add(newAttendance);
    db.Save();
}

So I have a database table with a bunch of attendance data and I cannot get it to display correctly on the screen, sorted similar to this:

Attendance Day 1 | 2 | 3 | 4 | 5 |

Student1 absent absent absent absent absent

Student2 absent absent absent absent absent

Student3 absent absent absent absent absent

Hopefully you can read that. . . I assume it is a pretty standard layout for an attendance table.

I have tried sorting the data with Group By:

var model = from s in db.Attendance
                             where s.CourseID == 4
                             group s.AttendanceDay by s.StudentID into t
                             select new
                             {
                                 StudentID = t.Key,
                                 Days = t.OrderBy(x => x)
                             };
        return View(model);

Which returns an IEnumerable of anonymous type (If I understand correctly), but I cannot seem to get this data to do anything. If I use 'simpler' table generators (and don't try to Group By the AttendanceDay number) I get the attendance table data just fine, but it is sorted just like it is when you view the actual database table for Attendance, not very useful if you want an Instructor to read and edit the information.

I am thinking I need an appropriate ViewModel to adjust the incoming attendance data in the IEnumerable of anonymous type format, followed by a view that appropriately displays that ViewModel. . . but I am not sure how I would handle that process.

Any help would be appreciated. Thank you.

Update:

I am beginning to think I need to take advantage of a "cross-tab report" / "pivoting" and use something like this: http://linqlib.codeplex.com/wikipage?title=Pivot&referringTitle=Home

Any pointers?

Update 2:

Almost completely solved using the below accepted answer, and here is my current controller:

        // Generates list of Attendances specifically for current Course
        var attendanceItems = db.Attendance.Where(s => s.CourseID == id);
        List<Attendance> attendanceItemsList = attendanceItems.ToList();
        // End of generating list of Attendances


        // CURRENT PROBLEM AREA - INCOMPLETE
        var student = attendanceItemsList.Select(a => a.Student).Distinct()/*.OrderBy(a => a)*/;  // This works for adding one student, Cannot use OrderBy in its current state - how can I properly order by name?  (right now it will order by id I believe)
        List<Student> StudentList = student.ToList();;

        //
        // Generates list of AttendingDays specifically for current Course
        Course course = db.Courses.FirstOrDefault(p => p.CourseID == id);
        List<int> attDayList = new List<int>();
        for (int i = 0; i < course.AttendingDays; i++)
        {
            attDayList.Add(i + 1);
        };
        // End of generating list of AttendingDays

        AttendanceReportViewModel model = new AttendanceReportViewModel
        {
            AttendanceDays = attDayList,
            Students = StudentList,
            Attendances = attendanceItemsList,
        };
        return View(model);

I think you should look into creating a ViewModel that contains a list of days, a list of students, and a list of attendance entries, then simply iterate over the students and within it over the attendance days and then display the attendance record for each.

So the ViewModel would look something like this:


public class AttendanceReportViewModel
{
    public List<int> AttendanceDays { get; set; }
    public List<Student> Students { get; set; }
    public List<Attendance> Attendances { get; set; }

    public string IsPresent(Student student, int attendanceDay)
    {
         return Attendances.Single(a => a.StudentID == student.ID && a.AttendanceDay == attendanceDay).Present ? "present" : "absent";
    }
}

Make sure to sort all items the way you would like them to appear, but this is simple OrderBy stuff. Also make sure to only load Attendance for the course in question and add any course data to the viewModel you want to show.

Then in your view iterate like so:


<table>
    <thead>
        <tr>
            <th>Attendance Day</th>
            @foreach (var attendanceDay in Model.AttendanceDays)
            {
                <th>@attendanceDay</th>
            }
        </tr>
    <thead>
    <tbody>
        @foreach (var student in Model.Students)
        {
            <tr>
                <td>@student.Name</td>
                @foreach (var attendanceDay in Model.AttendanceDays)
                {
                    <td>@Model.IsPresent(student, attendanceDay)</td>
                }
            </tr>
        }
    </tbody>
}

Just whacked this together for you, so not even sure if my exact code compiles, but I hope it gives you a good starting point. Obviously some styling to the table, etc. should be applied.

EDIT 1:

To load data into the view model you can just do this in the controller (ultimately this should technically speaking be in repositories and maybe even a service layer)


var viewModel = new AttendanceReportViewModel();

viewModel.AttendanceDays = db.Attendance.Select(a => a.AttendanceDay).Distinct().OrderBy(a => a)
...

I should also have noted that of course you could also only have loaded the attendance data only into the view model and then produced the AttendanceDays and Students properties in the ViewModel from this data. I avoided this for now as you would most likely want Student data as well.

EDIT 2

For students it is just more of the same you want all students enrolled in the class, but since you have initialized attendance data for all days up front you can just do this:


var viewModel = new AttendanceReportViewModel();

viewModel.AttendanceDays = db.Attendance.Select(a => a.AttendanceDay).Distinct().OrderBy(a => a);
viewModel.Students = db.Attendance.Select(a => a.Student).Distinct().OrderBy(s => s.Name);

Alternatively you can just load this information from the Enrollment table like so:


viewModel.Students = db.Enrollment.Where(e => e.CourseID == courseID).Select(e => e.Student).OrderBy(s => s.Name);

Please note that I presume that there is a unique constraint between course and student id on the enrollment table and that courseID is what you produce the attendance report for. Also you have Course in your Enrollment table mapped to Student above, which I would assume is incorrect.