LINQ subquery question

Can anybody tell me how I would get the records in the first statement that are not in the second statement (see below)?

from or in TblOrganisations
where or.OrgType == 2
select or.PkOrgID

Second query:

from o in TblOrganisations
 join m in LuMetricSites
 on o.PkOrgID equals m.FkSiteID
 orderby开发者_C百科 m.SiteOrder
 select o.PkOrgID

If you only need the IDs then Except should do the trick:

var inFirstButNotInSecond = first.Except(second);

Note that Except treats the two sequences as sets. This means that any duplicate elements in first won't be included in the results. I suspect that this won't be a problem since the name PkOrgID suggests a unique ID of some kind.

(See the documentation for Enumerable.Except and Queryable.Except for more info.)

Do you need the whole records, or just the IDs? The IDs are easy...

var ids = firstQuery.Except(secondQuery);

EDIT: Okay, if you can't do that, you'll need something like:

var secondQuery = ...; // As you've already got it

var query = from or in TblOrganisations
            where or.OrgType == 2
            where !secondQuery.Contains(or.PkOrgID)
            select ...;

Check the SQL it produces, but I think it should do the right thing. Note that there's no point in performing any ordering in the second query - or even the join against TblOrganisations. In other words, you could use:

var query = from or in TblOrganisations
            where or.OrgType == 2
            where !LuMetricSites.Select(m => m.FkSiteID).Contains(or.PkOrgID)
            select ...;

Use Except:

var filtered = first.Except(second);