Home
Help
Register
Log in

Search

 
   Active Threads  

You are here: Home > LLBLGen Pro > LLBLGen Pro Runtime Framework> LLBLGEN Equivalent to T-SQL IN CLAUSE
 

Pages: 1
LLBLGen Pro Runtime Framework
LLBLGEN Equivalent to T-SQL IN CLAUSE
Page:1/1 

  Print all messages in this thread  
Poster Message
BeauButton
User



Location:
New Orleans
Joined on:
13-Oct-2004 22:17:44
Posted:
5 posts
# Posted on: 29-Sep-2005 23:52:57.  
I need to retreive an entitycollection from a table and utilize some sort of functionality that is equivalent to an in clause.

For example. I need all Orders from the Orders table that are not in the SomeOrder table. The relationship is on OrderID.

In T-SQL i could do Select * from Orders WHERE Orders.OrderID NOT IN (SELECT OrderID FROM SomeOrder).

How can i do this with LLBLGen.

Thanks in advance.

-Beau
[BeauLButton()]  Top
Drewes
User



Location:
Columbus, Ohio
Joined on:
18-Aug-2003 15:25:34
Posted:
67 posts
# Posted on: 30-Sep-2005 03:43:49.  
In the documentation you can find this:
**************************
FieldCompareSetPredicate
Description
compares the entity field specified with the set of values defined by the subquery elements, using the SetOperator specified. The FieldCompareSetPredicate is the predicate you'd like to use when you want to compare a field's value with a range of values retrieved from another table (or the same table) using a subquery. Due to the big number of constructor overloads, there is no PredicateFactor shortcut, as it would otherwise make the PredicateFactory class to become extremely large in bigger LLBLGen Pro projects (100+ entities).

FieldCompareSetPredicates also allows you to define EXISTS () queries. It is then not necessary to specify an IEntityField object with the predicate's constructor (specify null / nothing) as it is ignored when building the SQL. Keep in mind that EXISTS() queries are semantically the same as IN queries and IN queries are often simpler to formulate.

The FieldCompareSetPredicate supports advanced comparison operators like ANY, ALL and combinations of these with comparison operators like Equal (=) or GreaterThan (>). If the set is just 1 value in size (because you've specified a limit on the number of rows to return), it's wise to use the Equal operator instead of the IN operator as most databases will be rather slow with IN and just 1 value compared to the Equal operator.
SQL equivalent examples Field IN (SELECT OtherField FROM OtherTable WHERE Foo=2)
EXISTS (SELECT * FROM OtherTable)
Operators All SetOperator operators: In, Exists, Equal, EqualAny, EqualAll, LessEqual, LessEqualAny, LessEqualAll, LesserThan, LesserThanAny, LesserThanAll, GreaterEqual, GreaterEqualAny, GreaterEqualAll, GreaterThan, GreaterThanAny, GreaterThanAll, NotEqual, NotEqualAny, NotEqualAll
************************

Looks like this might be what your are looking for.


  Top
Pages: 1  


Powered by HnD ©2002-2007 Solutions Design
HnD uses LLBLGen Pro

Version: 2.1.12172008 Final.