I can do this in other ways, but am looking for the "LINQ" way of doing this:
Table1: People (PersonId,SchoolId)Table2: AssignedPeople (PersonId,SchoolId,RoomId)
I want to select the people in Table1 within a particular SchoolId that are not in the assignedPeople table (in a particular Room).
The SQL is:
select p.* from people p left outer join assignedpeople ap on p.PersonId=ap.Person and p.SchoolId=ap.SchoolIdand ap.RoomId=@roomId where p.SchoolId=@SchoolId and ap.PersonId is null
I tried this LINQ (VB,NET):dim SchoolID as guid=...dim RoomID as guid=...
(from p in dc.people where p.SchoolId=SchoolIdgroup join ap in dc.AssignedPeopleon p.PersonId equals ap.PersonId and p.SchoolId equals ap.SchoolId into groupfrom g in group.DefaultIfEmptywhere g.RoomID = nothing)
but I can't get the ap.RoomId=@roomId part into the right place, should be something like:group join (from ap in dc.AssignedPeople where ap.RoomId=RoomId)
Any help would be appreciated,Reuven
View Complete Post