I'm trying to convince my manager that LLBLGen is powerful enough to use for our next project, so he given me this query to implement. I've got so far but now I'm stuck. Help!

Here the sql:

SELECT it.[OWNER_ID],
           it.[ITEM_URN],
           it.[NAME],
           it.[ITEM_TYPE_ID],
           mb.[MEMBER_TYPE_ID],
           ca.[MAX_CAPACITY],
           ca.[FLEX_PERCENTAGE],
           COUNT(*) - 1 AS [CURRENT_CAPACITY]
    FROM dbo.TB_DIM_ITEM it  
    JOIN dbo.TB_ADM_CAPACITY ca ON ca.[PARENT_ITEM_TYPE_ID] = it.[ITEM_TYPE_ID]
    LEFT OUTER JOIN dbo.TB_DIM_MEMBER mb ON it.[ITEM_URN] = mb.[PARENT_ITEM_URN] 
                                        AND (mb.[MEMBER_TYPE_ID] = @I_MemberTypeID OR mb.[MEMBER_TYPE_ID] IS NULL)  
    WHERE it.[ITEM_URN] > 0
      AND it.[ITEM_TYPE_ID] = @I_ItemTypeID
      AND it.[DELETED] = 0
      AND it.[DELETED] = 0
    GROUP BY it.[OWNER_ID],
             it.[ITEM_URN], 
             it.[NAME], 
             it.[ITEM_TYPE_ID], 
             mb.[MEMBER_TYPE_ID],
             ca.[MAX_CAPACITY],
             ca.[FLEX_PERCENTAGE]
    HAVING COUNT(*) - 1 < ca.[MAX_CAPACITY]
    ORDER BY it.[OWNER_ID] DESC,
            COUNT(*) - 1 DESC, 
            [ITEM_URN] ASC

The bits i havent been about to figure out are the COUNT(*) - 1 in the select and the having/orderby statements.

Here's my C# so far:

            ResultsetFields fields = new ResultsetFields(8);
            fields.DefineField(ItemFieldIndex.OwnerId, 0, "OwnerId");
            fields.DefineField(ItemFieldIndex.ItemUrn, 1, "ItemUrn");
            fields.DefineField(ItemFieldIndex.Name, 2, "Name");
            fields.DefineField(ItemFieldIndex.ItemTypeId, 3, "ItemTypeId");
            fields.DefineField(MemberFieldIndex.MemberTypeId, 4, "MemberTypeId");
            fields.DefineField(CapacityFieldIndex.MaxCapacity, 5, "MaxCapacity");
            fields.DefineField(CapacityFieldIndex.FlexPercentage, 6, "FlexPercentage");
            fields.DefineField(ItemFieldIndex.ItemUrn, 7, "CurrentCapacity", "", AggregateFunction.CountRow);


            IPredicateExpression customFilter = new PredicateExpression();
            customFilter.Add(PredicateFactory.CompareValue(MemberFieldIndex.MemberTypeId, ComparisonOperator.Equal, member.MemberTypeId));
            customFilter.AddWithOr(PredicateFactory.CompareNull(MemberFieldIndex.MemberTypeId));

            IRelationPredicateBucket filter = new RelationPredicateBucket();

            filter.Relations.Add(ItemEntity.Relations.MemberEntityUsingParentItemUrn, JoinHint.Left).CustomFilter = customFilter;
            filter.Relations.Add(ItemEntity.Relations.ItemTypeEntityUsingItemTypeId);
            filter.Relations.Add(ItemTypeEntity.Relations.CapacityEntityUsingParentItemTypeId);

            filter.PredicateExpression.Add(PredicateFactory.CompareValue(ItemFieldIndex.ItemUrn, ComparisonOperator.GreaterThan, Boxed.Zero));
            filter.PredicateExpression.Add(PredicateFactory.CompareValue(ItemFieldIndex.ItemTypeId, ComparisonOperator.Equal, item.ItemTypeId));
            filter.PredicateExpression.Add(PredicateFactory.CompareValue(ItemFieldIndex.Deleted, ComparisonOperator.Equal, Boxed.False));
            filter.PredicateExpression.Add(PredicateFactory.CompareValue(MemberFieldIndex.Deleted, ComparisonOperator.Equal, Boxed.False));


            ISortExpression sorter = new SortExpression();
            sorter.Add(SortClauseFactory.Create(ItemFieldIndex.OwnerId, SortOperator.Descending));
            sorter.Add(SortClauseFactory.Create(ItemFieldIndex.ItemUrn, SortOperator.Ascending));

            IGroupByCollection groupByClause = new GroupByCollection();
            groupByClause.Add(fields[0]);
            groupByClause.Add(fields[1]);
            groupByClause.Add(fields[2]);
            groupByClause.Add(fields[3]);
            groupByClause.Add(fields[4]);
            groupByClause.Add(fields[5]);
            groupByClause.Add(fields[6]);


            IPredicateExpression havingFilter = new PredicateExpression();
            havingFilter.Add(new FieldCompareValuePredicate(fields[5], ComparisonOperator.LesserThan, fields[7]));

            groupByClause.HavingClause = havingFilter;
            
            DataTable dynamicList = new DataTable();
            this.Adapter.FetchTypedList(fields, dynamicList, filter, 0, sorter, true, groupByClause);

Anyone got any ideas on how to do COUNT(*) - 1 in the select, having & orderby statements?

Did you check LLBLGen Pro documentation manual: "Generated code -> Adapter/SelfServicing -> Using views and lists -> Using GROUP BY and HAVING clauses" ?

There is a sample here also: http://www.llblgen.com/tinyforum/Messages.aspx?ThreadID=6161

And for the Count(*) -1 thing, I guess you ishould Implement IExpression. Please check the following thread for more details: http://www.llblgen.com/tinyforum/Messages.aspx?ThreadID=3829

Any way as a general rule, if you query gets too complex, it's easier to either build it in a database view without the where condition of course, and then map that view as a typedView or an Entity, and then you can select from it using the filter you want (those filtyers that were in the original where condition) Or have it in a SP and then map it's call from LLBLGen Pro.