Adapter ORM 2.0.7.810

expected query

declare @Status int,
    @BatchNumber varchar(14),
    @UpdatedBy varchar(20),
    @UpdatedOn datetime,
    @Status1 int,
    @Status2 int,
    @CommissionAmount int

select  @Status = 2,
    @BatchNumber = '[batch]',
    @UpdatedBy = 'me',
    @UpdatedOn = '2007-9-27 13:20:00',
    @Status1 = 1,
    @Status2 = 1,
    @CommissionAmount = 0

UPDATE  [order_commission_status] 
SET [status] = @Status,
    [batch_number] = @BatchNumber,
    [updated_by] = @UpdatedBy,
    [updated_on] = @UpdatedOn 
WHERE   [order_commission_status].[status] = @Status1 
AND [order_commission_status].[broker_id] IN (
        SELECT  [LPA_s1].[broker_id] AS [BrokerId] 
        FROM    [order_commission_status] [LPA_s1]  
            INNER JOIN [order_detail] ON [LPA_s1].[order_number] = [order_detail].[order_number]
            INNER JOIN [order_detail_commission] ON [order_detail].[order_detail_id] = [order_detail_commission].[order_detail_id] 
                AND [LPA_s1].[broker_id] = [order_detail_commission].[broker_id]
        WHERE   [LPA_s1].[status] = @Status2
        GROUP BY [LPA_s1].[broker_id]
        HAVING  SUM([order_detail_commission].[commission_amount]) > @CommissionAmount
    )

actual query

declare @Status int,
    @BatchNumber varchar(14),
    @UpdatedBy varchar(20),
    @UpdatedOn datetime,
    @Status1 int,
    @Status2 int

select  @Status = 2,
    @BatchNumber = '[batch]',
    @UpdatedBy = 'me',
    @UpdatedOn = '2007-9-27 13:20:00',
    @Status1 = 1,
    @Status2 = 1

UPDATE  [order_commission_status] 
SET [status] = @Status,
    [batch_number] = @BatchNumber,
    [updated_by] = @UpdatedBy,
    [updated_on] = @UpdatedOn 
WHERE   [order_commission_status].[status] = @Status1 
AND [order_commission_status].[broker_id] IN (
        SELECT  [LPA_s1].[broker_id] AS [BrokerId] 
        FROM    [order_commission_status] [LPA_s1]  
            INNER JOIN [order_detail] ON [LPA_s1].[order_number] = [order_detail].[order_number]
            INNER JOIN [order_detail_commission] ON [order_detail].[order_detail_id] = [order_detail_commission].[order_detail_id] 
                AND [LPA_s1].[broker_id] = [order_detail_commission].[broker_id]
        WHERE   [LPA_s1].[status] = @Status2
    )

Group BY is missing.

code query

            IRelationCollection relationships = new RelationCollection();
            relationships.Add(new EntityRelation(OrderCommissionStatusFields.OrderNumber.SetObjectAlias(alias), OrderDetailFields.OrderNumber, RelationType.ManyToMany), alias);
            relationships.Add(new EntityRelation(OrderDetailFields.OrderDetailId, OrderDetailCommissionFields.OrderDetailId, RelationType.OneToMany));
            relationships[1].CustomFilter = new PredicateExpression(OrderCommissionStatusFields.BrokerId.SetObjectAlias(alias) == OrderDetailCommissionFields.BrokerId);

            IGroupByCollection grouping = new GroupByCollection();
            grouping.HavingClause = new PredicateExpression(
                OrderDetailCommissionFields.CommissionAmount.
                SetObjectAlias(alias).
                SetAggregateFunction(AggregateFunction.Sum) > 0
            );

            IRelationPredicateBucket bucket = new RelationPredicateBucket();
            bucket.PredicateExpression.Add(OrderCommissionStatusFields.Status == (int)OrderCommissionStatus.InBalance);
            bucket.PredicateExpression.Add
                (new FieldCompareSetPredicate(
                    OrderCommissionStatusFields.BrokerId, null,
                    OrderCommissionStatusFields.BrokerId.SetObjectAlias(alias), null, 
                    SetOperator.In, 
                    OrderCommissionStatusFields.Status.SetObjectAlias(alias) == (int)OrderCommissionStatus.InBalance,
                    relationships, string.Empty, 0, null, false,
                    grouping
                )
            );

            OrderCommissionStatusEntity entity = new OrderCommissionStatusEntity();
            entity.BatchNumber = batchNumber;
            entity.Status = (int)OrderCommissionStatus.Batched;
            entity.UpdatedBy = WindowsIdentity.GetCurrent().Name;
            entity.UpdatedOn = DateTime.Now;

            using (IDataAccessAdapter adapter = new DataAccessAdapter())
            {
                try
                {
                    adapter.StartTransaction(IsolationLevel.ReadCommitted, "BatchAll");
                    adapter.UpdateEntitiesDirectly(entity, bucket);
                    adapter.Commit();
                    toReturn = true;
                }
                catch (ORMException e)
                {
                    adapter.Rollback();
                }
            }

Am I using FieldCompareSetPredicate.GroupBy incorrectly? If so, how can I accomplish this query? I know a stored proc will work, but I'm trying to keep my BL in one place.

I think I figured it out. I forgot to add

grouping.Add(OrderCommissionStatusFields.BrokerId.SetObjectAlias(alias))

I'm running a few more tests just to make sure

edit: right track with a few minor tweaks, the group by object now looks like this

IGroupByCollection grouping = new GroupByCollection();
grouping.Add(OrderCommissionStatusFields.BrokerId.SetObjectAlias(alias));
grouping.HavingClause = new PredicateExpression(OrderDetailCommissionFields.CommissionAmount.SetAggregateFunction(AggregateFunction.Sum) > 0);