Last post Oct 26, 2016 02:51 PM by deanwood
Oct 25, 2016 02:32 PM|deanwood|LINK
for example, student table and teacher table are many to many relationship, in database, there is a middle table (let's assume TeacherStudent table) simple has StdentID and TeacherID.
After I added 3 tables (Student, Teacher, TeacherStudent) into the edmx file, then Visual studio only shows 2 tables (Student, Teacher), but in the Students class, it added a Teachers data member as a collection of Teachers objects.
My question is how to select the Students their teacher's first name is "John" ? I cannot use join in the code, since there's no middle table.
Note, this is a simple syntax question, I just don't know.
I know to get all students first name is John, I can do:
a = From c in _db.Students where c.FirstName="John"
but to get all students their teachers first name is John,
a = From c in _db.Students where c.Teachers.Where(x => x.FirstName="John")
or (in VB)
a = From c in _db.Students where c.Teachers.Where(Function(x) x.FirstName="John")
gives a syntax error: "Value of type Teacher cannot be converted to 'Boolean‘
Oct 26, 2016 04:23 AM|Cathy Zou|LINK
For your issue, I suggest you should Perform Left Outer Joins in the two tables:
For how to Perform Left Outer Joins, you could please refer to the following links:
Oct 26, 2016 02:51 PM|deanwood|LINK
Thanks Cathy. I tried Join at the very beginning, but entity framework delete the middle table, so that if I have to join like the following (but don't work)
From c in _db.Students join t in _db_Teachers on c equals t.Students where t.FirstName="John"
then get an error message: 'Equals' cannot compare a value of type 'Teacher' with a value of type 'ICollection(Of Teacher)'
cf: the link suggested:
from person in people
join pet in pets on person equals pet.Owner