I have a classic many-to-many relationship, for example persons are members in clubs:


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.Clubs
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};

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