New Posts New Posts RSS Feed: Using subqueries with 'NOT IN'
  FAQ FAQ  Forum Search   Calendar   Register Register  Login Login

Using subqueries with 'NOT IN'

 Post Reply Post Reply
Author
JonF View Drop Down
Newbie
Newbie


Joined: 17-Dec-2008
Posts: 3
Post Options Post Options   Quote JonF Quote  Post ReplyReply Direct Link To This Post Topic: Using subqueries with 'NOT IN'
    Posted: 17-Dec-2008 at 3:30pm
(apologies for crossposting this to a couple forums... wasn't sure which forum would be the best)
 
Hi there,
 
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!
 

 

Back to Top
kimj View Drop Down
IdeaBlade
IdeaBlade
Avatar

Joined: 09-May-2007
Posts: 1390
Post Options Post Options   Quote kimj Quote  Post ReplyReply Direct Link To This Post Posted: 18-Dec-2008 at 2:09pm
Back to Top
 Post Reply Post Reply

Forum Jump Forum Permissions View Drop Down