CODEWITHSUNDEEP
vb.netentity-frameworklinq-to-entitiesentity-framework-6
AJGorcz
AJGorcz

Reputation: 1

Entity Framework Many-to-Many Query

I'm struggling writing a query in Entity Framework that deals with a many to many relationship that I have set up. What I want to do is get the items from TableA that belong to a relationship with TableB and at the same time know from the results which relationship was a correct match.

For instance, if I'm using Students and Courses, I want to look for all the students that are in a set of courses and also return only those courses that matched. I very specifically want to start the query with Students, as this can easily be accomplished by just looking at the Courses navigation property to get the list of students.

What I want is a list of Students where each Student contains only the set of Courses in my query (NOT every course the student is taking).

Something like the below is close, I get the correct list of Students, but the navigation property for Courses shows all Courses, not the subset from my query. I want to avoid having to query again if possible, and just return the set of Students / Courses I need.

Dim listOfStudents = From s In Students
                                 From c In s.Courses
                                 Where listOfCourseIds.Contains(c.CourseId)

Upvotes: 0

Views: 118

Answers (1)

mitbanip
mitbanip

Reputation: 179

If there's no junction table between the two, then try:

from s in dc.Students
from c in s.Courses
where c.CourseID == courseID
select s;

If entity has a junction table between the two, try:

from s in dc.Students
from e in s.StudentsCourses
where e.Course.CourseID == courseID
select s;

Upvotes: 0

Related Questions