I am a new user to LLBLGen Pro. I have version 2.0.0.0 (May 23rd, 2007).

I am trying to create a predicate that will accomplish the following but have not been able to find a way to relate a table to itself using a partial key. The table in question has a two-part primary key in which the second field is an effective date. I wish to retrieve the most recent effective date for the first key field value.

The primary key consists of: 1) InternalId, 2) Effective Date

The SQL statement that I currently use is:

SELECT * FROM thistable tbl1 WHERE tbl1.EffectiveDate = (SELECT MAX(EffectiveDate) FROM thistable tbl2 WHERE tbl1.InternalId = tbl2.InternalId)

My current workaround is to use a View that contains the above code. I was unable to figure out how to reference the field from tbl1 in the subquery.

Thanks for your help.

Hi Scott. You can achieve that with FieldCompareSetPredicate. Here an example:

Statemen: I want all Customers with MOST RECENT DATE for EACH CUSTOMER-EMPLOYEE COM

// collection to fill
    EntityCollection<OrdersEntity> orders = new EntityCollection<OrdersEntity>(new OrdersEntityFactory());

// general filter
IRelationPredicateBucket filter = new RelationPredicateBucket();


// -- SETUP THE SUBQUERY --

// CustomerID = OrdersMaxDate.CustomerID
IPredicate subQueryFilter1 = 
    new FieldCompareExpressionPredicate(
        OrdersFields.CustomerId, null,
        ComparisonOperator.Equal, 
        new Expression(OrdersFields.CustomerId.SetObjectAlias("OrdersMaxDate")) );

// EmployeeID = OrdersMaxDate.EmployeeID
IPredicate subQueryFilter2 =
    new FieldCompareExpressionPredicate(
        OrdersFields.EmployeeId, null,
        ComparisonOperator.Equal,
        new Expression(OrdersFields.EmployeeId.SetObjectAlias("OrdersMaxDate")));


// CustomerID == OrdersMaxDate.CustomerID and EmployeeID == OrdersMaxDate.EmployeeID
IPredicateExpression subQueryFilters = new PredicateExpression();
subQueryFilters.Add(subQueryFilter1);
subQueryFilters.AddWithAnd(subQueryFilter2);


/// OrderDate = ( MAX(OrderDate) FROM Orders ALIAS OrdersMaxDate 
/// WHERE CustomerID == OrdersMaxDate.CustomerID and EmployeeID == OrdersMaxDate.EmployeeID
filter.PredicateExpression.Add(
    new FieldCompareSetPredicate( 
        OrdersFields.OrderDate, null,
        OrdersFields.OrderDate.SetAggregateFunction(AggregateFunction.Max).SetObjectAlias("OrdersMaxDate"), null,
        SetOperator.In,
       subQueryFilters));
            
// data retrieval
using (DataAccessAdapter adapter = new DataAccessAdapter())
{
    adapter.FetchEntityCollection(orders, filter);              
}

check out the FieldCompareSetPredicate (LLBLGen Pro - Generated Code - Predicate System). you will need to set the Aggregate Function, and Alias on your EntityField since your referencing the same table.

... daelmo beat me to it

Thanks for the responses. I'm new to this and had not seen the 'alias' functionality.