Entity Framework: Queries involving many to many relationship tables

Entity framework handles tables participating in Many to Many relationship in a nice manner. If the junction table (sometimes called bridge table, association table, link table, etc) only consists of the foreign keys and no other columns, then that table is abstracted by EF and the two sides get a navigational property exposing a collection of the other side. If the junction table contains other fields as well (e.g. the table has its own primary key column), then that junction table is included in the model with many-to-one relationships with both the participating tables. This post will present some common LINQ queries involving the many to many relationship tables in both scenarios: when the junction table is abstracted by EF, and when it is exposed.

Consider the following scenario having a many to many relationship between Student and Course:

The generated Entity Model will look something like:

However, if the junction table contains any other field other than the two foreign keys, then EF will create a separate entity for it. E.g. lets assume that the StudentCourseEnrollment table has its own ID and some fields like EnrolledFrom and EnrolledTo, like:

The generated Entity Model for the above tables will look like:

Now, we will see how to write some typical LINQ queries involving the two tables participating in many to many relationship for both cases (with and without the junction table exposed in EF model):

Get students enrolled in a particular course

• For case 1, when there is no junction table, and we have the required CourseID to look for, we may write:

dc.Students.Where(s => s.Courses.Any(c => c.CourseID == courseID))

//or without the Any method
from s in dc.Students
from c in s.Courses
where c.CourseID == courseID
select s;

• For case 2, when the junction table is included in the entity model, we may need to write:

dc.Students.Where(s => s.StudentCourseEnrollments.Any(e => e.Course.CourseID == courseID)

//or without the any method
from s in dc.Students
from e in s.StudentCourseEnrollments
where e.Course.CourseID == courseID
select s;

If the foreign keys are also included in the model (applicable only for EF4), we can also directly refer to the CourseID property in the StudentCourseEnrollment table, like:

dc.Students.Where(s => s.StudentCourseEnrollments.Any(e => e.CourseID == courseID)

//or without the any method
from s in dc.Students
from e in s.StudentCourseEnrollments
where e.CourseID == courseID
select s;

In the above situations, the Any() method is translated to where exists() expression while the other SelectMany methods are translated to join expression.

However, an interesting twist for such queries is to start our query with the junction table and use navigational properties for filtering and selecting. This leaves us with a more compact query:

dc.StudentCourseEnrollments.Where(e => e.Course.CourseID == courseID).Select(e=>e.Student);

//the verbose query 
from e in s.StudentCourseEnrollments
where e.Course.CourseID == courseID
select e.Student;

Get students enrolled in any course from a list of courses

Notice that such query will use the Contains methodology that is only available with Entity Framework 4. For similar queries with EF1 (EF3.5) we can use the BuildContainsExpression() extension method described here.

• For case 1, when the bridge table is abstracted by EF, this can be written as:

int[,] courseIDs = new int[,] { 1,2,3 };
var students =
 from s in dc.Students
 from c in s.Courses
 where courseIDs.Contains(c.CourseID)
 select s;

//similarly, such query can be written with the any method
var students =
 from s in dc.Students
 where (
   from c in s.Courses
   where courseIDs.Contains(c.CourseID)
   select c
   ).Any()
 select s;

• For case 2, when the bridge table is included in the entity model, we may write:

int[,] courseIDs = new int[,] { 1,2,3 };
var students =
 from s in dc.Students
 from e in s.StudentCourseEnrollments
 where courseIDs.Contains(e.Course.CourseID)
 select s;

//starting with the bridge table
var students =
 from e in dc.StudentCourseEnrollments
 where courseIDs.Contains(e.Course.CourseID)
 select e.Student;

//with the any method
var students =
 from s in dc.Students
 where (
   from e in s.StudentCourseEnrollments
   where courseIDs.Contains(e.Course.CourseID)
   select e
   ).Any()
 select s;

Get students enrolled in ALL courses from a list of courses

• For case 1, when the bridge table is abstracted by EF, we can write the query as:

int[,] courseIDs = new int[,] { 1,2,3 };
var students =
 from s in dc.Students
 where
  (
   from c in s.Courses
   where courseIDs.Contains(c.CourseID)
   select c
  ).Count() == courseIDs.Count
 select s;

• For case 2, when the bridge table is included in the entity model, we may query as:

int[,] courseIDs = new int[,] { 1,2,3 };
var students =
 from s in dc.Students
 where
  (
   from e in s.StudentCourseEnrollments
   where courseIDs.Contains(e.Course.CourseID)
   select e
  ).Count() == courseIDs.Count
 select s;

Thats’s all. I will include some more queries in case I find interesting. Notice that same queries can be written for LINQ To SQL also. The only difference is that the bridge table is always included in the model, so always we need to refer to case 2 from this discussion.

About these ads