(apologies for crossposting this to a couple forums... wasn't sure which forum would be the best)
Was wondering if anyone could provide some insight into using subqueries with the 'NOT' operator.
I have two parent tables, lets call them TABLE_A and TABLE_B. They are linked to each other through a simple linking table called TABLE_AB whose records are simply TABLE_A.primarykey and TABLE_B.primarykey.
Now my issue is that I would like to get 2 lists of entities from TABLE_B. One list should be of all TABLE_B entities where the TABLE_B entity is linked to a given record in TABLE_A. This one is pretty straightforward.....
EntityQuery TABLE_B_query = new EntityQuery(typeof(TABLE_B));
EntitySubquery subquery = TABLE_Bquery.AddSubquery(EntityRelations.TABLE_B_to_TABLE_AB.ToChild);
subquery.AddClause(TABLE_AB.TABLE_A_primarykeyEntityColumn, EntityQueryOp.EQ, some_primarykey_from_TABLE_A);
the above would return me a list of all entities from TABLE_B that are joined to a given record from TABLE_A.
Now where I am having an issue is with with trying to get a list of entities from TABLE_B that are *NOT* joined to a given record from table A (essentially the inverse list). To write this in sql would be easy:
select * from TABLE_B where TABLE_B.TABLE_B_primarykey NOT IN (select TABLE_B_primarykey FROM TABLE_AB where TABLE_A_primarykey = somevalue)
But I'm having a tough time figuring out how to write this in an entityquery format. I could of course just write a passthrough query but unfortunately passthrough queries go straight to the database and I really need to be querying against the current persistencemanagers cache since it will have current updates (that have not been saved yet obviously).
Thanks in advance for any help!