I'm using 2.6, Adapter.

I want to be able to filter a FetchEntityCollection based on the rowcount of a related table. For example, I want all the orders that have 3 orderitems. I am pretty sure I need a FieldCompareExpressionPredicate for this, but I can't get it working. Here is what I have so far:

EntityCollection<OrderEntity> col = new EntityCollection<OrderEntity>();
IRelationPredicateBucket filter = new RelationPredicateBucket();
            filter.Relations.Add(OrderEntity.Relations.OrderItemEntityUsingOrderd);
            filter.PredicateExpression.Add(OrderItemFields.OrderItemId.SetAggregateFunction(AggregateFunction.CountRow) == 3);

daa.FetchEntityCollection(col, filter, null);

However, I get an exception about being unable to compare a Guid to an int...In this case OrderItemId is a Guid so I am guessing the system is choking on this field....Help?

It would be much easier for you if you first developed the SQL query that returns the correct results.

I think the following SQL should use something like the following:

SELECT * FROM Orders
WHERE OrderId IN 
(SELECT OrderId FROM OrderItem
GROUP BY OrderId
HAVING Count(*) > 3)

Which can be implemented using a FieldCompareSetPredicate.

Walaa wrote:

It would be much easier for you if you first developed the SQL query that returns the correct results.

I think the following SQL should use something like the following:

SELECT * FROM Orders
WHERE OrderId IN 
(SELECT OrderId FROM OrderItem
GROUP BY OrderId
HAVING Count(*) > 3)

Which can be implemented using a FieldCompareSetPredicate.

Right, I know this is the query I want (I guess I should have posted the query...is this what you are saying?). What I don't get is how to make this predicate. The Count(*) is the part I am having trouble with.

EntityCollection<OrderEntity> orders = new EntityCollection<OrderEntity>();

// -- BUILD THE FILTER
IRelationPredicateBucket filter = new RelationPredicateBucket();

// the group clause
GroupByCollection grouper = new GroupByCollection(OrderItemFields.OrderId);
grouper.HavingClause = new PredicateExpression(OrderItemFields.OrderId.SetAggregateFunction(AggregateFunction.Count) > 3);

// the FieldCompareSetPredicate
FieldCompareSetPredicate subqueryFilter = new FieldCompareSetPredicate(
    OrderFields.OrderId, null, OrderItemFields.OrderId, null,
     SetOperator.In, null, null, string.Empty, 0, null, false, grouper);

filter.PredicateExpression.Add(subqueryFilter);

// FETCH RESULTS
using (DataAccessAdapter adapter = new DataAccessAdapter())
{
    adapter.FetchEntityCollection(orders, filter);
}

daelmo wrote:

EntityCollection<OrderEntity> orders = new EntityCollection<OrderEntity>();

// -- BUILD THE FILTER
IRelationPredicateBucket filter = new RelationPredicateBucket();

// the group clause
GroupByCollection grouper = new GroupByCollection(OrderItemFields.OrderId);
grouper.HavingClause = new PredicateExpression(OrderItemFields.OrderId.SetAggregateFunction(AggregateFunction.Count) > 3);

// the FieldCompareSetPredicate
FieldCompareSetPredicate subqueryFilter = new FieldCompareSetPredicate(
    OrderFields.OrderId, null, OrderItemFields.OrderId, null,
     SetOperator.In, null, null, string.Empty, 0, null, false, grouper);

filter.PredicateExpression.Add(subqueryFilter);

// FETCH RESULTS
using (DataAccessAdapter adapter = new DataAccessAdapter())
{
    adapter.FetchEntityCollection(orders, filter);
}

Thank you. This is how I was trying to do it with the FieldCompareExpressionPredicate...I guess I misunderstood the use of this.