Query link table in Many to Many Relationship

2 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.
  3. Kosta Hristov
    Admin
    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)));

    or


    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 >>

Back to Top