CODEWITHSUNDEEP
c#linq
InTheWorldOfCodingApplications
InTheWorldOfCodingApplications

Reputation: 2616

How to query Link table based data in LINQ

Table 1: Student

StudentID, StudentName

Table 2: Course

CourseID, CourseName

Table 3: Group

GroupID, GroupName

Table 4: StudentCourseGroup

StudentID, CourseID, GroupID

I want all the students who belongs to 'xyz' course in following format

Class MyStudent

    string StudentName
    String [] Groups

Each student can be member of one or more groups and I need to populate "Class MyClass" in my LINQ query so that it holds StudentName and List of Groups in each object.

Can you please suggest a LINQ query that can do so.

Upvotes: 2

Views: 1576

Answers (1)

Sergey Berezovskiy
Sergey Berezovskiy

Reputation: 236208

var query = from s in db.Student
            join scg in db.StudentCourseGroup on s.StudentID equals scg.StudentID
            join c in db.Course on scg.CourseID equals c.CourseID
            join g in db.Group on scg.GroupID equals g.GroupID
            where c.CourseName == "xyz"
            select new { s, g } into x
            group x by x.s into studentGroups
            select new MyStudent {
                StudentName = studentGroups.Key.StudentName,
                Groups = studentGroups.Select(sg => sg.g.GroupName)
            };

Upvotes: 2

Related Questions