public static void Report() { DataRepository repository = new DataRepository(); repository.Initialize(); // Test results grouped by user. Used in other queries. var resultsByUser = from tr in repository.TestResults group tr by tr.User into tmp select new {User = tmp.Key, Result = tmp}; // Report 1. Users that successfully passed their tests, regardless of time taken. var Report1 = from ru in resultsByUser where (from tr in ru.Result where tr.Result >= tr.Test.PassingScore select tr).Count() == ru.Result.Count() select ru.User; // Report 2. Users that successfully passed their tests in time. var Report2 = from ru in resultsByUser where (from tr in ru.Result where tr.Result >= tr.Test.PassingScore && tr.TimeTaken <= tr.Test.TimeGiven select tr).Count() == ru.Result.Count() select ru.User; // Report 3. Users that successfully passed their tests, but didn't make it in time. var Report3 = from ru in resultsByUser where (from tr in ru.Result where tr.Result >= tr.Test.PassingScore && tr.TimeTaken > tr.Test.TimeGiven select tr).Count() > 0 && (from tr in ru.Result where tr.Result >= tr.Test.PassingScore select tr).Count() == ru.Result.Count() select ru.User; // Report 4. Number of students grouped by city var Report4 = from u in repository.Users group u by u.City into tmp select new { City = tmp.Key, User = tmp }; // Report 5. Successful students grouped by city var Report5 = from u in (from ru in resultsByUser where (from tr in ru.Result where tr.Result >= tr.Test.PassingScore && tr.TimeTaken <= tr.Test.TimeGiven select tr).Count() == ru.Result.Count() select ru.User) group u by u.City into tmp select new { City = tmp.Key, User = tmp }; // Report 6. Results for every student + success rate (%) var Report6 = from s in (from tr in repository.TestResults group tr by tr.User into tmp select new { User = tmp.Key, Result = tmp }) select new { User = s.User, Result = s.Result, Rate = (double)s.Result.Sum(item => item.Result) / 400 * 100 }; Console.WriteLine("\nReport 1. Users that successfully passed their tests, regardless of time taken"); foreach (var item in Report1) { Console.WriteLine("{0} -- {1} -- Age : {2} -- {3} -- {4} -- {5}", item.Name, item.Email, item.Age, item.Category, item.City, item.University); } Console.WriteLine("\nReport 2. Users that successfully passed their tests in time"); foreach (var item in Report2) { Console.WriteLine("{0} -- {1} -- Age : {2} -- {3} -- {4} -- {5}", item.Name, item.Email, item.Age, item.Category, item.City, item.University); } Console.WriteLine("\nReport 3. Users that successfully passed their tests but didn't make it in time"); foreach (var item in Report3) { Console.WriteLine("{0} -- {1} -- Age : {2} -- {3} -- {4} -- {5}", item.Name, item.Email, item.Age, item.Category, item.City, item.University); } Console.WriteLine("\nReport 4. Number of students grouped by city"); foreach (var item in Report4) { Console.WriteLine("{0} -- {1} student(s)", item.City, item.User.Count()); foreach (var student in item.User) { Console.WriteLine("{0} -- {1} -- Age : {2} -- {3} -- {4} -- {5}", student.Name, student.Email, student.Age, student.Category, student.City, student.University); } } Console.WriteLine("\nReport 5. Successful students grouped by city"); foreach (var item in Report5) { Console.WriteLine("{0} -- {1} successful student(s)", item.City, item.User.Count()); foreach (var student in item.User) { Console.WriteLine("{0} -- {1} -- Age : {2} -- {3} -- {4} -- {5}", student.Name, student.Email, student.Age, student.Category, student.City, student.University); } } Console.WriteLine("\nReport 6. Results for every student + success rate (%)"); foreach (var item in Report6) { Console.WriteLine("{0} -- {1} -- Age : {2} -- {3} -- {4} -- {5}", item.User.Name, item.User.Email, item.User.Age, item.User.Category, item.User.City, item.User.University); Console.WriteLine("Success rate = {0}", item.Rate); foreach (var result in item.Result) { Console.WriteLine("{0} -- {1}", result.Test.Name, result.Result); } } }