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

31 Responses to “Entity Framework: Queries involving many to many relationship tables”

  1. DotNetShoutout Says:

    Entity Framework: Queries involving many to many relationship tables « Mehroz’s Experiments…

    Thank you for submitting this cool story – Trackback from DotNetShoutout…

  2. Entity Framework: Queries involving many to many relationship … | Programming Blog Imagik.org Says:

    [...] the original post here: Entity Framework: Queries involving many to many relationship … No [...]

  3. advanceddotnettutorial Says:

    To learn .net, C#, Java, J2EE, Silverlight, Visual Basic, Asp .net with advanced concepts, you can visit http://advanceddotnettutorial.blogspot.com

  4. Abubakar Says:

    this is great ,i love it!

  5. Greg P Says:

    What are the “dc” and “s” objects above. I was thinking dc was your context but then you use S in the next line. I’m sure this is a newby question but I’m struggling with this and using it in MVC2.

    Thanks,
    Greg

  6. Nick Says:

    Nice write up :)
    But how do you save to such a relationship?

  7. Dave Says:

    I’m trying to implement exactly your 1st scenario I have 2 tables that are linked with a junction table. But when I run the project I get all employees returned instead of just from the single department.
    var employees = (from employee in projectDB.Employees
    from department in projectDB.Departments
    where department.DepartmentPK == 2
    select employee);
    return employees;
    Any idea why this would not work? This is in VS 2010 express using the repository method similar to the nerdDiner sample project.

    Thanks.

  8. Dave Says:

    Nevermind, I found my mistake:
    it is “from c in s.Courses” not “from c in dc.Courses”
    or in my case: “department in employee.Departments”

    Thanks

  9. Mike Says:

    I’m trying to implement your first scenario but in reverse: I’m looking for all the students NOT enrolled in a particular class.
    I have everything written up exactly the same except:
    where c.CourseID != courseID
    yet it pulls back no resuts. Any help?

  10. Cleyton Says:

    And how can I update this?

  11. Mike Says:

    Cleyton
    you use:
    De.SaveChanges();
    where De is whatever you named the database entity

  12. Syed Mehroz Alam Says:

    Hi Mike,

    where c.CourseID != courseID doesn’t make any sense. You need to query like this(Notice the negation ‘!’ is on Any:

    from s in dc.Students
    where !s.StudentCourseEnrollments.Any(e=>e.CourseID == courseID)
    select s
    
    //or
    dc.Students.Where(s => !s.StudentCourseEnrollments.Any(e => e.CourseID == courseID)
    

    Alternatively, you can query your database for students enrolled in a particular course and then use the Except method: http://msdn.microsoft.com/en-us/library/bb907956.aspx

  13. Syed Mehroz Alam Says:

    Hi Cleyton,

    For updating relationships, you might need to remove and then add a new relationship. Have a look here:
    http://stackoverflow.com/questions/1175251/entity-framework-many-to-many-crud

    Also, for inserting such relationships, have a look at this detailed article by Julie Lerman: http://thedatafarm.com/blog/data-access/inserting-many-to-many-relationships-in-ef-with-or-without-a-join-entity/

  14. Olivia Says:

    I am trying something similar to this and cannot figure it out. I will show my lack of knowledge first and say I do not know what Entity Framework is. Looks maybe like a newer version of Access? Anyhow I have 2 simple main tables. 1st is Clients Table with CID(PK), CFirstName, CLastName. 2nd is Skills Table with SkillID(PK) and Skills. The 3rd table is a join table with CID & SkillID. It sounded simple to me in the beginning, but all I want is to return a query/report to tell me which clients have at least X number of selected skills. I work in public service and we need a way to match clients to jobs that come in. So an employer may call and ask for people who have experience with welding, carpentry, and plumbing. I need to return individuals who have at least those 3 skills. Or if the employer just wants welding, I need to pull individuals who at least have the welding skill. Everything I have tried to this point returns results of individuals who match any of the skills and the last thing i tried was a listbox that ended up making me select all of the skills that a client had before they would come up. so if someone had 3 skills and I only asked for one they wouldnt come up.

  15. Wikki Pa Says:

    Hi,

    I have exactly the same scenario and following Entities:

    Customer and Site are two tables where joining table is CustomerSites. All i want is to fetch the list of site for a particular customer which are not yet joined into the CustomerSites entity.

    as well as, I want to fetch all the sites which are not yet mapped to a customer in customersites.

    regards,
    WK

  16. Eyad Says:

    Thank You :)

  17. Code First – Many To Many | GSmith Says:

    [...] addition this is a great post about querying many to many relationships in Entity Framework This entry was posted in Uncategorized. Bookmark the [...]

  18. Robert J. Bullock Says:

    That is like one of the clearest explanations of this I’ve seen. Thanks so much!

  19. Jules Bartow Says:

    Syed Mehroz Alam,

    You’re the best with this excellent blog. You make Selects easy.
    However, where’s the rest?

    You know… the other 3/4, i.e. the CUD part of CRUD?
    • Insert /Create/Add?
    • Delete/Remove?
    • Update/Modify?

    …and Left Outer Join for Many-to-Many?

  20. Irfan Akram Dar Says:

    Assalamu-alaikum!

    Jazakallah..for the scenerio 1..
    I thought first that it is some bug in the Model not generating the respective Entity in Text Template and that in the Model itself !

  21. Relazione molti a molti e code first « Mino Web Says:

    [...] L’ho letto qua! Posted in dotNet.Tagged code first, dotNet, entities, entity framework, linq. [...]

  22. sampath lokuge Says:

    Superb Article about EF M:M.
    Keep it up.

  23. jnegron.wordpress.com Says:

    Thank you for every other fantastic article. The place else could anyone
    get that type of information in such an ideal way of writing?
    I’ve a presentation subsequent week, and I am at the search for such info.

  24. jowehead.com Says:

    Hmm is anyone else encountering problems with the images on this blog
    loading? I’m trying to determine if its a problem on my end or if it’s the blog.

    Any responses would be greatly appreciated.

  25. Jules Bartow Says:

    4 images + avatars and code is showing up in Chrome fine.

  26. how to save relationship Says:

    I must show appreciation to this writer just for rescuing me from this type of trouble.
    Because of looking throughout the online
    world and meeting views which are not pleasant, I figured my entire life was over.

    Being alive minus the answers to the difficulties you have
    fixed through the guideline is a serious case,
    and the kind that would have badly affected my career if I had not noticed your web page.
    The competence and kindness in taking care of almost everything was precious.

    I don’t know what I would’ve done if I had not discovered such a solution like this.
    It’s possible to at this point look ahead to my future.
    Thank you so much for your impressive and effective help.
    I won’t hesitate to suggest your web sites to anyone who
    requires direction about this topic.

  27. Abdullah Khalid Says:

    Thank you so much for this. I’ve been having a lot of trouble Entity Framework, specifically with that association table with payload/metadata.

    And while I thank you for your examples on how to select data,. do you have examples on how to go about basic CRUD functionality with the association table.

  28. CB Says:

    You may just have saved my job with this post.. :) Thank you..

  29. Rolando Ernesto Valverde Chaverri Says:

    Great article. Precise and clear. Thanks a lot.

  30. Simon Shirley Says:

    Thanks for this! I’ve been going round in circles searching Google as my problem would leave me with Lists of EntityCollections. Fixed my issue with your first example (unfamiliarity with the Any() function).


Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Follow

Get every new post delivered to your Inbox.

Join 54 other followers

%d bloggers like this: