How can I join multiple tables including a join-table (auto-generated by Entity Framework) using LIN-CodePudding

I need to do a query with multiple joins which include a join-table that was generated from a many-to-many relation. It works if I query the db directly, but I need to do the query in one of my controllers and since I don't have a model for the join-table I'm not able to do a simple join with a DbSet and I've been stuck for hours trying to find out how it works.

This are my Tables in the Database:

Exams

(PK) Id
(FK) ClassId

Relation: * to 1

Classes

(PK) Id

Relation: 1 to *

ClassesStudents

(PK) ClassId
(PK) StudentId

Relation: * to 1

Student

(PK) Id
(FK) PersonId

Relation: 1 to 1

Person

(PK) Id
FirstName
LastName

Those are my models:

public class Exam
{
    public int Id { get; set; }

    public short ClassId { get; set; }
    public Class Class { get; set; }
}

public class Class
{
    public Class()
    {
        this.Students = new HashSet<Student>();
    }

    public int Id { get; set; }

    public virtual ICollection<Student> Students { get; set; }
}

public class Student
{
    public Student()
    {
        this.Classes = new HashSet<Class>();
    }

    public int Id { get; set; }

    public virtual ICollection<Class> Classes { get; set; }
}

public class Person
{
    public int Id { get; set; }

    [Required]
    public string FirstName { get; set; }

    [Required]
    public string LastName { get; set; }
}

I am trying go get this Query...

SELECT stu.Id, peo.FirstName, peo.LastName
FROM Exams AS exa
JOIN Classes AS cla
ON cla.Id = exa.ClassId
JOIN ClassesStudents AS clastu
ON clastu.ClassId = cla.Id
JOIN Students AS stu
ON stu.Id = clastu.StudentId
JOIN People AS peo
ON peo.Id = stu.PersonId
WHERE exa.Id = 1;

... to work somewhat like that in my Controller:

public ActionResult CreateMark(int id)
    {
        var students = (from exams in _context.Exams
                       join classes in _context.Classes
                       on exams.ClassId equals classes.Id
                       join classesstudents in _context.ClassesStudents // that
                       on classes.Id equals classesstudents.ClassId     // part
                       join students in _context.Students               // doesn't
                       on classesstudents.StudentId equals students.Id  // work
                       join people in _context.People
                       on students.PersonId equals people.Id
                       where exams.Id == id
                       select new
                       {
                           Id = students.Id,
                           FullName = people.FirstName   " "   people.LastName
                       }).ToList();

I'd be extremely thankful for any help!

EDIT:

I managed to make it work with the following code, but is there a way to do it with less code in just one query?

public ActionResult Create(int id)
    {
        var class = (from exa in _context.Exams
                     join cla in _context.Classes
                     on exa.ClassId equals cla.Id
                     where exa.Id == id
                     select new
                     {
                         ClassId = cla.Id,
                         Students = cla.Students
                     }).ToList();

        var classWithStudents= (from cla in class
                                from stu in cla.Students
                                select new
                                {
                                    Id = stu.Id,
                                    PersonId = stu.PersonId
                                }).ToList();

        var students = (from stu in classWithStudents
                        join peo in _context.People
                        on stu.PersonId equals peo.Id
                        select new FullNameStudentViewModel
                        {
                            Id = stu.Id,
                            FullName = peo.FirstName   " "   peo.LastName
                        }).ToList();

CodePudding user response：

Use the following query:

var query = 
    from exams in _context.Exams
    from students in exams.Class.Students
    join people in _context.People on students.PersonId equals people.Id
    where exams.Id == id
    select new
    {
        Id = students.Id,
        FullName = people.FirstName   " "   people.LastName
    };