I am trying to return a collection that is grouped and sorted.

This is the orignal code but didn't group.

        Dim Types As New TypesCollection
        Dim filter As New PredicateExpression()

        filter.Add(TypesFields.ParentId = ParentFilter)

        Types.GetMulti(filter)

The client wants it grouped by the parent Id and then sorted by name.

I've tried using the FieldCompareSetPredicate with it's groupby overload but it doesn't return any rows.

        Dim Types As New TypesCollection

        Dim groupByClause As IGroupByCollection = New GroupByCollection()
        groupByClause.Add(TypesFields.ParentId)


        Dim filter As New PredicateExpression()

        filter.Add(New FieldCompareSetPredicate(TypesFields.ParentId, TypesFields.ParentId, _
        SetOperator.In, Nothing, Nothing, String.Empty, 100, Nothing, True, groupByClause))

        Types.GetMulti(filter)

I'm a bit lost as what to do from here. I can't seem to get typedlists going either. If thats an easier way to do it then I'd need to somehow convert the typedlist to an entitycollection.

Any ideas?

I'd need to somehow convert the typedlist to an entitycollection.

You are already using an EntityCollection, which doesn't support Grouping. You can't group an entity Collection.

Instead use a TypedList or a DynamicList. Their fetch methods accepts GroupByClause collection, needless to say a SortExpression.

That was a quick reply

Ok, I've changed the code to this (slightly modifed from the help file):

        Dim fields As New ResultsetFields(3)
        fields.DefineField(TypesFields.Id, 0)
        fields.DefineField(TypesFields.Name, 1)
        fields.DefineField(TypesFields.ParentId, 2)

        Dim groupByClause As IGroupByCollection = New GroupByCollection()
        groupByClause.Add(fields(2))
        groupByClause.Add(fields(1))
        groupByClause.Add(fields(0))

        Dim dynamicList As New Data.DataTable()
        Dim dao As New TypedListDAO()
        dao.GetMultiAsDataTable(fields, dynamicList, 0, Nothing, Nothing, Nothing, True, groupByClause, Nothing, 0, 0)

However, the gridview it is binding to needs it to be a collection. Can a datatable be "converted" into a collection?

Yes it can.

Please check the manual's section: Using the generated code -> SelfServicing -> Fetching DataReaders and projections

Whoa, thats more complicated than I want to get.

Now I've changed the gridview to accept the datatable but it isn't grouping the items at all.

        Dim fields As New ResultsetFields(3)
        fields.DefineField(TypesFields.Id, 0)
        fields.DefineField(TypesFields.Name, 1)
        fields.DefineField(TypesFields.ParentId, 2)

        Dim groupByClause As IGroupByCollection = New GroupByCollection()
        groupByClause.Add(fields(2))
        groupByClause.Add(fields(0))
        groupByClause.Add(fields(1))

        Dim dynamicList As New Data.DataTable()
        Dim dao As New TypedListDAO()
        dao.GetMultiAsDataTable(fields, dynamicList, 0, Nothing, Nothing, Nothing, True, groupByClause, Nothing, 0, 0)

The lsiting should be grouped by the parentId. it is a list of categories and their parent categories.

Here is a small sample of the list:

Category --- Parent
Client --- Top Level
Individual  --- Client
Dependant --- Individual
Spouse --- Individual
Trust --- Client
Company --- Client
Close Corporation --- Client
Classification --- Top Level
Client --- Classification
Other --- Classification
Contact Log --- Top Level
Meeting  --- Contact Log
Telephone Call --- Contact Log
Letter or Fax --- Contact Log
Email --- Contact Log

The lsiting should be grouped by the parentId

Yet your code is grouping by all fields in the select list

   groupByClause.Add(fields(2))
    groupByClause.Add(fields(0))
    groupByClause.Add(fields(1))

If I choose just one option in the grouping list than I get the error:

Column 'Types.Type_Name' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.

Exactly you have to use an aggregate for the other fields found in the select list and not part of the Group by.

So either you do that or you shouldn't use a Group By in the first place.

Exactly you have to use an aggregate for the other fields found in the select list and not part of the Group by.

Maybe I'm a bit slow today but how do I use an aggregate?

Please check the following manual's section: Using the generated code - >Field expressions and aggregates

I've had a look at that section and I've done the following:

    
        Dim fields As New ResultsetFields(3)
        fields.DefineField(TypesFields.Id, 0)
        fields.DefineField(TypesFields.Name, 1)
        fields.DefineField(TypesFields.ParentId, 2)
        fields(0).AggregateFunctionToApply = AggregateFunction.None
        fields(1).AggregateFunctionToApply = AggregateFunction.None
        fields(2).AggregateFunctionToApply = AggregateFunction.None

        Dim groupByClause As IGroupByCollection = New GroupByCollection()
        groupByClause.Add(fields(2))

        Dim dynamicList As New Data.DataTable()
        Dim dao As New TypedListDAO()
        dao.GetMultiAsDataTable(fields, dynamicList, 0, Nothing, Nothing, Nothing, True, groupByClause, Nothing, 0, 0)

It still throws the error:

Column 'Types.Type_ID' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.

I don't want to use any of the other aggregrate functions like AVG, etc. I keep feeling like I'm missing something obvious here.

fields(0).AggregateFunctionToApply = AggregateFunction.None fields(1).AggregateFunctionToApply = AggregateFunction.None fields(2).AggregateFunctionToApply = AggregateFunction.None

    Dim groupByClause As IGroupByCollection = New GroupByCollection()
    groupByClause.Add(fields(2))

You can't have un-aggregated fields in the select list that are not part of the grouping clause, that's SQL rule.

Would you please work out the SQL Query you want to execute and test it against the database, then post it here so we can help you formulate it with LLBLGen's code.

I've just realised that group by may not be what I'm looking for.

What I'm trying to do is show a list of categories with their sub-categories listed alphabetically. Something like this (Sub Cat first, then Parent):

Coke - Drink
Fanta - Drink
Sprite - Drink
Bread - Food
Cereal - Food
Soup - Food
Peaches - Fruit
Watermelon - Fruit

Is there an easy way to do this?

Without more knowledge about your datamodel it's not easy to answer this. Are the hierarchies in teh same table, or in a separate table? etc.

The information is all in one table. Each record has id, name and parent_id. The parent_id is the FK for the self referencing id. i.e a record refrerences another reocrd in the table as it's parent.

The way I see it I have two options, 1. Do a join and sort by the parent name and then the child name. 2. Fetch all the parents and then recursively fetch the children.

I'm using self servicing and binding to a gridview so I don't really want to use option 2.

This is the sql I would normally use

SELECT t1.type_id, t1.type_name, t1.type_parent_id, t2.type_name 
from types as t1
    INNER JOIN types as t2 ON t1.type_parent_id = t2.type_id
ORDER By t2.type_name, t1.type_name ASC

Now I've figured I need to create a field for the t2.type_name field but I'm not sure what expression to use.

no need to reply, I got it sorted out.

Didn't realise there was an alias overload.

Thanks for great service and a brilliant time-saving product