How to join multiple tables to one table with lambda expression?

Question

I have 3 Table and Model like below

Table 1: Student

+----+-------+---------+
| ID | Name  | Subject |
+----+-------+---------+
| 1  | Alex  | 2       |
+----+-------+---------+
| 2  | Peter | 1       |
+----+-------+---------+
| 3  | Thomas| null    |
+----+-------+---------+

public class Student {

    public int ID {get; set;}
    public string Name {get; set;}
    public int Subject {get; set;}
}

Table 2: Subject

+----+---------+---------+
| ID | Subject | Teacher |
+----+---------+---------+
| 1  | Math    | 5       |
+----+---------+---------+
| 2  | History | 6       |
+----+---------+---------+

public class Subject {

    public int ID {get; set;}
    public string Subject {get; set;}
    public int Teacher {get; set;}
}

Table 3: Teacher

+----+---------+---------------+
| ID | TeacherName | Email     |
+----+-------------+-----------+
| 6  | John        | null      |
+----+-------------+-----------+
| 2  | Anna        | anna@aa.c |
+----+-------------+-----------+

public class Teacher {

    public int ID {get; set;}
    public string TeacherName {get; set;}
    public string Email {get; set;}
}

I create a new model like this

public class newModel{

    public Student studentList;
    public Teacher teacherList;
    public Subject subjectList;
}

How can I merge 3 table (model) above to a new table (model) with lambda expression?

It's mean I want join Table 1 to Table 2 by SubjectID. Then, new Table created by Table 1 and Table 2 join with Table 3 by Teacher ID. But keep every Things in Table 1 in new.

It's look like

List<newModel> finalList = new List<newModel>();
finalList = db.Student.Join( .... )

and the final result of finalList like below:

+----+--------+---------+-------------+-----------+-------------+-------+
| ID | Name   | Subject | SubjectName | TeacherID | Teachername | Email |
+----+--------+---------+-------------+-----------+-------------+-------+
| 1  | Alex   | 2       | History     | 6         | John        | null  |
+----+--------+---------+-------------+-----------+-------------+-------+
| 2  | Perter | 1       | Math        |           |             |       |
+----+--------+---------+-------------+-----------+-------------+-------+
| 3  | Thomas | null    |             |           |             |       |
+----+--------+---------+-------------+-----------+-------------+-------+

Answer 1

At first, update your models as below:

Student Model:

public class Student
{

    [Key]
    public int Id { get; set; }
    public string Name { get; set; }


    [ForeignKey(nameof(Subject))]
    public int? SubjectId { get; set; }
    public virtual Subject Subject { get; set; } //Navigation Property
}

Subject Model:

public class Subject
{
    [Key]
    public int Id { get; set; }
    public string SubjectName { get; set; }


    [ForeignKey(nameof(Teacher))]
    public int TeacherId { get; set; }
    public virtual Teacher Teacher { get; set; } //Navigation Property
}

And the Teacher Model:

    public class Teacher
    {
        [Key]
        public int Id { get; set; }
        public string TeacherName { get; set; }
        public string Email { get; set; }
    }

Then Add-Migration and update-Database. After All of these change your tables are connected together and with the below query, you can fetch data of these 3 tables.

var newModel = dbcontext.Students
                .Include(i => i.Subject)
                .ThenInclude(i => i.Teacher)
                .ToList();

now, base on your data, the result of newModel.First().Subject.Teacher.TeacherName is John. and as a conclusion, when you fetch these data you can map these data to another model or do anything that you want.

feel free to ask question. good luck.

Answer 2

If you have navigation properties then you can use Include() otherwise you can do join manually like below:

var result = (from student in db.Students
             join subject in db.Subjects on student.Subject equals subject.ID
             join teacher in db.Teachers on subject.Teacher equals teacher.ID
             where student.ID = 1
             select new newModel()
             {
                 studentList = student,
                 teacherList = teacher,
                 subejctList = subject
             }).ToList();