Query link table in Many to Many Relationship

Thread is closed for posting
3 posts, 0 answers
  1. Ta Duc
    Ta Duc avatar
    3 posts
    Member since:
    Aug 2009

    Posted 19 Sep 2011 Link to this post

    Currently I am having a Many to Many table constructed as ff:

    Employee -> EmpTag <- Tag

    is it possible to query the EmpTag provide I have a list of ID From Tags? The end result is to get all the Employee that have the Tag indicated in the List<int> tag.

    I tried to access my DataContext and there was no table called EmpTag listed.

    Thank you very much
  2. Kosta Hristov
    Kosta Hristov avatar
    13 posts

    Posted 22 Sep 2011 Link to this post

    Hello Ta Duc,

    If the junction table (in your case - EmpTag) only consists of two foreign keys, instead of adding it to the model, OpenAccess will include two navigational properties each exposing a collection to both of the classes, for example :

    In your scenario, In order to get all employees by a given list of tag ids, you can use one of the following LINQ queries :

    var employees = model.Employees.Where(e => e.Tags.Any(t => tagsIDList.Contains(t.TagID)));


    var employees = (from e
    in model.Employees from t in e.Tags where tagsIDList.Contains(t.TagID) select e);

    where model is the OpenAccess EntitiesModel, and tagsIDList is the list with tags IDs.

    As an alternative, It is also possible to visualize the EmpTag table and access it directly. In order to do that perform the following steps :
    1. Remove the association between the two domain classes. (right-click on the association -> Delete )
    2. Open the Model Schema Explorer , locate the EmpTag table and select it.
    3. Press F4 to open the Properties pane and set the IsJoinTable property to False
    4. Simply drag the table next to the other two tables.

    The result will be :

    This way the EmpTag table will be directly accessable from the OpenAccess context.

    Hope this helps.

    Best Regards,
    Kosta Hristov
    the Telerik team

    Thank you for being the most amazing .NET community! Your unfailing support is what helps us charge forward! We'd appreciate your vote for Telerik in this year's SQL Server Community Awards. We are competing in TWO categories and every vote counts! VOTE for Telerik NOW >>

  3. carlos
    carlos avatar
    1 posts
    Member since:
    Jan 2018

    Posted 16 Jan 2018 in reply to Kosta Hristov Link to this post

    How can i achive this with Code-First : 

    i did it this way tell me what i’m doing wrong.
    1.- i created the entities like this:
    class Student
    public int StudentId { get; set; }
    public string Name { get; set; }
    public ICollection Courses {get;set;} //or public List Courses {get;set;}
    class Course
    public int CourseId { get; set; }
    public string Name { get; set; }
    public ICollection Students {get;set;} //or public ListStudents {get;set;}
    2.- I run the migration an update the database.
    3.- Then when i look on Mangemet Studio everything is okay we have the the three tables “Students”,Courses”,”StudentsCourses” the last with just the two FK’s that are needed.
    4.- If we make a query Let’s say in the students table to get all of the students and while debbuging we take a Watch on the obj’s that we are geting from the query the course collection of each of them is null.
    so what is wrong with this.
    I think is because is not being mapped to the table or something. but not sure 

Back to Top