DevForce Community Forum : Using subqueries with 'NOT IN' http://www.ideablade.com/forum/ This is an XML content feed of; DevForce Community Forum : DevForce Classic : Using subqueries with 'NOT IN' Tue, 14 Apr 2026 09:41:56 -700 Thu, 18 Dec 2008 14:09:48 -700 http://blogs.law.harvard.edu/tech/rss Web Wiz Forums 9.69 360 www.ideablade.com/forum/RSS_post_feed.asp?TID=1025 DevForce Community Forum http://www.ideablade.com/forum/forum_images/IdeaBlade_logo_tm.png http://www.ideablade.com/forum/ Using subqueries with 'NOT IN' : See answer in thread http://ww... http://www.ideablade.com/forum/forum_posts.asp?TID=1025&PID=3721#3721 Author: kimj
Subject: 1025
Posted: 18-Dec-2008 at 2:09pm

See answer in thread http://www.ideablade.com/forum/forum_posts.asp?TID=1026.
]]> Thu, 18 Dec 2008 14:09:48 -700 http://www.ideablade.com/forum/forum_posts.asp?TID=1025&PID=3721#3721 Using subqueries with 'NOT IN' : (apologies for crossposting... http://www.ideablade.com/forum/forum_posts.asp?TID=1025&PID=3713#3713 Author: JonF
Subject: 1025
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!
 

 

]]> Wed, 17 Dec 2008 15:30:25 -700 http://www.ideablade.com/forum/forum_posts.asp?TID=1025&PID=3713#3713