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

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.