Hello,

I have searched a while througth the forum but I can't find the answer, I am using LLBLGen 2.5 in the SelfServicing mode and I am trying to recieve the following:

SELECT item, count(item) FROM table GROUP BY item;

I already tried this but this won't work:

ResultsetFields fields = new ResultsetFields(2); fields.DefineField(ItemFields.Type, 0, "Type"); fields.DefineField(ItemFields.Type, 1, "CountItem"); fields["CountItem"].SetAggregateFunction(AggregateFunction.Count);

GroupByCollection groupBy = new GroupByCollection(); groupBy.Add(fields[0]);

DataTable results = new DataTable(); TypedListDAO dao = new TypedListDAO(); dao.GetMultiAsDataTable(fields, results, 0, null, null, null, true, groupBy, null, 0, 0);

Tanks in advantage,

Can't see nothing wrong, try the following:

fields.DefineField(ItemFields.Type, 0, "Type","Items");
fields.DefineField(ItemFields.Type, 1, "CountItem","Items",AggregateFunction.Count);

instead of:

fields.DefineField(ItemFields.Type, 0, "Type");
fields.DefineField(ItemFields.Type, 1, "CountItem");
fields["CountItem"].SetAggregateFunction(AggregateFunction.Count);

and

IGroupByCollection groupBy = new GroupByCollection();

instead of

GroupByCollection groupBy = new GroupByCollection();

If that doenst work, please turn on the llblgen debugging feature in your .config file (see official documentation for details) and paste the generated sql-query in this thread.

I changed into the following:

ResultsetFields fields = new ResultsetFields(2);
fields.DefineField(Item.Type, 0, "Type", "Items");
fields.DefineField(Item.Type, 1, "Aantal", "Items", AggregateFunction.Count);
            
IGroupByCollection groupBy = new GroupByCollection();
groupBy.Add(fields[0]);
            
DataTable results = new DataTable();
TypedListDAO dao = new TypedListDAO();
dao.GetMultiAsDataTable(fields, results, 0, null, null, null, true, groupBy, null, 0, 0);

But thes errors popup:

The text, ntext, and image data types cannot be compared or sorted, except when using IS NULL or LIKE operator

in class: "TypedListDAO.cs" function: "public bool GetMultiAsDataTable(...)"

generated sql:

SELECT [Items].[type] AS [Type], 
COUNT([Items].[type]) AS [Aantal] 
FROM [database].[dbo].[tbl_message] AS [Items]  
GROUP BY [Items].[type]

I already tried this but this won't work: Quote: ResultsetFields fields = new ResultsetFields(2); fields.DefineField(ItemFields.Type, 0, "Type"); fields.DefineField(ItemFields.Type, 1, "CountItem"); fields["CountItem"].SetAggregateFunction(AggregateFunction.Count);

GroupByCollection groupBy = new GroupByCollection(); groupBy.Add(fields[0]);

DataTable results = new DataTable(); TypedListDAO dao = new TypedListDAO(); dao.GetMultiAsDataTable(fields, results, 0, null, null, null, true, groupBy, null, 0, 0);

You didn't specify what was the outcome of using the above code? Would you please examine the generated SQL Query and run it against the database and post you findings here?

Btw, what's the database data-type of the "Type" field? Which LLBLGen Pro runtime library version are you using?

Tanks for the help, it was a database problem, the item.type was a of the type: "text", Group By isn't supported on the type text

My problem is already solved, but the following just for the record (maybee a bug)

goose wrote:

fields.DefineField(ItemFields.Type, 0, "Type","Items");
fields.DefineField(ItemFields.Type, 1, "CountItem","Items",AggregateFunction.Count);

instead of:

fields.DefineField(ItemFields.Type, 0, "Type");
fields.DefineField(ItemFields.Type, 1, "CountItem");
fields["CountItem"].SetAggregateFunction(AggregateFunction.Count);

in the excample you gave the fields.DefineField also use an alias for the database. When you combine that with a filter the alias is not used in the WHERE section of the generated query and you will get an error.

Tanks for helping,

Great7ven

I think this was solved before, so maybe you are using an old version (check your runtime library version)?

Walaa wrote:

I think this was solved before, so maybe you are using an old version (check your runtime library version)?

SD.LLBLGen.Pro.DQE.SqlServer.NET20.dll version: 2.5.7.1219

SD.LLBLGen.Pro.ORMSupportClasses.NET20.dll version: 2.5.8.109

To use the alias in a predicate you should use a field from the ResultsetFields rather than the entity field.

use:

filter.Add(field[1] == xyz);

rather than:

filter.Add(MyEntityFields.SomeField == xyz);