c# - Implement "not in" (aka "not exists") logic in LINQ -

setup

1. i have 2 list<t>'s.
2. the data un-normalized , different sources explains convolution in desired logic
3. an informal compound key in data fielda, fieldb, fieldc.
4. the "fields" strings - reference types - values null. want drop records may matching on null. null references in c# match, in sql not. adding !string.isnullorempty() easy enough.
5. this not question db design or relational algebra.
6. i have other logic covers other criteria. not suggest reducing logic shown such might broaden result set. see # 5 above.

the problem

i want find records in lista not in listb based on informal key. want further refine lista results based on partial key match.

the sql version of problem:

select      lista.fielda, lista.fieldb, matching.fieldc    lista  left join listb keylist on        lista.fielda = keylist.fielda ,       lista.fieldb = keylist.fieldb ,       lista.fieldc = keylist.fieldc  inner join listb matching on       lista.fielda = matching.fielda ,       lista.fieldb = matching.fieldb       keylist.fielda null   

sql linq ( case 7 - filter data using in , not in clause)

note: in , not in use same function in linq query, use ! (not) symbol it. here graphical representation:

you use, where <list>.contains( <item> )

var myproducts = p in db.products                  productlist.contains(p.productid)                  select p; 

or can have list predefined such:

var ids = {1, 2, 3};  var query = item in context.items             ids.contains( item.id )             select item; 

for 'not' case, add '!' operator before 'contains' statement.