How to perform a select using a WHERE NOT EXISTS

Question

I'm using LINQ2SQL and I want to compare two tables and select all rows that are missing from one tables (based upon one of the column values).

In standard SQL I would write this as:

SELECT 
FirstName,
LastName,
RefId,
Email
FROM
Users_ActiveDirectory AS ADU
WHERE
NOT EXISTS
(
SELECT 
U.RefId
FROM
Users AS U
WHERE
U.RefID = ADU.RefId
)

However I'm not sure how to achieve the same result using LINQ2SQL?

Answer 1

Take a look at http://stackoverflow.com/questions/899090/linq-where-not-exists

Answer 2

Linq has a .Any() function which returns true if the sequence contains anything so something like...

from ADU in Users_ActiveDirectory
where !((from U in Users where U.RefID == ADU.RefId).Any())
select new
{
    ADU.FirstName, 
    ADU.LastName, 
    ADU.RefId, 
    ADU.Email 
}

Not tested, not sure if you need the extra brackets or if you'd have to do == false instead...