How do I convert this query to a LINQ query in Entity Framework? I am a new programmer still in school
SELECT
studfirstname, studlastname, grade
FROM
students
INNER JOIN
Student_Schedules ON students.StudentID = Student_Schedules.StudentID
INNER JOIN
Classes ON Student_Schedules.ClassID = Classes.ClassID
INNER JOIN
Subjects ON Classes.SubjectID = Subjects.SubjectID
WHERE
SubjectName = 'Introduction to Art';
As you are new in Lambda and LINQ I tried in the following Lambda to be as descriptive as possible..... You can use the Long Aliases in short form also student_schedules as ss OR studentsANDstudent_schedules as s_s_sch... Try this ...Hope this helps.... @Abdul Hameed
var filteredData = context.Students // your starting point
.Join(context.Student_Schedules, // the source table of the inner join
students => students.StudentID, // Select the primary key (the first part of the "on" clause in an sql "join" statement)
student_schedules => student_schedules.StudentID),// the foreign key
(students, student_schedules) => new { students, student_schedules })
.Join(context.Classes,
classes => classes.ClassID,
studentsANDstudent_schedules => studentsANDstudent_schedules.student_schedules.ClassID),
(classes, studentsANDstudent_schedules) => new { classes, studentsANDstudent_schedules })
.Join(context.Subjects,
subjects => subjects.SubjectID,
studentsANDstudent_schedulesANDClasses => studentsANDstudent_schedulesANDClasses.classes.SubjectID),
(subjects, studentsANDstudent_schedulesANDClasses) => new { subjects, studentsANDstudent_schedulesANDClasses })
.Where(allMergedTables => allMergedTables.subjects.SubjectName == "Intoduction to Art").ToList();
And this is the shortened version of the above one...
var filteredData = context.Students
.Join(context.Student_Schedules, s => s.StudentID, sch => sch.StudentID),
(s, sch) => new { s, sch })
.Join(context.Classes, c => c.ClassID, s_sch => s_sch.sch.ClassID),
(c, s_sch) => new { c, s_sch })
.Join(context.Subjects, sj => sj.SubjectID, s_sch_c => s_sch_c.c.SubjectID),
(sj, s_sch_c) => new { sj, s_sch_c })
.Where(all => all.sj.SubjectName == "Intoduction to Art")
.ToList();
You can use LINQ syntax:
from student in context.Students
join schedule in context.Student_Schedules on student.StudentID equals schedule.StudentID
join @class in context.Classes on schedule.ClassID equals @class.ClassID
join subject in context.Subjects on @class.SubjectID equals subject.SubjectID
where subject.SubjectName == "Introduction to Art"
var students = context.Students.Include("Student_Schedules")
.Include("Classes")
.Include("Subjects").Where(s => s.Subjects.Contains("Intoduction to Art"));
