Query link table in Many to Many Relationship

[Ta Duc]

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

[Kosta Hristov]

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

[carlos]

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