How to get entities from multiple tables with one LINQ call?-CodePudding

I've 4 tables and 4 calls to DB to get information from each of them. Trying to make it more optimal and get all 4 entities in 1 call. This is my current code

var teacher = await Teachers
    .Where(t => t.Id == teacherId)
    .FirstOrDefaultAsync(cancellationToken);

var student = await Students
    .Where(s => s.Teacher == teacher)
    .FirstOrDefaultAsync(cancellationToken);

var studentGrade = await StudentGrades
    .Where(s => s.StudentId == student.Id)
    .ToListAsync(cancellationToken);

var studentPlace = await StudentPlaces.
    .Where(sp => sp.Id == student.StudentPlaceId)
    .FirstOrDefaultAsync(cancellationToken);

Is there a way to get all 4 of these entities under one call?

Student table has 1-to-1 relationship with Teacher. I tried to do joins but it looks ugly. I have way too many nested objects.

var result= await Students.
           .Join(Teachers, u => u.Teacher, e=>e, (student, teacher) => new { student, employee })
           .Join(StudentGrades, u=>u.student.Id, up=>up.StudentId, (student, studentGrade) => new { student, studentGrade})
           .Join(StudentPlaces, up=>up.student.student.Id, upr=>upr.Id, (student, studentPlace) => new { student, studentPlace})
           .Where(x => x.student.student.teacher.Id == teacherId)
           .FirstOrDefaultAsync(cancellationToken);

CodePudding user response：

You can achieve this by using this query but You must have physical relations between these tables to the navigation properties. I am assuming that Student has collection of StudentGrades and Student has collection of StudentPlaces for this kind of relation this query is you can use.

var teacher = await Teachers
    .Include(x => x.Students)
        .ThenInclude(x => x.StudentGrades)
    .Include(x => x.Students)
        .ThenInclude(x => x.StudentPlaces)
    .Where(t => t.Id == teacherId)
    .FirstOrDefaultAsync(cancellationToken);

Include part is getting related navigation properties which are in relation with Teacher and You have already said that Teacher is linked with Student. ThenInclude is basically used to get the sub-entities for example Student has relation with Grades and StudentPlaces then you can get these by using ThenIclude.

Additional For Linq to Sql

    var result = await (from t in Teachers
                 join s in Students
                 on t.Id equals s.TeacherId
                 join g in StudentGrades
                 on s.Id equals g.StudentId
                 join p in StudentPlaces
                 on s.Id equals p.StudentId
                 where t.Id = teacherId
                 select new { t.TeacherName, s.StudentName }).FirstOrDefaultAsync(cancellationToken);