Print Page | Close Window

Using subqueries with 'NOT IN'

Printed From: IdeaBlade
Category: DevForce
Forum Name: DevForce Classic
Forum Discription: For .NET 2.0
URL: http://www.ideablade.com/forum/forum_posts.asp?TID=1025
Printed Date: 28-Mar-2024 at 8:43am


Topic: Using subqueries with 'NOT IN'
Posted By: JonF
Subject: Using subqueries with 'NOT IN'
Date 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!
 

 




Replies:
Posted By: kimj
Date Posted: 18-Dec-2008 at 2:09pm
See answer in thread http://www.ideablade.com/forum/forum_posts.asp?TID=1026. - http://www.ideablade.com/forum/forum_posts.asp?TID=1026.



Print Page | Close Window