Hi,

I have the following query

Select Column_1 from TableOne where column_2 = 12345 and column_3 in (Select (min(column_3) - 1) as column_3 from TableOne where Column_4 = 'ABCD' and column_2 = 12345);

I used AggregateFunction.Min and FieldCompareSetPredicate.

I am trying to do a Min(column_3) - 1 how is this achieved?

You need to use an Expression with the field. e.g. new Expression(OrderDetailFields.UnitPrice.SetAggregateFunction(AggregateFunction.Min), ExOp.Sub, 1)

/*
select * from [Order Details] od
where UnitPrice in (Select min(UnitPrice) -1 from [Order Details] where ProductID = 3)
*/

using (var adapter = new DataAccessAdapter())
{
    var orderDetail = new EntityCollection<OrderDetailEntity>();

    var myField = OrderDetailFields.UnitPrice.SetExpression(new Expression(OrderDetailFields.UnitPrice.SetAggregateFunction(AggregateFunction.Min), ExOp.Sub, 1));

    var predicate = new FieldCompareSetPredicate(OrderDetailFields.UnitPrice, null, myField, null, SetOperator.In, (OrderDetailFields.ProductId == 3));
    var filter = new RelationPredicateBucket(predicate);

    adapter.FetchEntityCollection(orderDetail, filter);
}