C# LINQ left join 3 Tables/Lists

Question

I am looking to join 3 tables which I am getting as List of objects. These are my three tables:

Employee

Department

Category

Employee DepartmentID and CategoryID is used to join Department and Category Table.

This is how my Linq Join looks

var result = from e in Employee.GetAllEmployees()
                         join d in Department.GetAllDepartments() on e.DepartmentID equals d.ID
                         join c in Cateory.GetAllCategories() on e.CategoryID equals c.ID
                         into eGroup
                         from c in eGroup.DefaultIfEmpty()
                         select new
                         {
                             Employee =e,
                             Department = d ==null? new Department() : d,
                             Cateory = c
                         };

My issue here is that I am getting two different rows for Employee ID = 1 and that's because of two different Categories for ID =1

I would like to get both categories in the same Employee node. Basically two categories for Employee ID = 1.

Expected Result: CategoryA and CategoryB is tied to Employee Mark.

How do I achieve that?

Thanks for the help !

Here is the code to reproduce what I have so far.

class Program
{
    static void Main(string[] args)
    {
        var result = from e in Employee.GetAllEmployees()
                     join d in Department.GetAllDepartments() on e.DepartmentID equals d.ID
                     join c in Cateory.GetAllCategories() on e.CategoryID equals c.ID
                     into eGroup
                     from c in eGroup.DefaultIfEmpty()
                     select new
                     {
                         Employee =e,
                         Department = d ==null? new Department() : d,
                         Cateory = c
                     };

        Console.WriteLine("Hello World!");
        Console.ReadLine();
    }

    public class Employee
    {
        public int EmployeeID { get; set; }
        public string Name { get; set; }

        public int DepartmentID { get; set; }
        public int CategoryID { get; set; }

        public static List<Employee> GetAllEmployees()
        {
            return new List<Employee>()
                    {
                        new Employee { EmployeeID = 1, Name = "Mark", DepartmentID = 1, CategoryID = 1 },
                    };
        }
    }

    public class Department
    {
        public int ID { get; set; }
        public string DepartmentName { get; set; }

        public static List<Department> GetAllDepartments()
        {
            return new List<Department>()
                {
                    new Department { ID = 1, DepartmentName = "TECH"},
                    new Department { ID = 2, DepartmentName = "HR"},
                };
        }
    }

    public class Cateory
    {
        public int ID { get; set; }
        public string CategoryName { get; set; }

        public static List<Cateory> GetAllCategories()
        {
            return new List<Cateory>()
                {
                 new Cateory { ID = 1, CategoryName = "CategoryA"},
                 new Cateory { ID = 1, CategoryName = "CategoryB"},
                 new Cateory { ID = 2, CategoryName = "CategoryC"},
            };
        }
    }
}

Answer 1

Here are your results. The two employer have the same ID number

Answer 2

I don't have a way of testing this really but you should be able to do a left join by updating your query

 var result = from e in Employee.GetAllEmployees()
                 join d in Department.GetAllDepartments() on e.DepartmentID equals d.ID into d_def
                 from d in d_def.DefaultIfEmpty()
                 join c in Cateory.GetAllCategories() on e.CategoryID equals c.ID into c_def
                 from c in c_def.DefaultIfEmpty())
                 select new
                 {
                     Employee =e,
                     Department = d ==null? new Department() : d,
                     Cateory = c
                 };