linq not in query

Question

I'm lost in linq-land, trying to turn this SQL (which give the results I need) into something that works in linq / C#. The result will end up in a SelectList.

select Agencies.AgencyName, Agencies.AgencyId
from Agencies
where Agencies.Active = 1 and Agencies.AgencyId not in (
  select distinct Agencies.AgencyId
  from Agencies, AgenciesDonors
  where AgenciesDonors.AgencyId = Agencies.AgencyId and
        AgenciesDonors.DonorId = '73B29E01-8BF0-4EC9-80CA-089BA341E93D')

The Guid value will be passed in each time.

Help very much appreciated after a long day of getting nowhere.

EDIT: I should have said I'm working in an MVC 1.0 / EF context. Not sure how that changes the initial answers.

Answer 1

This should do the trick

var query = from c in dc.Agencies 
            where c.agencies == 1 && 
                  !(from o in dc.AgenciesDonors 
                    where o.DonorId = myDonorId 
            select o.AgencyId).Contains(c.AgencyId) 
            select c;

Linq to SQL will turn the not contains into a not exists which usually has better performance than a not in SQL statement.

Answer 2

Unless I'm missing something, bringing in the Agencies table into the subquery is unnecessary. So something like this should work:

from a in dataContext.Agencies
where a.Active == 1 &&
      !(from inner in dataContext.AgenciesDonors
        where inner.DonorId == donorID
        select inner.AgencyId).Contains(a.AgencyId)
select a

Answer 3

var query = from a in dc.Agencies 
            let ad = from o in dc.AgenciesDonors 
                     where o.DonorId = myDonorId 
                     select o.Agencies.AgencyId
            where a.Active == 1 && !ad.Contains(a.AgencyId) 
            select c;

I find this to be a neater syntax, since it negates the need to nest a join statement on AgenciesDonors to Agencies to get the AgencyId, this must execute on a DataContext (dc), since o.Agencies.AgencyId in the "let" statement will only work when run as IQueryable