CODEWITHSUNDEEP
c#sql-serverentity-frameworklinq
Abdul Hameed
Abdul Hameed

Reputation: 11

Convert SQL to Linq queries in Entity Framework

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';

Upvotes: 0

Views: 110

Answers (3)

J4ime
J4ime

Reputation: 155

var students = context.Students.Include("Student_Schedules")
                               .Include("Classes")
                               .Include("Subjects").Where(s => s.Subjects.Contains("Intoduction to Art"));

Info -> https://msdn.microsoft.com/en-us/data/jj574232.aspx

Upvotes: 0

khaled4vokalz
khaled4vokalz

Reputation: 958

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();

Upvotes: 2

Alberto Monteiro
Alberto Monteiro

Reputation: 6219

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"

Upvotes: 0

Related Questions