Hi,

I am still new to LLBLGen and I was not able to find the solution to my problem in the forum so far.

I want to do a query like:

SELECT Table1_ID, ... FROM Table1
INNER JOIN Table2 ON Table1.Table1_ID = Table2.Table1_ID
...
WHERE
  Table1.Table1_ID IN (a, list, of, IDs) 
  Table2.DateFrom = (
    SELECT MAX(Table2.DateFrom) 
    WHERE Table2.Table1_ID = Table1.Table1_ID)

Is this possible?

Regards, Jochen

Jochen, Welcome to LLBLGen.

The example SQL should be possible. There are some good examples in the maul, but I suggest you have a quick look at "Best practises - How do I ... ?".

Please come back if this is not sufficient.

Please check out the FieldCompareSetPredicate in the help, and Field Expressions and Aggregates.

Well, I tried with FieldCompareSetPredicate, but no avail. I seem to be stuck.

I keep trying ...

If the first where condition compares against a list of pre-defined IDs (an array of IDs) then use FieldCompareRangePredicate

If you are going to select the list of IDs in a sub-query then use FieldCompareSetPredicate

Your Predicate Filter should look like the following:

SELECT Table1_ID, ... FROM Table1 INNER JOIN Table2 ON Table1.Table1_ID = Table2.Table1_ID ... WHERE Table1.Table1_ID IN (a, list, of, IDs) Table2.DateFrom = ( SELECT MAX(Table2.DateFrom) WHERE Table2.Table1_ID = Table1.Table1_ID)

// C#
IPredicateExpression filter = new PredicateExpression();

// First Predicate
int[] values = new int[3] {1, 2, 5};
filter.Add(new FieldCompareRangePredicate(
    Table1Fields.Table1_ID, null, values));
// which is equal to:
//filter.Add(Table1Fields.Table1_ID == values);

// Second Predicate
EntityField2 FiledMax = EntityFieldFactory.Create(Table2FieldIndex.DateFrom);
FiledMax.AggregateFunctionToApply = AggregateFunction.Max

filter.Add(new FieldCompareSetPredicate(EntityFieldFactory.Create(Table2FieldIndex.DateFrom), null, FiledMax , null, SetOperator.In, 
PredicateFactory.CompareExpression(Table2FieldIndex.Table1_ID, 
    ComparisonOperator.Equal,
    new Expression(Table1FieldIndex.Table1_ID))
, false));