public IEnumerable <AttendanceSummaryViewModel> GetAttendanceSummary(DateTime start, DateTime end, int structuralGroupId) { var db = ApplicationContext.DatabaseContext.Database; using (var structureRepository = new StructureRepository(db, Services.MemberService)) { var structure = structureRepository.GetStructureFrom(structuralGroupId); var ids = new List <int>(); PopulateStructureIds(structure, ids); var query = string.Format("SELECT [Date], Attendance, COUNT(*) AS Total FROM AttendanceRecord " + "INNER JOIN AttendanceSet ON AttendanceRecord.AttendanceSetId = AttendanceSet.Id " + "WHERE [Date] BETWEEN '{0}' and '{1}' " + "AND StructuralGroupId IN ({2}) " + "GROUP BY [Date], Attendance", start.ToString("yyyy-MM-dd"), end.ToString("yyyy-MM-dd"), string.Join(",", ids)); var table = DbUtils.ExecuteDataTable(db, query); return(DateUtils.Range(start, end).Select(date => { var presentRow = table.Select(string.Format("Date = '{0:yyyy-MM-dd}' AND Attendance = {1}", date, (int)Attendance.Present)).FirstOrDefault(); var absentRow = table.Select(string.Format("Date = '{0:yyyy-MM-dd}' AND Attendance = {1}", date, (int)Attendance.Absent)).FirstOrDefault(); var present = presentRow == null ? 0 : presentRow.Field <int>("Total"); var absent = absentRow == null ? 0 : absentRow.Field <int>("Total"); return new AttendanceSummaryViewModel { Date = date, Present = present, Absent = absent }; })); } }