LINQ: Except and Intersect

As of this writing, I’m currently involved in developing a production appliction making use of LINQ, Orcas Beta 2 (Visual Studio 2008), ADO.NET and the .NET 3.5 framework.  I just wanted to put down my recent experience using Except, which is akin to the not in operation in SQL statements.

The task is simple enough.  I have a database containing class/course schedules and a many-to-many relationship table called Registrations that has a uniqueness constraint across two foreign keys, one to the Course table and the other to the Registrant table.  I wanted to be able to present two sets of collections.  One, given a particular course, be able to get the collection of registered Registrants; the second, is a collection of Registrants that are not registered for the course.

In SQL, we’d have something like this:

-- Get registered Registrants
SELECT r.* FROM Registrant AS r JOIN Registrations AS reg ON r.registrantId = reg.registrantId
WHERE reg.courseId = @courseId;

-- Registrants not registered for the course
SELECT * FROM Registrant registrantId NOT IN
(SELECT r.registrantId FROM Registrant AS r JOIN Registrations AS reg ON r.registrantId = reg.registrantId
WHERE reg.courseId = @courseId);

-- Alternate query without using NOT IN
SELECT r.* FROM Registrant AS r LEFT JOIN Registrations AS reg ON r.registrantId = reg.registrantId AND reg.registrantId IS NULL;


With LINQ, the one point I found with Except and Intercept is that the comparison is done at the object level, unlike in SQL where we include/exclude based on the value of a particular property/column.


var regList = (from r1 in Registrant
select r1).Except(from r2 in Registrant
join reg in Registrations on r2.registrantId equals reg.registrantId
where reg.courseId == (new Guid(courseId))
select r2);
/*
Note that in SQL we just needed to specify the registrantId's we want to exclude while with LINQ we need to compare apples to apples.
*/