We are using GenPro to get Oracle data, and there are some complex business rules used to determine what records are returned. I'd like to use a predicate bucket in our ORM tier to filter records, but I am not sure if GenPro supports something like a subquery with aggregates, like this:

        
SELECT PS_CUST_ADDRESS.EFFDT, 
PS_CUST_ADDRESS.CUST_ID,
PS_CUST_ADDRESS.SET_ID,
PS_CUST_ADDRESS.ADDRESS_SEQ_NUM

FROM PS_CUST_ADDRESS
WHERE 
               PS_CUST_ADDRESS.EFFDT = 
                (
                      SELECT    MAX(ADR2.EFFDT) 
                      FROM    PS_CUST_ADDRESS ADR2 
                      WHERE  ADR2.CUST_ID = PS_CUST_ADDRESS.CUST_ID
                                  AND ADR2.SETID = PS_CUST_ADDRESS.SETID
                                  AND ADR2.ADDRESS_SEQ_NUM = PS_CUST_ADDRESS.ADDRESS_SEQ_NUM
                                  AND ADR2.EFFDT <= SYSDATE
                )

Can this even be done? Our alternative is to use views that have this logic, but I'd rather use entities.

I am using LLBL 3.5 against Oracle with MS Oracle, VS2010, C#, and .NET 4.0.

Yes, it's possible, you should use a ScalarQueryExpression in the filter. Here is an example, I want to fetch orders where their freight is the same to the max freight registered for the customer in the involved order:

// define the scalar field and filter
var scalarField = new EntityField2("MaxFreightFromCust", 
    new ScalarQueryExpression(OrderFields.Freight.SetObjectAlias("O2")
        .SetAggregateFunction(AggregateFunction.Max),
        (OrderFields.CustomerId.SetObjectAlias("O2") == OrderFields.CustomerId)));

var filter = new RelationPredicateBucket(OrderFields.Freight == scalarField);

// fetch
var orders = new EntityCollection<OrderEntity>();
using (var adapter = new DataAccessAdapter())
{
    adapter.FetchEntityCollection(orders, filter);
}

Just apply the same concept to your scenario. The last parameter of the ScalarQueryExpression is the predicate expression of the query (the WHERE clause in your subquery), you can add many clauses to it, you can build it outside and pass the predicate to the constructor. Example:

var subqueryFilter = new PredicateExpression();
subqueryFilter.Add(OrderFields.CustomerId.SetObjectAlias("O2") == OrderFields.CustomerId);
subqueryFilter.Add(OrderFields.ShipDate.SetObjectAlias("O2") < DateTime.Now);

var scalarField = new EntityField2("MaxFreightFromCust", 
    new ScalarQueryExpression(OrderFields.Freight.SetObjectAlias("O2")
        .SetAggregateFunction(AggregateFunction.Max),
        subqueryFilter));