0

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)
AJGorcz
  • 1
  • You will need to Join the two tables in your query. It looks like you need left or inner join . but not sure from your question. Can you post the students and courses models? – IndieTech Solutions Sep 25 '15 at 22:13
  • Maybe you should consider a CourseStudent table in which you have a reference to a student and its associated course. Then it will be much easier to request against the new table and easily obtain the result you want. – christophe.chapron Sep 25 '15 at 22:22
  • Possible duplicate: http://stackoverflow.com/q/16798796/861716 – Gert Arnold Sep 25 '15 at 22:39
  • from s in Context.Students where Context.CoursesStudent.Any(cs => listOfCourseIds.contains(cs.courseId) && s.studentId = cs.studentId) – christophe.chapron Sep 25 '15 at 22:44
  • @christophe.chapron my database does have that relationship table (the one auto-created by EF for the many-to-many relationship). But considering it's the auto created one, I don't have a model for it. – AJGorcz Sep 26 '15 at 14:35
  • @GertArnold Thanks for that link, this looks like it might be what I'm looking for. Lots of info there so I'll have to dive in and play around with some stuff. – AJGorcz Sep 26 '15 at 14:37

1 Answers1

0

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;
mitbanip
  • 179
  • 3
  • 13