Join three list using multiple columns c# linq lambda

Question

I have these lists:

var subjects = new List<SubjectModel>
        {
            new SubjectModel { subjId = 1, subjName = "Math" },
            new SubjectModel { subjId = 2, subjName = "Science" },
            new SubjectModel { subjId = 3, subjName = "History" },
            new SubjectModel { subjId = 4, subjName = "Language" }
        };

var quizzes = new List<QuizModel>
        {
            new QuizModel { quizId = 1, quizDate = DateTime.Parse("2016-11-25"), quizScore = 10, subjectId = 1 },
            new QuizModel { quizId = 2, quizDate = DateTime.Parse("2016-11-25"), quizScore = 15, subjectId = 1 },
            new QuizModel { quizId = 3, quizDate = DateTime.Parse("2016-11-25"), quizScore = 8, subjectId = 2 },
            new QuizModel { quizId = 4, quizDate = DateTime.Parse("2016-11-26"), quizScore = 13, subjectId = 1 },
            new QuizModel { quizId = 5, quizDate = DateTime.Parse("2016-11-26"), quizScore = 20, subjectId = 2 }
        };

var exams = new List<ExamModel>
        {
            new ExamModel { examId = 1, examDate = DateTime.Parse("2016-11-25"), examScore = 90, subjectId = 1 },
            new ExamModel { examId = 2, examDate = DateTime.Parse("2016-11-25"), examScore = 88, subjectId = 2 },
            new ExamModel { examId = 3, examDate = DateTime.Parse("2016-11-25"), examScore = 92, subjectId = 4 },
            new ExamModel { examId = , examDate = DateTime.Parse("2016-11-26"), examScore = 84, subjectId = 1 },

        };

var exercises = new List<ExerciseModel>
        {
            new ExerciseModel { exerciseId = 1, exerciseDate = DateTime.Parse("2016-11-25"), exerciseScore = 17, subjectId = 1 }, 
            new ExerciseModel { exerciseId = 2, exerciseDate = DateTime.Parse("2016-11-25"), exerciseScore = 15, subjectId = 2 },
            new ExerciseModel { exerciseId = 3, exerciseDate = DateTime.Parse("2016-11-26"), exerciseScore = 15, subjectId = 1 },
            new ExerciseModel { exerciseId = 4, exerciseDate = DateTime.Parse("2016-11-26"), exerciseScore = 12, subjectId = 4 },
            new ExerciseModel { exerciseId = 5, exerciseDate = DateTime.Parse("2016-11-26"), exerciseScore = 10, subjectId = 1 },
        };

I was able to successfully group each of them by date and by subject.

var allQuizzes = quizzes.GroupBy(qz => qz.quizDate, (q, values) =>
            new
            {
                Date = q,
                Quizzes = values.GroupBy(v => v.subjectId, (c, values2) => 
                    new { 
                        SubjectId = c, 
                        QuizSum = values2.Sum(v2 => v2.quizScore) 
                    })
            });

var allExercises = exercises.GroupBy(ex => ex.exerciseDate, (e, values) =>
            new {
                Date = e,
                Exercises = values.GroupBy(x => x.subjectId, (z, values2) =>
                    new {
                        SubjectId = z,
                        ExerSum = values2.Sum(r => r.exerciseScore)
                    })
            });

var allExams = exams.GroupBy(ex => ex.examDate, (e, values) =>
            new
            {
                Date = e,
                Exercises = values.GroupBy(x => x.subjectId, (z, values2) =>
                    new
                    {
                        SubjectId = z,
                        ExamSum = values2.Sum(r => r.examScore)
                    })
            });

However, I need to join all three of them to get the sum of all scores. The final table should display like this.

-----------------------------------------------------------------
|      Date     |  Math  |  Science  |   History   |  Language  |
|   11/25/2016  |   132  |      111  |         0   |        92  |
|   11/26/2016  |   122  |       20  |         0   |        12  |
-----------------------------------------------------------------

I tried to join them, but it can't seem to join by multiple columns.

Answer 1

I select from all 3 collections results in form of the same anonymous class (the same Idea had Andrei in first answer), that allows me just to collect all results together in all list, without mapping and converting.

var allQuiz = quizzes.GroupBy(x => new { x.subjectId, x.quizDate })
                     .Select(x => new {
                          Date = x.Key.quizDate, 
                          Subj = x.Key.subjectId, 
                          Sum = x.Sum(r=>r.quizScore)});

var allExam= exams.GroupBy(x => new { x.subjectId, x.examDate })
                     .Select(x => new {
                          Date = x.Key.examDate, 
                          Subj = x.Key.subjectId, 
                          Sum = x.Sum(r=>r.examScore)});

var allExc = exercises.GroupBy(x => new { x.subjectId, x.exerciseDate })
                     .Select(x => new {
                          Date = x.Key.exerciseDate, 
                          Subj = x.Key.subjectId, 
                          Sum = x.Sum(r=>r.exerciseScore)});

Combining of all results together:

var all = allQuiz.ToList();
all.AddRange(allExam.ToList());
all.AddRange(allExc.ToList());

var result = all.GroupBy(x => new { x.Date, x.Subj })
                .Select(x => new { x.Key.Date, x.Key.Subj, Sum = x.Sum(s => s.Sum)});

var list = result.GroupBy(r => r.Date).Select(x => new {
       Date = x.Key, 
       Math = x.SingleOrDefault(t=>t.Subj==1)?.Sum ?? 0,
       Science = x.SingleOrDefault(t=>t.Subj==2)?.Sum ?? 0,
       History = x.SingleOrDefault(t=>t.Subj==3)?.Sum ?? 0,
       Language = x.SingleOrDefault(t=>t.Subj==4)?.Sum ?? 0,
       });

Output in LinqPad: