Using Linq in many-to-many select scenarios

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

Relationship diagram
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:
EF diagram
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.

Pages in this section

Categories

ASP.Net | Community | Development | IIS | IT Pro | Security | SQL (Server) | Tools | Web | Work on the road | Windows