C# & LINQ: multiple join and group by on multiple fields

Trabla: C# & LINQ: multiple join and group by on multiple fields

Solving:

This  example shows show to join multiple tables
and group data by multiple table columns
using LINQ in C#


To run example use  https://dotnetfiddle.net/



using System;
using System.Collections.Generic;
using System.Linq;
                  
public class Program
{
  
    public class User
    {
        public int id {get;set;}
        public string name {get;set;}
    }
  
    public class Course
    {
        public int id {get;set;}
        public string name {get;set;}
    }
  
    public class CourseExercise
    {
        public int id {get;set;}
        public int courseid {get;set;}
        public string name {get;set;}
    }
  
    public class ExerciseUserResult
    {
        public int id {get;set;}
        public int userid {get;set;}
        public int exerciseid {get;set;}
        public int score {get;set;}
        public int attempt {get;set;}
    }

  
  
    public static void Main()
    {
      
      
        List<User> users = new List<User>
        {
                new User { id = 1, name = "Tommy" },
                new User { id = 2, name = "Jerry" },
                new User { id = 3, name = "Merry" }
        };
      
        List<Course> courses = new List<Course>
        {
                new Course { id = 1, name = "Level A" },
                new Course { id = 2, name = "Level B" }
        };
      
        List<CourseExercise> exercises = new List<CourseExercise>
        {
            new CourseExercise { id = 1, courseid = 1, name = "A1" },
            new CourseExercise { id = 2, courseid = 1, name = "A2" },
            new CourseExercise { id = 3, courseid = 1, name = "A3" },
          
            new CourseExercise { id = 4, courseid = 2, name = "B1" },
            new CourseExercise { id = 5, courseid = 2, name = "B2" },
            new CourseExercise { id = 6, courseid = 2, name = "B3" }
        };
      
        List<ExerciseUserResult> results = new List<ExerciseUserResult>
        {
            // First attempt of user Tommy, course A1
            new ExerciseUserResult{ id=1, userid=1,  exerciseid = 1, score = 10, attempt = 1 },
            new ExerciseUserResult{ id=2, userid=1,  exerciseid = 2, score = 15, attempt = 1 },
            new ExerciseUserResult{ id=3, userid=1,  exerciseid = 3, score = 25, attempt = 1 },
              
            // Second attempt of user Tommy, course A1  
            new ExerciseUserResult{ id=4, userid=1,  exerciseid = 1, score = 6, attempt = 2 },
            new ExerciseUserResult{ id=5, userid=1,  exerciseid = 2, score = 7, attempt = 2 },
            new ExerciseUserResult{ id=6, userid=1,  exerciseid = 3, score = 23, attempt = 2 },
              
            // First attempt of user Tommy, course B1
            new ExerciseUserResult{ id=7, userid=1,  exerciseid = 4, score = 14, attempt = 1 },
            new ExerciseUserResult{ id=8, userid=1,  exerciseid = 5, score = 12, attempt = 1 },
            new ExerciseUserResult{ id=9, userid=1,  exerciseid = 6, score = 21, attempt = 1 },
              
          
          
            // First attempt of user Jerry, course B1
            new ExerciseUserResult{ id=10, userid=2,  exerciseid = 4, score = 34, attempt = 1 },
            new ExerciseUserResult{ id=11, userid=2,  exerciseid = 5, score = 32, attempt = 1 },
            new ExerciseUserResult{ id=12, userid=2,  exerciseid = 6, score = 11, attempt = 1 },
              
          
            // First attempt of user Merry, course B1
            new ExerciseUserResult{ id=13, userid=3,  exerciseid = 4, score = 34, attempt = 1 },
            new ExerciseUserResult{ id=14, userid=3,  exerciseid = 5, score = 32, attempt = 1 },
            new ExerciseUserResult{ id=15, userid=3,  exerciseid = 6, score = 11, attempt = 1 },
              
            // Second attempt of user Merry, course B1
            new ExerciseUserResult{ id=16, userid=3,  exerciseid = 4, score = 34, attempt = 2 },
            new ExerciseUserResult{ id=17, userid=3,  exerciseid = 5, score = 32, attempt = 2 },
            new ExerciseUserResult{ id=18, userid=3,  exerciseid = 6, score = 11, attempt = 2 },  
          
              
        };
      
        // Select - user(name), course(name), attempt from dataset
        var query = from r in results
                    join e in exercises on r.exerciseid equals e.id
                    join c in courses on e.courseid equals c.id
                    join u in users on r.userid equals u.id
                  
                group new { user = u.name, course = c.name, attempt = r.attempt }
                by    new { user = u.name, course = c.name, attempt = r.attempt }
                into groupedData
          
                select groupedData.Key;
       
         // Print data            
        query.ToList().ForEach( x => {
          
            Console.WriteLine(
                string.Format("user = {0}, course = {1}, attempt = {2} ", x.user, x.course, x.attempt )
            );
        
        });
      
      
      
    }
}


Result:
user = Tommy, course = Level A, attempt = 1 
user = Tommy, course = Level A, attempt = 2 
user = Tommy, course = Level B, attempt = 1 
user = Jerry, course = Level B, attempt = 1 
user = Merry, course = Level B, attempt = 1 
user = Merry, course = Level B, attempt = 2