I'm using LLBLGen 4.2 SelfServicing.

I have a view Pallet with 4 fields: Code, IdSap, Pallennummer and DateTimeCreated. I want to have a collection that is the outcome of:

select Code, IdSap, count(distinct Palletnummer) where DateTimeCreated between from and to.

The following is working fine:

        ResultsetFields fields = new ResultsetFields(3);
        fields.DefineField(PalletFields.Code, 0);
        fields.DefineField(PalletFields.IdSap, 1);
        fields.DefineField(PalletFields.Palletnummer, 2, "Pallets");
        fields[2].AggregateFunctionToApply = AggregateFunction.CountDistinct;
        PredicateExpression filter = new PredicateExpression(new FieldBetweenPredicate(PalletFields.DateTimeCreated, from, to));
        SortExpression sorter = new SortExpression(PalletFields.Code | SortOperator.Ascending);
        GroupByCollection gbc = new GroupByCollection();
        gbc.Add(PalletFields.Code);
        gbc.Add(PalletFields.IdSap);
        TypedListDAO dao = new TypedListDAO();
        DataTable results = new DataTable();

I do not think this is the most elegant way Isn't it possible to achieve via collection on the view itself, without using the result set? I also tried QuerySpec but could not get it to work. How could this be done via QuerySpec?

You are doing it the right way, as you are aggregating and grouping. Collections are used to retrieve entities as they exist in the database.

Please post your QuerySpec code trials, and the output.

There is an example here in docs that can give you a clear idea how to aggregate and group in QuerySpec