in the UI for a person I need to display the clubs the person is a member of and all the
other clubs to allow the person to join another club.
In T-SQL I would do this like this (assume 3 is my personId)
SELECT Name FROM Clubs
WHERE ClubId IN
(SELECT ClubId FROM Members WHERE PersonId = 3)
SELECT Name FROM Clubs
WHERE ClubId NOT IN
(SELECT ClubId FROM Members WHERE PersonId = 3)
then I moved to Linq To SQL
from c in db.Clubs
join m in db.Members
on c.ClubId equals m.ClubId
where m.PersonId == 3
select new { Name = c.Name };
from clubs in db.Clubs
where !(
from members in db.Members
where members.PersonId == 3
select members.ClubId
).Contains(clubs.ClubId)
select new { Name = clubs.Name };
On to Linq to Entities, here the junction table 'members' is hidden
and I have use the persons collection on Clubs:
from c in context.ClubsThe generated SQL for LinqToSql and LinqToEntities is similar but both are quite different from the hand written SQL. However the execution plans for all three are nearly the same.
from p in c.Persons
where p.PersonId == 3
select new { Name = c.Name };
from c in context.Clubs
where !(from n in context.Clubs
from p in n.Persons
where p.PersonId == 3
select n.ClubId
).Contains(c.ClubId)
select new {Name = c.Name};